Table of Contents

What is fragmentation?

A fragmented table is a table where some of its data pages point to pages that are not immediately following pages in the extent, and if all its pages are contiguous in both the allocation plan and the extents, then the table is not fragmented.

An example to better understand :

Imagine that there are 3 pages of data for a table with a cluster index. A new row with a primary key of "5" must be inserted, and since it is a clustered index, the new row is inserted in order. Because the target page is sufficiently complete that the new row can not be inserted, SQL Server divides the page into approximately two and inserts the new data on the new page, as shown in the figure above.

Now, the logical order of the index does not correspond to the physical order, and the index has become fragmented.

How to detect fragmentation ?

To measure the fragmentation of the indexes, you can run the following query on your database :

SELECT
  I.index_id, I.name,
  DM.avg_fragmentation_in_percent
FROM
  Sys.dm_db_index_physical_stats(db_id(),
  NULL, NULL, NULL, DEFAULT) DM
JOIN sys.Indexes I
ON I.object_id=DM.object_id
AND I.Index_id=DM.index_id

Result :

How to eliminate fragmentation ?

To reduce fragmentation (Defragmentation) you can use the [ALTER INDEX] [REBUILD | REORGANIZE] command :

1. Alter index reorganize

  • Rearranges only leaf pages, and compresses index pages that delete empty pages.
  • Less effective than rebuilding indexes.
  • To be done when 5%<=avg_fragmentation_in_percent<=30% and the count of pages higher than 2000
  • Warning : Does not update statistics.

Query T-SQL :

USE AdventureWorks2012;  
GO 
-- Reorganize all indexes on the HumanResources.Employee table. 
ALTER INDEX ALL ON HumanResources.Employee 
REORGANIZE ;  
GO

2. Alter index rebuild

  • Recreating the index, when the index is clustered, table is also reorganized.
  • Caution : The reconstruction of a clustered index with [ALTER INDEX REBUILD] does not rebuild nonclustered indexes on the table (v>=7.0) unless [ALL] is specified.
  • To be done when avg_fragmentation_in_percent>30% and the count of pages higher than 2000 The [ONLINE=ON] option allows you to rebuild an index without blocking the activity.

Query T-SQL : 

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

Defragmentation of an index (Reorganize)

To understand how SQL Server defragments an index, consider a simplified example of pages after many insertions, updates, and deletions, as shown in the following figure :

Page numbering represents the logical sequence of pages.

However, the physical sequence as shown in the figure from left to right does not correspond to the logical sequence.

The following figure illustrates several passes during the defragmentation and reorganization process, causing re-scheduling of physical pages by changing the first logical page with the first physical page and swapped with the second physical page and so on.

During the first pass, SQL Server finds the first physical page (4) and the first logical page (1), and then exchanges these pages in a discrete transaction.

On the second pass, SQL Server exchanges the next physical page (7) with next logical page (2).

On the third pass, SQL Server exchanges the next physical page (4) with the next logical page (3).

On the fourth pass, SQL Server exchanges the next physical page (5) with the next logical page (4).

Sorting is now complete because all physical pages correspond to their logical positions.