How to Check Fragmentation Level of a Database
The below script outputs a list of database indexes where the fragmentation level is over 50%, and also includes information on the page count of the table.
For those using Ola’s optimisation script, remember that by default this script will not undertake index maintenance on low page count tables. This is something you can override by explicitly defining the @PageCountLevel variable when you call the optimisation script, however it’s worth reviewing his notes in his FAQ about this first.
Fragmentation often isn’t a problem where the page size is less than 1000. To check for problematic fragmentation, uncomment the last line in the script below to see only the larger pages with high fragmentation.
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 >50 -- and page_count > 1000
BrentOzar also does a great write up on Index maintenance.