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)
Thank this author by sharing:
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.
Scripting of Maintenance plan
INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005
I dedicate this article to my senior DBA “Roshan Jo...
Maintenance Plan For Rebuild index
How to convince your colleagues to have index maintenance job
Index maintenance procedure using rebuild or reorganized based on fragementation level.