ALTER TABLE REBUILD: Fixing a Heap Without a Clustered Index

ALTER TABLE REBUILD cleans up forwarded records in a heap, and you do not need a clustered index to do it. It rewrites the table into a fresh layout. But if the same kind of updates keep coming, the forwarding comes back.

How a heap collects forwarded records

A heap is a table with no clustered index. Rows go wherever there is room. That is fine until an update makes a row longer. If the row no longer fits on its page, SQL Server moves it to a new page. It leaves a small pointer, a forwarded record, in the old spot, because other structures still point there.

Now every read of that row costs an extra page visit: first the old spot, then the real one. Picture a staging table. Rows arrive with a short placeholder, and a later job fills in the long text. Months later the nightly job is slow, and nobody can say why.

Let me build exactly that. First, 50 small rows in a heap with one nonclustered index.

DROP TABLE IF EXISTS dbo.HeapDemo;
CREATE TABLE dbo.HeapDemo (Id int NOT NULL, Payload varchar(2000));
CREATE INDEX IX_HeapDemo ON dbo.HeapDemo (Id);

INSERT dbo.HeapDemo (Id, Payload)
SELECT value, 'a' FROM GENERATE_SERIES(1, 50);

SELECT N'Small rows' AS Stage, index_id, page_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.HeapDemo'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = N'IN_ROW_DATA';

The small rows fit on one page, and nothing is forwarded.

Widen the rows and measure

Now the filling-in job runs. Every row grows to 1,500 bytes. Only about five of those fit on a page, so most rows must move. I measure, rebuild the heap with ALTER TABLE, and measure again with the same query.

UPDATE dbo.HeapDemo SET Payload = REPLICATE('x', 1500);

SELECT N'Before' AS Stage, index_id, page_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.HeapDemo'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = N'IN_ROW_DATA';

ALTER TABLE dbo.HeapDemo REBUILD;

SELECT N'After' AS Stage, index_id, page_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.HeapDemo'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = N'IN_ROW_DATA';

Before the rebuild, 45 of the 50 rows are forwarded. After it, none are. Look at the page count too. It is 10 in both grids, so this rebuild did not shrink the table. It removed the pointers. Read both numbers, not just the one you hoped would change.

DETAILED mode reads the whole heap to count forwarded records. That is fine on 50 rows. On a big table, use it on purpose, and not in a loop.

Check that no data was touched

A rebuild moves rows. It should never change them. A quick look at two sample rows costs nothing.

SELECT Id, DATALENGTH(Payload) AS PayloadBytes
FROM dbo.HeapDemo
WHERE Id IN (1, 50)
ORDER BY Id;

Both rows still hold 1,500 bytes.

The repair is not permanent

Here is the part people skip. Run the filling-in job again, this time with 2,000 bytes per row, and count the forwarded records.

UPDATE dbo.HeapDemo SET Payload = REPLICATE('y', 2000);

SELECT N'After next update' AS Stage, index_id, page_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.HeapDemo'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = N'IN_ROW_DATA';

The forwarded records are back: 10 of them, and the table has grown to 14 pages. The rebuild repaired the layout at that moment. It did nothing about the habit of widening rows after the insert.

Rebuild, or change the design

If the table is a staging heap that you load and clear, a rebuild now and then is a fair answer. If an application table keeps expanding rows, ask why. Maybe the rows should start at their final size, or the table needs a clustered index so that rows have a home. Either way, budget time and locks for the rebuild, and try it on a copy first. Keep the measurement from before the rebuild next to the one from after, and write down what caused the forwarding. Then clean up.

DROP TABLE IF EXISTS dbo.HeapDemo;

Next time a heap gets slow, count the forwarded records before you rebuild, and ask what widened the rows.

A heap rebuild is not a lasting access plan, it is a repair of today’s layout.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.