SQL Server: Heap page deallocation and IndexOptimize

Introduction

HEAP tables are a type of data structure that, unlike a clustered index table, does not have a specific ordering of its rows, making them faster to insert and delete data.

They are often used for temporary “staging” tables.

This is all fine. But, if your table is never dropped and reused over time for data loading, you need to be aware of how SQL Server manages HEAPs pages.

I recently was asked by a customer why a HEAP table would consume significantly more disk space than the same table with a clustered index. Do you have any idea why?”

HEAP page deallocation

Well, SQL Server does not deallocate pages when doing a DELETE on a Heap. When a row is deleted, it does not mark the space as reusable, as a result, the space remains allocated and consumes more disk space.
This may seem surprising but it is documented in Microsoft Docs.
See: Space that a table uses is not completely released after you use a DELETE statement to delete data from the table in SQL Server

After you use a DELETE statement in Microsoft SQL Server to delete data from a table, you may notice that the space that the table uses is not completely released.

There’s also a great article by Paul Randal on the topic:
SQLskills SQL101: Why does my heap have a bunch of empty pages?

The table I was asked about is a heap table with a size of approximately 130GB. This table experiences a high volume of data modification, with almost the entire table being deleted and inserted again on a daily basis.
In this specific scenario, the best workaround would be to perform a TRUNCATE statement on the table prior to data loading, as this would clear out any unused pages and release the space back to the operating system.

Another workaround that is not mentioned in the documentation is to perform index maintenance periodically on the HEAP with a table REBUILD, this will defragment the heap and make the space more contiguous, as well as release any unused space back to the operating system.

IndexOptimize and HEAP fragmentation

The Ola Hallengren maintenance script is a widely-used open-source solution for maintaining the performance and integrity of SQL Server databases. One of the features of the script is its ability to rebuild indexes to fix fragmentation.

However, the script does not include any specific functionality for rebuilding heaps or fixing heap fragmentation.

It has been two years since an issue was opened on GitHub for this feature, and we are still waiting for it to be implemented.

Heap empty pages and IndexOptimize demo

Here is an example of creating a HEAP table with 3000 empty and unallocated pages. Running the indexOptimize procedure will not address this table, and therefore will not release this unused space.

use master
go
DROP DATABASE IF EXISTS Heapo;
CREATE DATABASE Heapo;
GO

USE Heapo;
GO
CREATE TABLE HeapTest (
	[c1] INT IDENTITY
	, [c2] VARCHAR(4000) DEFAULT replicate('a', 4000)
	, [c3] VARCHAR(4000) DEFAULT ''
);
GO

-- Insert 3000 rows, which will allocate 1500 pages
INSERT INTO HeapTest DEFAULT VALUES;
GO 3000

In this table, we can observe that there are 3000 rows with 1500 pages.

SELECT [page_count], [record_count], [avg_page_space_used_in_percent]
FROM sys.dm_db_index_physical_stats (DB_ID (N'Heapo'), OBJECT_ID (N'HeapTest'), 0,  DEFAULT, 'DETAILED');
GO

Let’s delete all the rows.

-- Delete all the rows
DELETE FROM HeapTest;
GO

Rows are still allocated:

Now, what happens if we run Ola Hallengren’s index maintenance script.

exec master.dbo.IndexOptimize @Databases = 'Heapo'

Same things. IndexOptimize does not do anything about HEAPs.

Rebuilding the index will release the unused pages.

ALTER TABLE HeapTest REBUILD

Conclusion

HEAP tables in SQL Server are a powerful data structure that can be used to store temporary data or large volumes of data that are inserted, updated, and deleted frequently.
However, it is important for DBAs to be aware of the issues with deallocations and to manually address HEAP issues as needed.
One strategy is to periodically rebuild the HEAPs using the ALTER TABLE … REBUILD command.