The Curious Case of: the empty heap table

Question: We’re using heaps for some of our tables in a a new database for a variety of reasons and we’ve found a strange phenomenon where if we delete lots of data from the tables, the amount of space used by the tables does not go down. What’s going on?

Answer: The behavior you’re seeing is how SQL Server works, but it’s non-intuitive.

Whenever a row is deleted in a heap, if the page that the row is stored on becomes empty, the page cannot be deallocated from the table unless an exclusive table lock is held. This is usually not the case unless lock escalation occurs because you’re deleting enough rows to trigger escalation, or if you specifically use the TABLOCK hint on the delete statement.

There is a Knowledge Base article that describes this phenomenon – KB 913399. However the KB article only references up to and including SQL Server 2005. This behavior exists in more recent releases too and is very easy to reproduce. The script below will generate the scenario (I was running on SQL Server 2008 SP2):

PaulsDB;
        GO
        GO
CREATE TABLE t1 (c1 CHAR (8000) DEFAULT 'a');
        GO
INSERT INTO t1 DEFAULT VALUES;
        GO 100
DELETE FROM t1;
        GO
<pdbcc br="" ind="">    GO</pdbcc>

The DBCC IND output gives one row per page allocated to the table and the script above returns 101 rows (100 data pages and 1 allocation bitmap page). If you use DBCC PAGE to examine any of the data pages you’ll see that they’re all empty.

If that space isn’t going to be reused, I would consider avoiding this phenomenon through one of the workarounds (use TABLOCK, for instance) or simply use a clustered index instead.

Discuss this Blog Entry 2

on Sep 28, 2011
I was pointed to this entry from http://www.sqlservercentral.com/Forums/Topic1182140-392-1.aspx Paul, you say 'If that space isnt going to be reused', why might it not be reused? In the situation I have it appears that the new data is just being appended to the end of the table, and the free space is never getting freed up. There appears to be no command to recover the space, so once the problem is there the options are to create (and then drop) a clustered index, or copy the data 'somewhere' truncate and replace. I think there is a missing DBCC command, or an enhancement to one of the background processes required. pcd
on Jun 29, 2011
Would DBCC FORCEGHOSTCLEANUP help in this situation?

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×