Index Maintenance Query, Rebuid (>40)or Reorganize (>10)

Below query will generated list of indexes to be re organized or rebuild based on 10-40 Rule.

>=40 % Rebuild

>=10 and <40 ReOrganize

— This query is tested on working fine.

 

 

  SELECT ‘ALTER INDEX [‘ + ix.name + ‘] ON [‘ + s.name + ‘].[‘ + t.name + ‘] ‘ +

       CASE WHEN ps.avg_fragmentation_in_percent >= 40 THEN ‘REBUILD’ ELSE ‘REORGANIZE’ END +

       CASE WHEN pc.partition_count > 1 THEN ‘ PARTITION = ‘ + cast(ps.partition_number as nvarchar(max)) ELSE ” END

FROM   sys.indexes AS ix

 

INNER JOIN sys.tables t  ON t.object_id = ix.object_id

       INNER JOIN sys.schemas s     ON t.schema_id = s.schema_id

       INNER JOIN(SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number

                   FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)) ps

           ON t.object_id = ps.object_id AND ix.index_id = ps.index_id

       INNER JOIN(SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count

                   FROM sys.partitions

                   GROUP BY object_id, index_id) pc

           ON t.object_id = pc.object_id AND ix.index_id = pc.index_id

WHERE  ps.avg_fragmentation_in_percent >= 10 AND

       ix.name IS NOT NULL

 

 

Advertisements

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 )

Google photo

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