Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Page History: Index Maintenance - SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Current Revision


Page Revision: Fri, Feb 23, 2024, 6:35 AM


Reference


Overview

This article provides a script for maintaining database indexes, according to the following guidelines.

  • 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

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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.