SQL Server – the curse of heaps

A HEAP in SQL Server is a table without a clustered index, and in this blog post I’ll show you the curse of heaps. Run the code below and take a look at the record_count and page_count for each result:

— create a table with a large char column to create as many pages as records, just for the demonstration
CREATE TABLE dbo.HeapCurse(id INT IDENTITY(1,1), BigChar CHAR(4096)); 

SELECT ‘CREATED’, ips.[record_count], ips.[page_count] ,[ips].[avg_record_size_in_bytes]
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘HeapCurse’), NULL, NULL, ‘DETAILED’) ips; 

INSERT INTO dbo.HeapCurse (BigChar)
SELECT TOP 1000 ‘Håkan Winther’
FROM sys.columns c1 CROSS APPLY sys.columns c2;

SELECT ‘INSERTED’, ips.[record_count], ips.[page_count],[ips].[avg_record_size_in_bytes]
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘HeapCurse’), NULL, NULL, ‘DETAILED’) ips;

DELETE FROM dbo.[HeapCurse];

SELECT ‘DELETED’, ips.[record_count], ips.[page_count], [ips].[avg_record_size_in_bytes]
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘HeapCurse’), NULL, NULL, ‘DETAILED’) ips;

INSERT INTO dbo.HeapCurse (BigChar)
SELECT TOP 1000 ‘Håkan Winther’
FROM sys.columns c1 CROSS APPLY sys.columns c2;

DELETE FROM dbo.[HeapCurse] WITH(TABLOCK);

SELECT ‘DELETED WITH TABLOCK’, ips.[record_count], ips.[page_count], [ips].[avg_record_size_in_bytes]
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘HeapCurse’), NULL, NULL, ‘DETAILED’) ips;

INSERT INTO dbo.HeapCurse (BigChar)
SELECT TOP 1000 ‘Håkan Winther’
FROM sys.columns c1 CROSS APPLY sys.columns c2;

TRUNCATE TABLE dbo.[HeapCurse];

SELECT ‘TRUNCATED’, ips.[record_count], ips.[page_count], [ips].[avg_record_size_in_bytes]
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘HeapCurse’), NULL, NULL, ‘DETAILED’) ips;

DROP TABLE DBO.[HeapCurse];

Did you expect this result? As you can see in this results, the pages in the heap is not deallocated when you delete the records, and they can’t be reused by another table. This results in 8kb pages that are like “living dead” (Zombies), they will stay there in the table and haunt your storage solution. In this case, I lost 8192kb space until I finally killed the Zombies. 

As you could see, by using the TABLOCK hint when you deleted the records most of the pages are deallocated, but not all of them. To really deallocate all pages, you need use one of the following methods:

• truncate the table
• create a clustered index and then drop the index

The second option is more time consuming, but you don’t need to delete all records.

Of course, the best option is to create a clustered index and keep it, but if you really think you have a good argument for using a HEAP, drop the clustered index.

/Håkan Winther