Reduce heap table fragmentation
A table that does not have a clustered index is referred to as a Heap. While a lot has been written about index fragmentation and how to defrag indexes, there is not much that talks about how to defrag a heap table.
To identify whether your heap table is fragmented, you need to either run DBCC SHOWCONTIG (2000 or 2005) or use the DMV sys.dm_db_index_physical_stats (2005 and later):
DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks2008R2'); SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.dbo.DatabaseLog'); IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED'); END; GO
The third parameter in sys.dm_db_index_physical_stats is for index_id, and you should use zero when the table is a heap. If you use zero and the table is not a heap, you will receive an error.
The following are different options you can take to resolve heap fragmentation:
- Create a clustered index, and then drop it right away if need be (Not many reasons you would want to drop it, unless you are working with PDW which discourages indexes)
- Create a new table and insert data from the heap table into the new table based on some sort order
- Export the data, truncate the table and import the data back into the table
More info:
A SQL Server DBA myth a day: (29/30) fixing heap fragmentation
Reduce SQL Server table fragmentation without adding/dropping a clustered index?
Hi James
I checked with the query for finding fragmentation and found that the average fragmentation is always 0 for certain heaps.
Is that correct ?