SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. 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 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...