SQL SERVER – Fragmentation – Detect Fragmentation

When data is inserted into, deleted from, or updated in a SQL Server table, the indexes defined on that table are automatically updated to reflect those changes. As the indexes are modified, the information stored in them becomes fragmented, resulting in the information being scattered across the data files. When this occurs, the logical ordering of the data no longer matches the physical ordering, which can lead to a deterioration of query performance.

We can classify fragmentation into two types:

Internal Fragmentation

When records are stored non-contiguously inside the page, then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page. This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table. As these modifications are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time. This unused space causes poor cache utilization and more I/O, which ultimately leads to poor query performance.

External Fragmentation

When on disk, the physical storage of pages and extents is not contiguous. When the extents of a table are not physically stored contiguously on disk, switching from one extent to another causes higher disk rotations, and this is called Extent Fragmentation.

How to detect Fragmentation:

We can get both types of fragmentation using the DMV: sys.dm_db_index_physical_stats.

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ind.name AS IndexName, indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

INNER JOIN sys.indexes ind

ON ind.object_id = indexstats.object_id

AND ind.index_id = indexstats.index_id

WHERE indexstats.avg_fragmentation_in_percent > 10--You can specify the percent as you want

ORDER BY indexstats.avg_fragmentation_in_percent DESC

SELECT b.name AS IndexName,

  a.avg_fragmentation_in_percent AS PercentFragment,

  a.fragment_count AS TotalFrags,

  a.avg_fragment_size_in_pages AS PagesPerFrag,

  a.page_count AS NumPages

 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS a

 JOIN sys.indexes AS b

 ON a.object_id = b.object_id

 AND a.index_id = b.index_id

 WHERE a.avg_fragmentation_in_percent > 0

 ORDER BY IndexName

Reorganizing Indexes

When you reorganize an index, SQL Server physically reorders the leaf-level pages to match the logical order of the leaf nodes. The process uses the existing pages only and does not allocate new ones, but it does compact the index pages. In addition, reorganization uses minimal resources and is automatically performed online, without blocking queries or updates. You should reorganize indexes only if they’re lightly fragmented, otherwise, you should rebuild them.

ALTER INDEX PK_CompletedOrders

ON CompletedOrders

REORGANIZE

Rebuilding Indexes

Rebuilding an index is generally more effective than reorganizing it because it drops the original index and builds a new one. As a result, the index starts clean with minimal fragmentation and the pages are compacted, and new pages are allocated as needed. In addition, you can choose to rebuild an index offline (the default) or online.

ALTER INDEX PK_CompletedOrders

ON CompletedOrders

REBUILD

About Author:
Akash Verma is budding technology geek, who actively contributes to Systems Plus with his creativity and research on technology. He can be contacted at akash.v@spluspl.com