Data World

SQL Server -Defragment Indexes

Posted by Pramod Singla on December 17, 2015

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

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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 )

Connecting to %s