Forwarded Records: What an UPDATE Can Do to a Heap
A heap, a table with no clustered index, has a failure mode that clustered tables do not: an UPDATE that makes a row larger can force it to physically move, and SQL Server leaves a pointer behind at the old location so nonclustered indexes and the RID can still find it. These are forwarded records, and they are one of the better reasons to think twice before leaving a busy table as a heap. This post creates one on purpose, finds the forwarding stub and the forwarded record on the pages, and explains why they punish scans.
This continues the byte-level storage series alongside the NULL bitmap, row-overflow and LOB, and where each column lives in a record. If DBCC PAGE is new to you, this older post covers the mechanics. Every byte below is from a SQL Server 2019 instance, and the script is included so you can reproduce it.
Why a heap forwards a row
In a heap, a row is identified by its physical location: an 8-byte row identifier (RID) of file, page, and slot.[1] Nonclustered indexes point at rows by that RID. That creates a problem when a row grows. If you update a variable-length column so the row no longer fits where it sits, the row has to move to a page with room, but moving it would change its RID and invalidate every index pointer aimed at it.
SQL Server’s solution is to leave a forwarding pointer in the original slot that points to the row’s new home, the forwarded record. The RID never changes; anyone who arrives at the old location is redirected. The documentation is candid about the cost: following these pointers during a scan “limits read-ahead performance, and can incur additional I/O which reduces scan performance.”[1]
Making one happen
Ten medium rows that together fill a single 8 KB page, then an update that grows the middle one past the space left on that page:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE [dbo].[fwd_demo] ( [id] int NOT NULL , [v] varchar(8000) NOT NULL ); DECLARE @i int = 1; WHILE @i <= 10 BEGIN INSERT INTO [dbo].[fwd_demo] ( [id], [v] ) VALUES ( @i, REPLICATE('A', 750) ); SET @i += 1; END; /* grow one row far past the page's remaining free space */ UPDATE [dbo].[fwd_demo] SET [v] = REPLICATE('X', 3000) WHERE [id] = 5; |
sys.dm_db_index_physical_stats counts the damage before and after the update:
|
1 2 3 4 |
phase page_count record_count forwarded_records ------------- ---------- ------------ ----------------- before update 2 10 0 after update 2 11 1 |
The forwarded record count went from 0 to 1, and the record count rose to 11 even though there are still only 10 rows. That extra “record” is the forwarding stub, which the engine counts alongside the real rows.
The stub and the forwarded record
Dumping the pages shows the two halves. On the original page, in the slot that used to hold row 5, there is now a stub:
|
1 2 3 |
Record Type = FORWARDING_STUB Record Attributes = Record Size = 9 0000000000000000: 04082400 00030001 00 |
Nine bytes. The first, 04, is the status byte marking this as a forwarding stub. The remaining eight are the RID of the new location, and they decode cleanly: page 0x00002408 = 9224, file 0x0003 = 3, slot 0x0001 = 1. In other words, “the row you are looking for now lives at 3:9224, slot 1.”
Go to that page and there it is, flagged as a forwarded record:
|
1 2 3 4 |
Record Type = FORWARDED_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 3027 0000000000000000: 32000800 05000000 02000002 00c90bd3 8b585858 |
The id is 05 00 00 00 = 5, followed by the 3,000 X characters (58 is X). This is the real row, relocated. A forwarded record also carries a back-pointer to its stub, so the engine can find its way home if the row shrinks and moves back.
Why it hurts, and what to do
The performance cost is about scan efficiency. When SQL Server scans a heap in allocation order and hits a forwarding stub, it has to jump to the forwarded record’s page to read the row, then return to continue the scan. That extra hop defeats read-ahead and, on a heap riddled with forwarded records, can multiply the I/O of a full scan.
- Forwarded records are a heap-only problem. A clustered table identifies rows by key, not physical location, so a growing row causes a page split (its own cost) but never a forwarding pointer. This is one concrete reason most busy tables want a clustered index.
- Updates that grow variable-length columns are the trigger. A workload that inserts short values and later fills them in, or that updates
varcharcolumns to longer strings, is the classic way a heap accumulates forwarded records. - You can measure and fix it.
sys.dm_db_index_physical_statsreportsforwarded_record_countfor heaps. To clear them, rebuild the heap withALTER TABLE ... REBUILD, which rewrites the rows in place and removes the forwarding pointers. A clustered index removes the possibility entirely. - Heaps still have good uses. Staging tables that are bulk-loaded and truncated, never updated in place, will not forward. The trouble comes specifically from in-place growth.
Forwarded records are the heap quietly paying interest on a decision to skip a clustered index. Seeing the stub, RID and all, makes the abstract “heaps fragment under updates” warning concrete: there really is a little “moved, see 3:9224” note sitting where your row used to be.
Have you found a heap full of forwarded records behind a slow scan? I would like to hear it. Find me on Bluesky or LinkedIn.
References
- Heaps (Tables without clustered indexes) – Microsoft Learn. The 8-byte RID (file, page, slot), how an update that needs more space creates a forwarded record and forwarding pointer, and the effect on read-ahead and scan performance. ↩