Heap Table Maintenance

I have a customer who let me know that some of their tables had a large amount of unused space in them.  They were wondering if I could get them to release the space.  After doing some investigation, I found that all tables with the huge amount of unused space were heap tables.

I advised that we needed to rebuild the tables to get them to release the space.

ALTER TABLE ProductionPlanning REBUILD
ALTER TABLE Operations REBUILD
ALTER TABLE TechServices REBUILD
ALTER TABLE LaneVariance REBUILD

That released over 170GB of space.  The customer was thrilled!  But, since I didn’t know exactly what had caused all the extra space, I thought that I should build a process to detect heap tables that have a significant amount of free space and then automatically rebuild them.

I thought about how to determine which tables to work on and decided to work on tables with more than 1GB of unused space in it.  You can, of course, change this value that works for your shop.  The space filter is in this line in the code below:  (SELECT [Table] FROM #HeapSpaceUsed WHERE UnusedKB > 1048576) — 1GB

Here is the heap table maintenance that I came up with:

DECLARE @TableName NVARCHAR(128)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @SchemaTableStr NVARCHAR(250)
DECLARE @SQLStr NVARCHAR(MAX)

IF (SELECT OBJECT_ID('tempdb..#Heaps')) IS NOT NULL
	DROP TABLE #Heaps
CREATE TABLE #Heaps
	([Schema] NVARCHAR(128),
	[Table] NVARCHAR(128));

IF (SELECT OBJECT_ID('tempdb..#HeapSpaceUsed')) IS NOT NULL
	DROP TABLE #HeapSpaceUsed
CREATE TABLE #HeapSpaceUsed 
	([Table] NVARCHAR(128),
	[Rows] BIGINT,
	[ReservedKB] VARCHAR(18),
	[DataKB] VARCHAR(18),
	[IndexSizeKB] VARCHAR(18),
	[UnusedKB] VARCHAR(18));

INSERT INTO #Heaps ([Schema],[Table])
SELECT SCHEMA_NAME(t.schema_id) AS [Schema], t.name AS [Table]
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
WHERE i.index_id = 0 
AND i.type = 0 
AND t.is_ms_shipped = 0
ORDER BY [Schema], [Table]

DECLARE SpaceUsedCursor CURSOR FOR
SELECT QUOTENAME([Schema]) + '.' + QUOTENAME([Table])
FROM #Heaps

OPEN SpaceUsedCursor
FETCH NEXT FROM SpaceUsedCursor INTO @SchemaTableStr
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLStr = 'INSERT INTO #HeapSpaceUsed ([Table], [Rows], [ReservedKB], [DataKB], [IndexSizeKB], [UnusedKB])
	EXEC sp_spaceused N'''+@SchemaTableStr+''''
	--PRINT @SQLStr
    EXEC sp_executesql @SQLStr
    FETCH NEXT FROM SpaceUsedCursor INTO @SchemaTableStr
END

CLOSE SpaceUsedCursor
DEALLOCATE SpaceUsedCursor

-- strip all the KB designations from the numeric values
UPDATE #HeapSpaceUsed
SET ReservedKB = SUBSTRING(ReservedKB,1,CHARINDEX(' ',ReservedKB,1)),
DataKB = SUBSTRING(DataKB,1,CHARINDEX(' ',DataKB,1)),
IndexSizeKB = SUBSTRING(IndexSizeKB,1,CHARINDEX(' ',IndexSizeKB,1)),
UnusedKB = SUBSTRING(UnusedKB,1,CHARINDEX(' ',UnusedKB,1))

--SELECT * FROM #Heaps
--SELECT * FROM #HeapSpaceUsed

SET @SchemaTableStr = ''  -- empty this out since we are reusing it

DECLARE HeapTableCursor CURSOR FOR
SELECT QUOTENAME([Schema]) + '.' + QUOTENAME([Table])
FROM #Heaps
WHERE [Table] IN (SELECT [Table] FROM #HeapSpaceUsed
					WHERE UnusedKB > 1048576) -- 1GB

OPEN HeapTableCursor
FETCH NEXT FROM HeapTableCursor INTO @SchemaTableStr

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLStr = 'ALTER TABLE ' + @SchemaTableStr + ' REBUILD;'
	--PRINT @SQLStr
    EXEC sp_executesql @SQLStr
    FETCH NEXT FROM HeapTableCursor INTO @SchemaTableStr
END

CLOSE HeapTableCursor
DEALLOCATE HeapTableCursor

Of course, this should be run during a maintenance window because during the rebuild, the table will be locked which will cause blocking.  You can put this into a stored procedure or a job step and it should do it’s job to make sure that heap tables with a good amount of unused space are maintained.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!