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

SELECT
    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
FROM 
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
Advertisements
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: