SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Intelligent Index Reorganize and Rebuild Script - v1.0

By Tim Parker,


This script will provide a dynamic mechanism to decide between REBUILDING an index or simply REORGANIZING an index.  This will improve performance when this critical maintenance process executes.  This version of the script provided has been tested and approved for use on SQL Server 2005 & 2008 R2 Standard or Enterprise or R2.  This script is very thorough and provides several intelligence mechanisms for determining when and how to perform index maintenance on a given table.

Rules For Index Maintenance:

1.) Are there open cursors in the database, if so skip the database.

2.) Index Size is greater than 5 MB's.

3.) Reorganize = fragmentation level is between 5% and 30%

4.) Rebuild = fragmentation level is greater than 30%

In addition to the index maintenance script provided, there is a table named IndexMaintenanceHistory that will collect the historical runs of the index maintenance job.  This is useful for auditing purposes when you need to find out if certain indexes are having maintenance completed as required.


I have found this script to provide a significant improvement in runtime for index maintenance on our SQL Servers across the board.  Because the script makes the decision between rebuilding or reorganizing the index, I can execute the index maintenance job more often without over burdening the SQL Server during the nightly maintenance window.

As always, I highly recommend running an Update Statics job separately from this script to ensure that all index statistics are up to date.

Total article views: 2791 | Views in the last 30 days: 2
Related Articles

Scripting of Maintenance plan

Scripting of Maintenance plan


Capture Index Maintenance Operations

One good database maintenance practice is to keep the indexes in good working order. This is typical...



  INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005 I dedicate this article to my senior DBA “Roshan Jo...


Maintenance Plan For Rebuild index

Maintenance Plan For Rebuild index


Index maintenance

How to convince your colleagues to have index maintenance job