SQL Server -Defragment Indexes

Problem: Many a times you need to defragment indexes on SQL server. Following query is quite handy for doing this task.


SELECT 'Alter index ' + i.NAME + ' on '
 + Quotename(s.NAME) + '.' + Quotename(o.NAME) + CASE WHEN
 stats.avg_fragmentation_in_percent >=60 THEN ' rebuild ' ELSE
 ' reorganize' END
 FROM   sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, 'LIMITED') AS
 sys.objects AS o,
 sys.schemas AS s,
 sys.indexes AS i
 WHERE  o.object_id = stats.object_id
 AND s.schema_id = o.schema_id
 AND i.object_id = stats.object_id
 AND i.index_id = stats.index_id
 AND stats.avg_fragmentation_in_percent >= 20
 AND stats.page_count >= 1000
 ORDER  BY stats.avg_fragmentation_in_percent DESC,
 stats.page_count DESC

Leave a Reply

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