Index Maintenance - SQL Server

Reference¶

• Adapted from https://stackoverflow.com/questions/1086812/script-for-rebuilding-and-reindexing-the-fragmented-index
• Further reading: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16#index-maintenance-methods-reorganize-and-rebuild
  

Overview¶

• Sometimes it is sufficient to update statistics. Doing so is less resource-intensive and time-consuming than reorganizing or rebuilding indexes.
• If fragmentation is less than 5%, do nothing.
• If fragmentation is 5% to 30%, reorganize the index.
• If fragmentation is above 30%, rebuild the index.
  

SQL Script¶

exec sp_updatestats;

select 
     TableName       = OBJECT_NAME(ind.OBJECT_ID)
    ,IndexName       = ind.name 
    ,IndexType       = indexstats.index_type_desc 
    ,Fragmentation   = indexstats.avg_fragmentation_in_percent

    ,SqlQuery        = 'ALTER INDEX ' + QUOTENAME(ind.name)  + ' ON ' +QUOTENAME(object_name(ind.object_id)) + 
                        CASE    WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD ' 
                                WHEN indexstats.avg_fragmentation_in_percent>=5 THEN ' REORGANIZE '
                                ELSE NULL END   -- if <5 not required, so no query needed

from 
    sys.dm_db_index_physical_stats(db_id(), null, null, null, null) indexstats 

    inner join sys.indexes ind 
        ON ind.object_id = indexstats.object_id 
        AND ind.index_id = indexstats.index_id 
where 1=1
    and ind.Name is not null
    and indexstats.avg_fragmentation_in_percent >= 5
order by 
    indexstats.avg_fragmentation_in_percent DESC