Deleting From a Heap: Why Empty Pages Stay Behind

Deleting from a heap removes the rows but often leaves the empty pages behind. The table looks smaller in a SELECT COUNT and exactly the same size on disk. Two fixes exist, and they cost different things.

The archive job that freed nothing

Someone writes an archive job. It deletes most of an old log table, the job succeeds, and everyone is happy. A week later the table is still the same size, and a report that scans it is no faster. The rows are gone. The pages are not.

A heap is a table with no clustered index. When you delete rows from it with ordinary row or page locks, SQL Server usually keeps the emptied pages assigned to the table. Let me prove it with a small demo. It creates a database named SqlAuthorityDemo and drops it at the end.

Build a heap and measure it

The table has 1,000 rows of about 1,000 bytes each. We measure it with sys.dm_db_index_physical_stats. Index zero means the heap itself, and DETAILED mode gives exact page counts.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.HeapDemo (ItemId int NOT NULL, Payload char(1000) NOT NULL);

INSERT dbo.HeapDemo (ItemId, Payload)
SELECT value, REPLICATE('x', 1000) FROM GENERATE_SERIES(1, 1000);

SELECT page_count, record_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.HeapDemo'), 0, NULL, 'DETAILED');

On my run, the heap holds 143 pages for 1,000 rows, with no forwarded records. Your page count may differ a little on another version or setting.

Delete the rows the ordinary way

Now delete 900 of the 1,000 rows, the way a normal job would.

DELETE dbo.HeapDemo WHERE ItemId <= 900;

SELECT page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.HeapDemo'), 0, NULL, 'DETAILED');

One hundred rows left, and still 143 pages. Ninety percent of the data is gone and not a single page came back. A scan has to visit every page, even the empty ones. Check it.

SET STATISTICS IO ON;
SELECT COUNT(*) AS rows_left FROM dbo.HeapDemo;
SET STATISTICS IO OFF;

The message tab says logical reads 143, to count 100 rows. That is the real cost of leftover pages. It is the reason the report never got faster.

Fix it with a rebuild

The first fix is a rebuild after the delete. ALTER TABLE REBUILD rewrites the heap and keeps only the pages it needs. The page count drops from 143 to 15.

ALTER TABLE dbo.HeapDemo REBUILD;

SELECT page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.HeapDemo'), 0, NULL, 'DETAILED');

A rebuild is a second pass over the table. It needs working space, and it also rebuilds any nonclustered indexes on the table. On a very large heap, plan for that.

Or delete with TABLOCK

The second fix happens during the delete itself. With a table lock, SQL Server can free each page as soon as it empties. Reload the table so we start from the same 1,000 rows, and repeat the delete with the hint.

TRUNCATE TABLE dbo.HeapDemo;

INSERT dbo.HeapDemo (ItemId, Payload)
SELECT value, REPLICATE('x', 1000) FROM GENERATE_SERIES(1, 1000);

DELETE FROM dbo.HeapDemo WITH (TABLOCK) WHERE ItemId <= 900;

SELECT page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.HeapDemo'), 0, NULL, 'DETAILED');

Same 100 rows, same 15 pages as the rebuild, with no second pass. The catch is that TABLOCK blocks everyone else from the table while the delete runs. Use it in a maintenance window, not during the busy day.

Which one to pick, and what not to do

If the delete is a regular job on a table nobody reads at night, use TABLOCK. If the table is already bloated, rebuild once. If you empty the whole table, TRUNCATE is simplest. Do not reach for a file shrink first. Freed pages inside the file can be reused by later inserts, and shrinking has its own costs.

Also remember that empty pages and forwarded records are different heap problems. A slow scan is not always fragmentation. Count the pages first, then decide. When you are done, clean up.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time a delete frees nothing, count the pages before you blame the disk.

An empty heap page is not free space, it is still allocated to the table.

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