Archive for the ‘indexes’ Category

Find Index Fragmentation in SQL Server

December 20, 2011 Leave a comment

Below is a script using the DMV’s to find fragmented tables in a SQL Server database instead of the old way using DBCC SHOWCONTIG.  Most DBA’s want to start at using these DMV’s and this script can be customized to your environment

    DB_NAME(database_id) as DatabaseName,
    OBJECT_SCHEMA_NAME(ps.object_id) + '.' + OBJECT_NAME(ps.object_id) as TableName, as IndexName,
    ps.page_count as IndexPageCount,
    ps.avg_fragmentation_in_percent as IndexFragmentation,
    ps.avg_fragment_size_in_pages as IndexFragmentationPages,
    ps.partition_number as PartitionNumber,
    ps.index_type_desc as IndexDesc,
    ps.alloc_unit_type_desc as AllocUnitDesc,
    ps.index_depth as IndexDepth,
    ps.index_level as IndexLevel,
    i.[fill_factor] as [FillFactor],
    CASE i.index_id
        WHEN 1 THEN 1
        ELSE 0
        END as IsClustered
sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') ps
INNER JOIN sys.indexes i
    ON ps.object_id = i.object_id 
    AND ps.index_id = i.index_id
WHERE ps.index_type_desc <> 'HEAP'          --Ignore HEAP tables
AND ps.page_count > 30                      --Ingore less than 30 pages
AND ps.avg_fragmentation_in_percent > 30    --Fragmentation Threshold
ORDER BY TableName, IsClustered DESC, IndexName
Categories: indexes