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.

SELECT OBJECT_NAME(dt.object_id), 
si.name,

dt.avg_fragmentation_in_percent, 
dt.avg_page_space_used_in_percent

FROM

      (SELECT object_id, 
              index_id, 
              avg_fragmentation_in_percent, 
              avg_page_space_used_in_percent

            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. */