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

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.

Reference

 

Related Articles & Tools

Leave your thoughts...

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