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 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 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 complete enough that the new row cannot be inserted, SQL Server roughly halves the page 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

result:

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

• Alter Index Reorganize :

  • Rearranges leaf-level pages only, and compresses index pages removing 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.

  • Warning: 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 activity.

  • T-SQL query:

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