Fixing the Fragmentation of HEAPS

Introduction

A table objects that not containing any Clustered Index is called Heap. It does not mean that this table has no indexes. The table may contain several non clustered indexes on it.

I personally not recommended any kind of Heap table in database. But sometime we find it as for functional support. I do not understand why we create the Heap table? If we do not have any option to primary key we can use the surrogate key.

The problem lies when a Heap table is highly fragmented.

This article is trying to identify the Heap table fragmentation issue and try to defragment it.

How to Identify Heap table Fragmentation Percent

To identify whether your heap table is fragmented, we 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.tbl_EMPLOYEE');

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 we should use zero (0) when the table is a heap.  If you use zero and the table is not a heap, you will receive an error.

How to Defrag Heap

1.    Using the REBUILD option of ALTER TABLE (Only MS SQL 2008 and higher)

ALTER TABLE dbo.tbl_EMPLOYEE REBUILD;

GO

2.    We will need to select a column to create the clustered index on; keeping in mind this will reorder the entire table by that key.  Once the clustered index has been created, immediately drop it.

CREATE CLUSTERED INDEX cluIdx1 ON dbo. tbl_EMPLOYEE (col1);

GO

DROP INDEX cluIdx1 ON dbo. tbl_EMPLOYEE;

      GO

3.    By manually moving all data to a new temporary table. 

CREATE TABLE dbo.tbl_EMPLOYEE_Temp(col1 INT,col2 INT);

GO

INSERT dbo.tbl_EMPLOYEE_Temp

SELECT * FROM dbo.tbl_EMPLOYEE;

GO

Next, drop the old table, rename the temporary table to the original name, and then create the original non-clustered indexes.

DROP TABLE dbo.tbl_EMPLOYEE;

GO

EXEC sp_rename 'tbl_EMPLOYEE_Temp','tbl_EMPLOYEE';

GO

CREATE NONCLUSTERED INDEX idx1 ON dbo.tbl_EMPLOYEE(col1);

GO

CREATE NONCLUSTERED INDEX idx2 ON dbo.tbl_EMPLOYEE(col2);

GO

Hope you like it.

Posted By: MR. JOYDEEP DAS