What is fragmentation?

What is fragmentation? How to detect fragmentation and how to eliminate it?

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 all 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 data pages for a table with a clustered index.A new line with a primary key of "5" must be inserted, and since it is a clustered index, the new line is inserted in order. Because the target page is full enough that the new row can not be inserted, SQL Server divides the page in half and inserts the new data on the new page, as shown in the figure above. Now, the logical order of the index does not match the physical order, and the index has become fragmented.

To measure index fragmentation, 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


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

  • Alter Index Reorganize :
    • Rearranges leaf-only pages, and compresses index pages that remove empty pages.

    • Less efficient than an index rebuild

    • To do when 5% <= avg_fragmentation_in_percent <= 30%

    • Warning: do not update the statistics

    • T-SQL query:

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


  • Alter Index Rebuild :
    • Recreating the index. When the index is clustered, the table is also reorganized.

    • Caution: A clustered index rebuild with ALTER INDEX REBUILD does not rebuild nonclustered indexes on the table (v> = 7.0) unless the ALL option is specified

    • To do when avg_fragmentation_in_percent> 30%

    • The option ONLINE = ON allows to rebuild an index without blocking the activity.

    • T-SQL query:

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



Leave a comment

Leave a Reply

Your email address Will not be published. Required fields are marked *