The amazing never shrinking heap

Leave a comment

August 25, 2014 by Kenneth Fisher

This is a quick demo of a little “trick” with heaps I’ve known about for a couple of years. However until recently I could never duplicate it on purpose. (You can read that as I’ve had a production problem bite me in the …. repeatedly.) At least I couldn’t duplicated it until I watched Kendra Little’s (b/t) video on heaps. Kendra goes into a great deal more detail on heaps than I will be here. Fair warning though if you are a beginner or dabbler it may be a bit tough in spots. She is using a lot of DMOs and some undocumented commands as well. If you feel comfortable with the skill level however, I highly recommend watching it.

On to the demo:

-- Create a test table.
CREATE TABLE HeapSpace (Id int NOT NULL identity(1,1), 
	Code char(1), Col1 varchar(1000), Col2 varchar(1000));
GO
-- Load the test table with some values and check the table size.
INSERT INTO HeapSpace (Code, Col1) VALUES ('A', REPLICATE('A',50));
GO 10000
EXEC sp_spaceused 'HeapSpace'; 
GO

The system stored procedure sp_spaceused will return to us (among other things) the amount of space reserved by the table and how much of it is free.

name rows reserved data index_size unused
HeapSpace 10000 712 KB 704 KB 8 KB 0 KB

-- Add some more values to the table and check the table size again.
INSERT INTO HeapSpace (Code, Col1) VALUES ('B', REPLICATE('A',50));
GO 10000
EXEC sp_spaceused 'HeapSpace';
GO
name rows reserved data index_size unused
HeapSpace 20000 1416 KB 1408 KB 8 KB 0 KB

Note that the amount of space used has about doubled and the unused space is still 0.
 

-- Do some processing on one of our data sets.
UPDATE HeapSpace SET Col2 = REPLICATE('B',50);
GO
EXEC sp_spaceused 'HeapSpace';
GO
name rows reserved data index_size unused
HeapSpace 20000 2696 KB 2648 KB 8 KB 40 KB

Processing the data has added almost another 50% to the size of the table.
 

-- Get rid of the first block of data.
DELETE FROM HeapSpace WHERE Code = 'A';
GO
EXEC sp_spaceused 'HeapSpace';
GO
name rows reserved data index_size unused
HeapSpace 10000 1928 KB 1872 KB 8 KB 48 KB

Here you can see we have deleted half the data and only reduced the reserved space of the table by 700KB (say a quarter of the total space).
 

-- Get rid of the rest of the data.
DELETE FROM HeapSpace WHERE Code = 'B';
GO
EXEC sp_spaceused 'HeapSpace';
GO
name rows reserved data index_size unused
HeapSpace 0 1224 KB 1096 KB 8 KB 120 KB

Now we have 1224KB reserved for the table and only 120KB of that is “unused”. That means there should be 1104 KB of data right? But wait just a minute. I have zero rows so no data!

Now to be fair if I truncate the table it will all go back to 0. However in my case only part of the data is cleared out at any point in time. Over and over data is loaded, processed, and cleared out. Millions of rows at a time. Every couple of month or so I hear “I’ve run out of space again.” I clear it out, tell them they really need a clustered index, and in a another month I’m clearing space up again.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: