Check index fragmentation for SQL Server

Query to find the SQL queries having fragmentation greater than 30%

This SQL query identifies database indexes with fragmentation exceeding 30 percent. It retrieves the associated table name, index name, index type, and average fragmentation percentage using SQL Server’s index physical statistics and system catalog views. Results are filtered for significant fragmentation and sorted in descending order, helping prioritize indexes for maintenance or optimization.

SELECT OBJECT_NAME(ix.object_ID) AS TABLE_NAME,
ix.name as INDEX_NAME,
ixs.index_type_desc as IndexType,
ixs.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(),NULL, NULL, NULL,NULL) ixs
INNER JOIN sys.indexes ix on ix.object_id = ixs.object_id
AND ix.index_id = ixs.index_id
WHERE ixs.avg_fragmentation_in_percent > 30
ORDER BY ixs.avg_fragmentation_in_percent DESC;

Leave a Reply