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.

-- 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

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

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

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

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.

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication Tagged: heap, language sql, microsoft sql server, problem resolution