What are Forwarded Records in SQL Server? – Interview Question of the Week #145

Question: What is a forwarded record in SQL Server, and what should I do when I find one?

Answer: This topic still appears in interviews, and I’m surprised how often a DBA has never examined a heap closely enough to see why it matters. A heap is a table without a clustered index. If an update enlarges a row so it no longer fits on its page, SQL Server can move the row and leave a forwarding pointer at its original location.

The extra pointer preserves a way to find the moved row. It can also mean extra page reads when a lookup follows that pointer. This doesn’t make every heap slow, and a count alone doesn’t prove that forwarded rows caused a particular query’s delay. It tells you where to investigate.

Inspect One Heap

Running sys.dm_db_index_physical_stats across a whole database in DETAILED mode can scan far more than you intend. Point it at one table instead. Replace dbo.YourHeap below with the table you are investigating. The guard matters: passing a NULL object ID to this DMV broadens the scan to every table.

DECLARE @ObjectId int = OBJECT_ID(N'dbo.YourHeap', N'U');
IF @ObjectId IS NULL
    THROW 50001, 'Choose an existing heap in this database.', 1;
IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE object_id = @ObjectId AND index_id = 0)
    THROW 50139, 'Selected table is not a heap.', 1;
SELECT partition_number,
       forwarded_record_count,
       page_count,
       avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
     (DB_ID(), @ObjectId, 0, NULL, 'DETAILED')
WHERE index_id = 0;

Run this against the intended database and at a suitable time for the table size. index_id = 0 identifies the heap, and forwarded_record_count is the number to read. Compare that finding with the actual queries and their I/O before choosing a repair.

Choose the Repair That Fits the Table

One option is a clustered index, if the table’s design and workload justify it. That’s a design decision, not an automatic rule that every heap needs an index. If the table should remain a heap, rebuilding it removes the current forwarded records. It may only be a temporary improvement if the same expanding updates continue.

-- Replace the name and plan the lock, log, and maintenance impact.
ALTER TABLE dbo.YourHeap REBUILD;

That’s the fuller interview answer I look for: explain the forwarding pointer, show how to inspect a specific heap, and connect any change to observed I/O and the table’s design. If you have met a forwarded-row case that changed performance, please share the before and after in the comments.

A forwarded record is not corruption, it is a row that moved and left a note, and the reads it adds are what you measure.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.