/* Find index fragmentation */ SELECT DB_NAME(DATABASE_ID) AS [DatabaseName], OBJECT_NAME(OBJECT_ID) AS TableName, SI.NAME AS IndexName, INDEX_TYPE_DESC AS IndexType, AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation, PAGE_COUNT AS PageCounts FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS INNER JOIN sysindexes SI ON DPS.OBJECT_ID = SI.ID AND DPS.INDEX_ID = SI.INDID GO --Although there is no hard and fast rule, a common recommendation is to keep index fragmentation --below 10 percent whenever possible. Microsoft recommends defragmenting indexes that are 30 percent --or less fragmented and rebuilding indexes that are more than 30 percent fragmented. --To identify indexes in the current database that have more than 10 percent fragmentation, --the following query can be used: SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id WHERE ips.avg_fragmentation_in_percent > 10
