What is Index Fragmentation and how to detect it.?

Problem: What is Index Fragmentation and how to detect it.?

Solution:

Index Fragmentation: When there are data modifications or new data entries on a table, the pages in the index gets full and the new rows gets into a place where they need some new pages and SQL server tries to split the pages roughly in between rows. The logical order of the index may not match the physical order of the index. This can cause Indexes to be fragmented.

you can see the details of index fragmentation by clicking on the properties of an index and pages occur.

There are two types of fragmentation:

Logical Fragmentation

This is the percentage of out-of-order pages in the leaf pages of an index. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

Extent Fragmentation

This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

How to detect this:

sys.dm_db_index_physical_stats (Transact-SQL)

There are few Index Related Dynamic Management views and functions that can be helpful to detect missing indexes and stats of the indexes. Among those the index physical stats function will show us a database particular index fragmentation with respect to each index.

So the syntax looks like these and the function accepts few parameters and can be null by default or specify to identify particular database, table, index, partition  and mode to scan.

sys.dm_db_index_physical_stats (  

    { database_id | NULL | 0 | DEFAULT }—database id of the database name

  , { object_id | NULL | 0 | DEFAULT } -–object you want to look for table or view

  , { index_id | NULL | 0 | -1 | DEFAULT }—particular index you want to focus on   

  , { partition_number | NULL | 0 | DEFAULT }—partition number of index or heap.

  , { mode | NULL | DEFAULT } –mode of scan to use for obtaining the stats.

) 

The acceptable percentage of average fragmentation of index is between 0 to 10 percent and if it crosses more than that it should be considered as badly fragmentated.

Example:

Example 1:

In this example I would like to show how we can see top 5 most fragmented indexes in the database.

SELECT TOP 5 OBJECT_NAME(stats.OBJECT_ID) as table_name,

                i.name as index_name,

                index_type_desc,

                index_level,

                avg_fragmentation_in_percent,

                avg_page_space_used_in_percent,

                page_count

FROM sys.dm_db_index_physical_stats(DB_ID(N'DB'), NULL, NULL, NULL, 'DETAILED') stats

LEFT JOIN sys.indexes i ON

i.index_id=stats.index_id

and i.object_id=stats.object_id

ORDER BY avg_fragmentation_in_percent DESC

OUTPUT:

table_name	index_name	index_type_desc	index_level	avg_fragmentation_in_percent	avg_page_space_used_in_percent	page_count
Address	ui_uAddress	NONCLUSTERED INDEX	1	100	36.9804793674327	5
Invoice	NonClusteredIndex-20170110-211924	NONCLUSTERED INDEX	0	98.8212180746562	63.7155423770694	509
Address	ui_uSubject	NONCLUSTERED INDEX	1	80	37.0126019273536	5
Invoice	NULL	HEAP	0	55.8823529411765	95.5950580677045	783
Employees	PK_Employees	CLUSTERED INDEX	1	50	62.1123671855696	16

Example 2:

In this example I would like to show how we can detect index fragmentation on a particular table in a database.

SELECT OBJECT_NAME(stats.OBJECT_ID),

                i.name as index_name,

                index_type_desc,

                index_level,

                avg_fragmentation_in_percent,

                avg_page_space_used_in_percent,

                page_count

FROM sys.dm_db_index_physical_stats(DB_ID(N'DB'), object_id('Employees'), NULL, NULL, 'LIMITED') stats

LEFT JOIN sys.indexes i ON

i.index_id=stats.index_id

and i.object_id=stats.object_id

ORDER BY avg_fragmentation_in_percent DESC

OUTPUT:

table_name	index_name	index_type_desc	index_level	avg_fragmentation_in_percent	avg_page_space_used_in_percent	page_count
Employees	ix_name_phonenumber	NONCLUSTERED INDEX	0	0.194390447098028	NULL	3601
Employees	AK_Name_PhoneNumber	NONCLUSTERED INDEX	0	0.146076794657763	NULL	4792
Employees	PK_Employees	CLUSTERED INDEX	0	0.113085621970921	NULL	6190

In the next article let’s focus on how to get rid of index fragmentation and how to avoid indexes to get fragmented.