Heaps and forwarded records

In one line: a heap (a table with no clustered index) gets expensive in two quiet ways. Rows that grow get forwarded to other pages, and every scan follows the pointer. And pages emptied by DELETE stay allocated unless the delete held a table lock.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/heap-forwarded-records on GitHub.

What you’ll see

  • A heap that keeps getting slower to scan while its row count barely changes. In the demo, widening every third row turned a 351-read scan into 34,074 reads.
  • A staging table purged down to a few rows that still scans hundreds of pages. 10,000 rows sat on 347 pages, 16% full.
  • forwarded_fetch_count climbing in the index operational stats.

Why it happens

  • Forwarding. When an updated row no longer fits on its page, a heap moves it to any page with room and leaves a pointer in the old slot, because every nonclustered index points to that physical location. A scan reads the pointer, jumps to the new page and comes back: one extra read per forwarded row, almost exactly. The heap had fewer pages than a clustered table that split instead, and read 9× as much.
  • Empty pages. A heap releases a page that DELETE emptied only if the delete held a lock on the whole table. Batched deletes, the standard cure for lock escalation, never do, so every emptied page stays and is scanned forever.

What to do

  • Give the table a clustered index if its rows grow or it’s purged with batched deletes. That fixes both problems for good.
  • Empty a heap with TRUNCATE, or DELETE ... WITH (TABLOCK), when the whole table or most of it goes.
  • ALTER TABLE ... REBUILD after a batched purge or a wave of growing updates. It cleared 31,949 forwarded records and released the empty pages, but it also rebuilds every nonclustered index, and it only resets the problem.

What doesn’t help

  • “Heaps are fine for staging.” They’re fine while rows don’t grow and the table is emptied with a table lock. Stop doing either and they aren’t.
  • Counting pages. Fewer pages didn’t mean less work: forwarded rows are extra reads that the page count doesn’t show.

Check your server

-- Heaps in this database that scans have had to follow forwarding pointers
-- (since the table's metadata was last cached), plus their size.
SELECT OBJECT_SCHEMA_NAME(os.object_id) AS schema_name,
       OBJECT_NAME(os.object_id)        AS table_name,
       os.forwarded_fetch_count,
       ps.row_count,
       ps.used_page_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS os
JOIN sys.dm_db_partition_stats AS ps
  ON ps.object_id = os.object_id AND ps.index_id = os.index_id AND ps.partition_number = os.partition_number
WHERE os.index_id = 0
  AND OBJECTPROPERTY(os.object_id, 'IsUserTable') = 1
ORDER BY os.forwarded_fetch_count DESC;

A heap with many pages per row is the empty-page problem. Many forwarded fetches is the forwarding problem.

See it happen

The heap-forwarded-records demo widens rows in a heap and in a clustered table side by side. It compares scans and lookups, purges four ways, and measures what ALTER TABLE ... REBUILD recovers.

Related: lock escalation · index fragmentation · data compression

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights