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 @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.dbo.DatabaseLog');
IF @object_id IS NULL
PRINT N'Invalid object';
  SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');

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:

  1. 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)
  2. Create a new table and insert data from the heap table into the new table based on some sort order
  3. 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?

Clustered Tables vs Heap Tables

Back To Basics: Heaps