Heap Tables, RID Lookups, Forwarded Records, and Heap Maintenance

Learning outcomes

A ServiceHub staging table receives narrow rows quickly, then workers append large diagnostic text. On a heap, an update that no longer fits on the original page can leave a forwarding record that points to the moved row. A nonclustered index on a heap uses a Row Identifier (RID)—file ID, page ID, and slot—as its base-row locator. This lesson shows how to prove those mechanics and decide whether maintenance is justified by workload evidence.

1. Heap row locators and RID Lookups

For a nonclustered index on a heap, the leaf row needs a way to locate the base table row. SQL Server uses a RID containing the file ID, page ID, and row slot. If a query uses the nonclustered index but needs columns not stored there, the plan may perform a RID Lookup. On a clustered table, that same locator role is played by the clustering key and the lookup operator is a Key Lookup.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab08') IS NULL EXEC(N'CREATE SCHEMA lab08 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab08.DispatchHeap;CREATE TABLE lab08.DispatchHeap(  dispatch_id int IDENTITY(1,1) NOT NULL,  work_order_id int NOT NULL,  status varchar(20) NOT NULL,  note varchar(3000) NULL); -- no clustered index: this is a heapCREATE INDEX IX_DispatchHeap_StatusON lab08.DispatchHeap(status);;WITH n AS(  SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n  FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)INSERT lab08.DispatchHeap(work_order_id,status,note)SELECT n,CASE WHEN n%10=0 THEN 'ESCALATED' ELSE 'OPEN' END,'small'FROM n;GO

2. Row growth can create forwarding records

When a heap row grows and the original page does not have enough free space, SQL Server can move the row to another page and leave a forwarding record at the original location. The forwarding pointer preserves existing RIDs, but scans and lookups may need an extra hop. This is why a heavily updated variable-length heap can accumulate additional I/O without any corruption.

UPDATE lab08.DispatchHeapSET note=REPLICATE('N',2400)WHERE dispatch_id % 3 = 0;GOSELECT index_id,index_type_desc,page_count,       forwarded_record_count,avg_page_space_used_in_percentFROM sys.dm_db_index_physical_stats     (DB_ID(),OBJECT_ID(N'lab08.DispatchHeap'),0,NULL,'DETAILED');GO

Expect index_id=0 because the object is a heap. A nonzero forwarded_record_count demonstrates forwarding, but the exact count is data- and page-layout-dependent. It is not itself a mandate to rebuild.

3. Observe access behavior, not just storage shape

The operational DMV can count forwarded fetches and range scans. These counters are cumulative since the relevant metadata/counter reset and do not prove that forwarding is the bottleneck. Pair them with actual execution plans and local I/O evidence.

SET STATISTICS IO ON;SELECT dispatch_id,work_order_id,noteFROM lab08.DispatchHeapWHERE status='ESCALATED';SET STATISTICS IO OFF;GOSELECT index_id,range_scan_count,singleton_lookup_count,       forwarded_fetch_count,leaf_allocation_countFROM sys.dm_db_index_operational_stats     (DB_ID(),OBJECT_ID(N'lab08.DispatchHeap'),NULL,NULL)ORDER BY index_id;GO

Capture the actual plan in SSMS or VS Code MSSQL and inspect whether IX_DispatchHeap_Status is followed by a RID Lookup. Plan shape can change with statistics and data distribution, so the lesson describes what to inspect rather than promising a fixed operator.

4. Repair the problem you measured

ALTER TABLE ... REBUILD can rebuild a heap and remove forwarding/wasted layout. Creating a clustered index changes the storage structure entirely and also affects nonclustered row locators. Both operations consume resources and can be disruptive; “rebuild all heaps nightly” is not a safe default.

SELECT forwarded_record_count AS before_rebuildFROM sys.dm_db_index_physical_stats     (DB_ID(),OBJECT_ID(N'lab08.DispatchHeap'),0,NULL,'DETAILED');GOALTER TABLE lab08.DispatchHeap REBUILD;GOSELECT forwarded_record_count AS after_rebuildFROM sys.dm_db_index_physical_stats     (DB_ID(),OBJECT_ID(N'lab08.DispatchHeap'),0,NULL,'DETAILED');GO

5. Production judgment and cleanup

Use sys.dm_db_index_physical_stats in an appropriate scan mode; DETAILED can be expensive on large objects. Use sys.dm_db_index_operational_stats as a reset-sensitive counter source, not historical truth. If converting a large heap to/from clustered storage, budget log, tempdb/storage, locking, and nonclustered-index rebuild effects. No paid edition or special topology is required for the learning lab.

DROP TABLE IF EXISTS lab08.DispatchHeap;GO

Authoritative references

• Heaps — heap behavior and maintenance
• Index architecture and design guide — RID and clustering-key row locators
• sys.dm_db_index_physical_stats — forwarded_record_count
• sys.dm_db_index_operational_stats — forwarded_fetch_count and access counters
• SQL Server 2025 build versions — servicing baseline