Knowing Index Fragmentation Query
The below query explains about fragmentation on each index on each table on a specific database. Fragmentation and page count should be greater than 1000 USE DatabaseName go SELECT B . name AS TableName , C . name AS IndexName , C . fill_factor AS IndexFillFactor , D . rows AS RowsCount , A . avg_fragmentation_in_percent , A . page_count FROM sys . dm_db_index_physical_stats ( DB_ID (),NULL,NULL,NULL,NULL) A INNER JOIN sys . objects B ON A . object_id = B . object_id INNER JOIN sys . indexes C ON B . object_id = C . object_id AND A . index_id = C . index_id INNER JOIN sys . partitions D ON B . object_id = D . object_id AND A . index_id = D . index_id WHERE C . index_id > 0 and A . avg_fragmentation_in_percent > 30 order by A . avg_fragmentation_in_percent desc