Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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


Leave a comment on the original post [, opens in a new window]

Loading comments...