Randall Neth – BI, SQL, & SharePoint

As part of your analysis of any SQL Server infrastructure you take a good long look at the indexes within their databases.  You want to look for and create missing indexes, determine what indexes can be removed or consolidated, determine where to implement covering indexes and filtered indexes, and review overall health of the indexes.  As part of this final health check of the indexes you will want to review index fragmentation as depending upon how indexes are utilized and queries are written this can be a major bottleneck within your SQL infrastructure. 

I have been looking for a simple script that will return the index information for a specific database and will return the Index Name, Fragmentation Percentage, and the number of pages in the index.  Below is a script that will assist you with determining fragmentation of your indexes.  This is a mashup of the script by Deepak here: http://www.sql-articles.com/scripts/index-fragmentation-report and a script by Brent Ozar here: http://sqlserverpedia.com/wiki/Index_Maintenance.

SELECT  DB_NAME(ps.database_id) AS DBName,
        S.name AS Schemaname,
        OBJECT_NAME(ps.OBJECT_ID) AS Tablename,
        Index_Description = CASE WHEN ps.index_id = 1 THEN 'Clustered Index'
                                 WHEN ps.index_id <> 1
                                 THEN 'Non-Clustered Index'
                            END,
        b.name AS Indexname,
        ROUND(ps.avg_fragmentation_in_percent, 0, 1) AS 'Fragmentation%',
        SUM(page_count * 8) AS 'IndexSizeKB',
        ps.page_count
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
        INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
                                       AND ps.index_id = b.index_id
                                       AND b.index_id <> 0 -- heap not required
        INNER JOIN sys.objects AS O ON O.OBJECT_ID = b.OBJECT_ID
                                       AND O.TYPE = 'U'
                                       AND O.is_ms_shipped = 0 -- only user tables
        INNER JOIN sys.schemas AS S ON S.schema_Id = O.schema_id
WHERE   ps.database_id = DB_ID()
        AND ps.avg_fragmentation_in_percent > 30 -- Indexes having more than 30% fragmentation
GROUP BY DB_NAME(ps.database_id),
        S.name,
        OBJECT_NAME(ps.OBJECT_ID),
        CASE WHEN ps.index_id = 1 THEN 'Clustered Index'
             WHEN ps.index_id <> 1 THEN 'Non-Clustered Index'
        END,
        b.name,
        ROUND(ps.avg_fragmentation_in_percent, 0, 1),
        ps.avg_fragmentation_in_percent,
        ps.page_count
ORDER BY ps.avg_fragmentation_in_percent DESC
OPTION  ( MaxDop 1 ) ;


A great resource for more information on Indexes and fragmentation is:

Kimberly L. Tripp – SQLSkills Blog