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:

  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

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQL Server, SQLServerPedia Syndication. Bookmark the permalink.

One Response to Reduce heap table fragmentation

  1. Subramanian kaushik Gurumoorthy says:

    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 ?

Leave a Reply

Your email address will not be published. Required fields are marked *