SQL Server Heaps and Forwarded Records: The Audit Table That Got Slower Every Month

The report that paged me was a storage trend, not a performance one: a single audit table had consumed 340 GB in a database whose live data was under 90 GB, and its nightly aggregation scan had degraded from four minutes to seventy over about eighteen months — smoothly, monotonically, the kind of slope that never trips a threshold until it trips a human. The table had been created by a contractor with an IDENTITY column, a pile of VARCHAR columns, and no clustered index, on the theory that heaps insert fast. That theory was even correct, for the inserts. What nobody modeled was the update pattern: rows were inserted with short payload values and then updated hours later with the full payload, and every update that no longer fit in place left a forwarded record behind. Eighteen months later the physical stats showed 52 million rows and 40 million forwarded records — three quarters of the table’s reads were chasing pointers to second locations, and the nightly scan was doing nearly double the I/O of the data it actually needed.

Heaps are the most misunderstood storage structure in SQL Server, because they are genuinely excellent at exactly one thing and quietly pathological at almost everything else. This is how forwarding actually works, how to measure it, why the standard fix is a treadmill, and the honest cases where a heap is the right design. Applies to SQL Server 2016 through 2022.

How do forwarded records actually form?

A heap has no key order, so a row lives wherever it was inserted, and when an update makes the row too large for its original location, the engine’s choices are to move the row or split the page — and heaps do neither in the way you would hope. The row is moved to a new location that has room, and the original slot is left holding a nine-byte forwarding pointer to the new address. Every subsequent read of that row pays two I/Os: one for the pointer, one for the row. Update the row again and the engine updates the pointer in place rather than chaining, so the pointer stays at the original slot forever — the row’s home address never changes, only how far from home it sleeps. This is why the damage is cumulative and silent: each forwarding event is individually trivial, the insert workload that heaps are chosen for keeps humming along, and nothing anywhere logs a warning as the pointer population climbs into the millions. The only honest measurement is the physical stats DMV, in LIMITED mode so you do not scan the structure to measure the structure:

SELECT OBJECT_NAME(s.object_id) AS table_name,
       s.record_count,
       s.forwarded_record_count,
       CAST(100.0 * s.forwarded_record_count
            / NULLIF(s.record_count, 0) AS DECIMAL(5,2)) AS pct_forwarded
FROM sys.dm_db_index_physical_stats(
         DB_ID(), NULL, NULL, NULL, 'LIMITED') AS s
JOIN sys.tables AS t ON t.object_id = s.object_id
WHERE s.index_id = 0            -- heaps only
  AND s.record_count > 100000
ORDER BY s.forwarded_record_count DESC;

My working rule is that a heap with more than ten percent forwarded records is paying real money on every scan, and anything over thirty percent — our audit table sat at seventy-seven — is an active incident that happens to be moving slowly.

Why is rebuilding the heap a treadmill?

Because ALTER TABLE … REBUILD on a heap — available since SQL Server 2008 and often presented as the fix — compacts the table and eliminates the existing forwarded records, and then the update pattern that created them starts creating them again the same afternoon. We ran the rebuild on the audit table the first weekend: scan time dropped from seventy minutes back to five, everyone declared victory, and I put a reminder in the calendar to re-measure in ninety days. At ninety days the table had four million new forwarded records and the scan was at nineteen minutes. The rebuild is a valid one-time corrective after you have changed the underlying behavior — narrowed the variable-length columns, stopped the insert-then-grow pattern, or archived the cold data — but as a standalone maintenance task it is a recurring outage-sized job that treats the symptom on a schedule. It also has a sharp edge worth knowing before you schedule it: rebuilding a heap rebuilds every nonclustered index on that heap, because nonclustered indexes on heaps reference rows by physical RID and every RID changes when the heap is compacted. On our 340 GB table the “heap rebuild” was actually a full table rebuild plus seven index rebuilds, and the maintenance window math had to be done accordingly:

-- one-time corrective: compacts the heap AND every NC index on it
ALTER TABLE dbo.AuditEvents REBUILD;

-- check free space per page afterward (avg_page_space_used_in_percent)
SELECT OBJECT_NAME(object_id) AS table_name,
       avg_page_space_used_in_percent, forwarded_record_count
FROM sys.dm_db_index_physical_stats(
         DB_ID(), OBJECT_ID('dbo.AuditEvents'), 0, NULL, 'DETAILED');

When is a clustered index the fix, and when is the heap genuinely right?

The clustered index is the fix whenever the table is read by any predicate, updated after insert, or deleted from in bulk — which is to say, nearly every table that is not a pure staging hop. On the audit table the durable fix was a clustered index on the identity column plus an archive job that switched old months out instead of deleting them row by row, and the forwarded record count has been zero ever since, because rows that grow in a clustered index split pages instead of leaving pointers — the page-split tradeoff I covered in the page splits notes, which is at least a tradeoff you can manage with fillfactor. The honest cases for heaps are narrow and real: insert-only staging tables that are truncated between loads, where the heap’s insert speed and freedom from page splits is exactly what you want; bulk-load targets where TABLOCK minimal logging matters; and queue-style tables with careful patterns, though even those usually end up clustered in my designs. The design question that settles it is a single sentence: will any row in this table ever be updated or individually deleted? If yes, it wants a clustering key. The index maintenance plans that quietly include heap rebuilds — the kind I audited in the index maintenance reality check — deserve the same scrutiny, because a scheduled heap rebuild with no design change behind it is a scheduled admission that the table is wrong.

How do I audit an estate for heap rot?

With one query run everywhere, quarterly: every heap in the database, its row count, its forwarded record count, and its size, ranked by read pain. The query above is the whole audit; the discipline is running it as part of estate hygiene rather than as incident response. What you will find, in my experience, is a long tail of small heaps that do not matter, a handful of contractor-era tables like our audit table that matter enormously, and at least one table that everyone believed had a clustered index because it has a primary key — and nobody noticed the primary key was created nonclustered. That last pattern is common enough that the audit should list every heap by name for human review rather than only the statistically ugly ones. A heap that is small and truly insert-only stays on the list with a note; a heap that is large and updated gets a ticket.

Watching table structure with MonPG when SQL Server support lands

For MonPG product capabilities and setup information, see the SQL Server monitoring page. Use the diagnostics in this article to identify the measurements and operational checks your deployment needs.

Related documentation

• SQL Server: index maintenance