Diagnose forwarded records in SQL Server heaps

A heap can be a reasonable landing table for short-lived data, yet behave differently after months of updates. When variable-length rows expand, the original page may no longer have room. SQL Server can move the row and leave forwarding information at its previous location, allowing existing row locators to remain useful at the cost of extra navigation.

Reproduce row expansion in a disposable table

A heap has no clustered index. It can still have nonclustered indexes, including a nonclustered primary key. The example makes that choice explicit so the primary key does not quietly turn the demonstration into a clustered table. Run it only in a practice database where this object name is unused.

CREATE TABLE dbo.HeapForwardDemo
(
    RowId int NOT NULL PRIMARY KEY NONCLUSTERED,
    Payload varchar(1000) NOT NULL
);
;WITH N AS
(
    SELECT TOP (10000)
        CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.HeapForwardDemo (RowId, Payload)
SELECT n, REPLICATE('a', 100) FROM N;

UPDATE dbo.HeapForwardDemo
SET Payload = REPLICATE('b', 900)
WHERE RowId % 2 = 0;

The initial payload is short; half the rows then expand substantially. The exact number of forwarded records depends on page placement and available space, so measure rather than expecting a fixed percentage.

SELECT
    index_id, partition_number, alloc_unit_type_desc,
    page_count, record_count,
    forwarded_record_count,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(), OBJECT_ID(N'dbo.HeapForwardDemo'), 0, NULL, 'DETAILED'
)
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

The inspection targets one heap and requests DETAILED information. It is deliberately narrow because physical-statistics collection can perform significant I/O. Appropriate database diagnostic permissions are required. Do not replace the object filter with NULL on a large production instance merely to produce a comprehensive-looking report.

forwarded_record_count is evidence of row movement, while page_count and average page-space usage help describe the resulting layout. A NULL metric in a less detailed collection mode is not the same as zero. Keep the collection mode, partition, and allocation-unit context with the measurement.

Connect the layout to observed work

Nonclustered indexes on a heap use row locators that can lead to the original location and then to a forwarded row. That extra navigation can increase work, but a forwarding count alone does not quantify the application's latency. The effect depends on access paths, cache state, and how often the affected data is read.

Identify queries that use the heap and record logical reads, CPU, duration, and actual plans for representative parameters. Compare under a similar workload. If rows also became nine times wider, increased page count and fewer rows per page can explain part of the change independently of forwarding.

A covering nonclustered index may avoid fetching some payloads from the heap for a particular query, but that is not a universal repair. It adds storage and write maintenance, and other queries may still read the base rows. Choose indexes based on useful access patterns rather than using them to hide an unexplained storage issue.

Also separate heap forwarding from logical fragmentation in a B-tree. A generic maintenance script that looks only at avg_fragmentation_in_percent can miss the problem you actually care about. Heap layout needs its own interpretation and workload evidence.

Choose a remedy that matches the table's lifecycle

Rebuilding the heap can remove existing forwarding, but it does not stop future expanding updates from creating it again. The following commands apply only to the practice object. Run the inspection again after the rebuild before dropping the table.

ALTER TABLE dbo.HeapForwardDemo REBUILD;
-- Run the inspection query again before removing the test table.
DROP TABLE dbo.HeapForwardDemo;

For a production table, plan rebuild duration, transaction-log capacity, space, locking, and effects on its nonclustered indexes. Rebuilding only a nonclustered index does not reorganize the heap's base rows. Avoid scheduling a large rebuild solely because an arbitrary forwarding threshold was crossed.

A well-chosen clustered index can fit a long-lived table with frequent keyed reads and updates, but it changes nonclustered row locators and introduces its own page-split and key-width tradeoffs. Choose a stable key based on the workload; adding any clustered key without review is not automatically an improvement.

For transient staging data loaded, processed, and discarded, retaining a heap may be entirely appropriate. Its maintenance strategy can be tied to the batch lifecycle instead of a nightly repair loop. For growing persistent rows, review whether the schema repeatedly expands values after insertion and whether that pattern is necessary.

Keep the after-measurement tied to query behavior and recurrence. If forwarding quickly returns and user latency barely changes after rebuild, routine rebuilding is treating a symptom. The useful outcome is a storage design and maintenance schedule supported by the table's actual lifecycle.

Technical references: Microsoft Learn: Heaps · Microsoft Learn: Index physical statistics · Microsoft Learn: ALTER TABLE.