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.
- About sys.dm_db_index_physical_stats at Microsoft Docs.