Index Fragmentation in SQL server

We know data in SQL server data are stored on 8kb pages. When we are UPDATE or INSERT data on a page that is already full, SQL Server creates a new page. The information from the original page will be split 50/50 with half being added to the new page, along with the new record. Doing this causes empty space at the end of the page(s). This empty space is known as Internal Fragmentation, it also occurs when we delete data from a page.

 

This newly created page won’t sit in sequence with the original page(s) meaning the related data is now stored in a different area of the disk. This separated data is called External Fragmentation. External Fragmentation is caused by pages that are out of order. 

Check Index Fragmentation

Below query is used to get the list of index and fragmentation percentage.

SELECT s.NAME + '.' + t.NAME                  AS table_name,        i.NAME                                 AS index_name,        index_type_desc,        Round(avg_fragmentation_in_percent, 2) AS avg_fragmentation_in_percent,        ips.fragment_count,        ips.avg_fragment_size_in_pages,        record_count                           AS table_record_count FROM   sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, 'SAMPLED') ips        INNER JOIN sys.tables t                ON t.[object_id] = ips.[object_id]        INNER JOIN sys.schemas s                ON t.[schema_id] = s.[schema_id]        INNER JOIN sys.indexes i                ON ( ips.object_id = i.object_id )                   AND ( ips.index_id = i.index_id ) ORDER  BY avg_fragmentation_in_percent DESC

 

avg_page_space_used_in_percent shows the average percentage of the data storage space used on the page. This value allows us to see the internal index fragmentation.

avg_fragmentation_in_percent provides us information about external index fragmentation. For tables with clustered indexes, it indicates the percent of out-of-order pages when the next physical page allocated in the index is different from the page referenced by the next-page pointer of the current page. For heap tables, it indicates the percent of out-of-order extents, when extents are not residing continuously in data files.

fragment_count indicates how many continuous data fragments the index has. Every fragment constitutes the group of extents adjacent to each other. Adjacent data increases the chances that SQL Server will use sequential I/O and Read-Ahead while accessing the data.

After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.