Here is the code for smart Index reorg: If avg_fragmentation_in_percent > 5% and < = 30% ALTER INDEX REORGANIZEIF avg_fragmentation_in_percent > 30% ALTER INDEX REBUILD WITH (ONLINE = ON)SELECT case when avg_fragmentation_in_percent between 5 and 30 then 'ALTER INDEX ' + i.name + ' ON ' + t.name + ' REORGANIZE;' when avg_fragmentation_in_percent > 30 then 'ALTER INDEX ' + i.name + ' ON ' + t.name + ' REBUILD with(ONLINE=ON);'else 'PRINT ''INDEX '+i.name+' ON TABLE '+t.name+' '+cast(avg_fragmentation_in_percent as varchar(5))+'%'''FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) AS aINNER JOIN sys.indexes AS i ON a.object_id = i.object_id AND a.index_id = i.index_idjoin sys.tables t on t.object_id=i.object_idwhere i.name is not null
HOW TO REORGANIZE ALL INDEXES ?
Posted - 2008-11-20 : 11:03:28
