DELETE data on SQL Server HEAP table - Did you know...
Before I complete my question let me provide context.
I’ve received an alert saying that a specific database could not allocate a new page (disk was full)
The message that you will see on the SQL Server Error log is:
I didn’t know the database structure or what is saved there, so I picked up a script from my toolbelt that shreds all indexes from all table. Just some information like number of rows and space that it is occupying. I have sorted by occupying space in descending order, look what I found…
So…my script has a bug? :-) No, it hasn’t!
The joy of heaps
The joy of heaps
First, the definition:
Source: MS Docs - Heaps (Tables without Clustered Indexes)
Until now, everything seems normal, it is just a table with unordered data.
Why am I talking about heaps?
Not because of table name (was created on purpose for this demo), let me show to you the whole row of the script:
Do you have a clue? Yup,
index_id = 0. That means that our table does not have a clustered index defined and therefore it is an HEAP.
Even so, how it is possible? 0 rows but occupying several MB…
The answer is…on the documentation :-)
source: DELETE (Transact-SQL) - Locking behavior
That explains it!
So…what should I do in order to get my space back when deleting from a HEAP?
On the same documentation page we can read the following:
Following the documentation, it suggest we can to use the TABLOCK hint in order to release the empty pages when deleting the data. Example:
DELETE FROM dbo.Heap WITH (TABLOCK)
What if I didn’t that way or if anyone else run a DELETE without specify it?
You can rebuild your table using this syntax (since SQL Server 2008):
ALTER TABLE dbo.Heap REBUILD
This way, the table will release the empty pages and you will recovery the space to use on other objects in the database.
I hope that with this little post you understood how and why a HEAP can have few rows or even zero but still occupy lots of space. Also I have mentioned two ways to solve this problem. Also, I have found databases with dozens of HEAPS almost empty or even empty that were occupying more than 50% of the total space allocated to the database. And guess what? People where complaining about space.
To finish, I need to complete the title, Did you know…you should use TABLOCK hint when deleting data from a HEAP?
Thanks for reading!