SQL Server Tuning – De-fragment Indexes

Problem Statement: Script to generate the scripts used to de-fragment indexes.

Solution: Use this script to find the fragmented indexes and following script can be used to de-fragment them.

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
AND s.schema_id = o.schema_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

One comment

Leave a Reply

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