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 currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQL Server, SQLServerPedia Syndication. Bookmark the permalink.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>