Query to Check Fragmentation and rebuild index in a database

By | December 28, 2015

Below query checks fragmentation level (>30%) per table which has clustered or non-clustered index and provides you the query to perform ONLINE rebuild of that index.The fragmentation level of the table which has the highest number of rows is displayed on top.

Use [database_name]

SELECT OBJECT_SCHEMA_NAME(ind.OBJECT_ID) + ‘.’ + OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName,
indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent ,
p.[Rows] as RowsCount,
‘ALTER INDEX [‘ + ind.name + ‘] ON ‘ + OBJECT_SCHEMA_NAME(ind.OBJECT_ID) + ‘.[‘ + OBJECT_NAME(ind.OBJECT_ID) + ‘] REBUILD WITH (ONLINE = ON)’ AS Command
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sys.partitions p ON ind.object_id = p.OBJECT_ID AND ind.index_id = p.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.index_type_desc <> ‘HEAP’
ORDER BY 5 DESC

This query is helpful when users report slowness in the database .Check “avg_fragmentation_in_percent” for tables having the highest number of “RowsCounts”.You can do selective ONLINE rebuild of fragmented indexes(in case you have no other choice)

Leave a Reply

Your email address will not be published. Required fields are marked *