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.