Home > indexes > Find Index Fragmentation in SQL Server

Find Index Fragmentation in SQL Server

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,
    i.name 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
  1. No comments yet.
  1. No trackbacks yet.

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: