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

Advertisements

Posted on January 16, 2011, in Index, Performance Tuning, Sql Server 2005, Sql Server 2008. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: