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) ixsINNER 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;