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