Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Fixing the Fragmentation of HEAPS

DZone's Guide to

Fixing the Fragmentation of HEAPS

· Java Zone ·
Free Resource

Verify, standardize, and correct the Big 4 + more– name, email, phone and global addresses – try our Data Quality APIs now at Melissa Developer Portal!

Introduction

A table object that doesn't contain a 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 dont recommend 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 is 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.

Developers! Quickly and easily gain access to the tools and information you need! Explore, test and combine our data quality APIs at Melissa Developer Portal – home to tools that save time and boost revenue. 

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}