>SQL Server: How to Determine All Indexes Fragmentation
On request of one of my blog reader, here is a script which I like to use to check internal and external fragmentation of all indexes of my database.
In this simple script I am currently using DETAILED option. You can obtain results with moderate accuracy but with increase in speed by using the SAMPLE option, which scans only 1 percent of the pages. For the most accuracy, use the DETAILED option, which scan all the pages in an index. But always keep in mind that if an index has fewer than 10,000 pages, what ever option you select, DETAILED option is used automatically.
Note: Please don’t forget to provide your own database name at YourDatabaseNameHere.
FROM sys.dm_db_index_physical_stats (DB_ID(‘YourDatabaseNameHere’), NULL, NULL, NULL, ‘DETAILED’)
WHERE index_id <> 0) as dt –does not return information about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
WHERE avg_fragmentation_in_percent >10 OR avg_page_space_used_in_percent <75
ORDER BY avg_fragmentation_in_percent DESC
/*avg_fragmentation_in_percent column is used to determine external fragmentation
External fragmentation is indicated when this value exceeds 10.
avg_page_space_used_in_percent column is used to determine internal fragmentation.
Internal fragmentation is indicated when this value falls below 75. */