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
SELECT 
    OBJECT_NAME(ips.OBJECT_ID) as [Object], 
    i.name AS [Index Name],
    index_type_desc AS [Index Type],
    index_level,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent,
    page_count
FROM 
	sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED') AS ips
INNER JOIN 
	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

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.

Reference

• About sys.dm_db_index_physical_stats at Microsoft Docs.