Description:
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.
Results:
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.
