How To Find Index Fragmentation

One among the important step in improving the performance of SQL Server database is by reducing the fragmentation of its indexes. To find Index fragmentation level, we ca use the system dynamic management view sys.dm_db_index_physical_stats. Here is the T-SQL query using the DMV to find index fragmentation. This query will list all the indexes in the database and the fragmentation level in percent and sorted in descending order.

Query To Find Index Fragmentation

Use WideWorldImporters
    OBJECT_NAME(ips.OBJECT_ID) as [Object], AS [Index Name],
    index_type_desc AS [Index Type],
	sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED') AS ips
	sys.indexes AS i ON ips.OBJECT_ID = i.OBJECT_ID 
	AND ips.index_id = i.index_id
ORDER BY avg_fragmentation_in_percent DESC

Find Index Fragmentation

Reducing Index Fragmentation

After finding the fragmentation level, you have to do either reorganize or rebuild the affected index to reduce the index fragmentation. If the value of avg_fragmentation_in_percent is between 5% to 30% then perform INDEX REORGANIZE. If the avg_fragmentation_in_percent value is above 30% then perform INDEX REBUILD. Read more about index reorganize and index rebuild in the article Reduce Index Fragmentation.



Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.