Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Intelligent Index Reorganize and Rebuild Script - v1.0

By Tim Parker,

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.

Total article views: 2112 | Views in the last 30 days: 49
 
Related Articles
FORUM

Scripting of Maintenance plan

Scripting of Maintenance plan

BLOG

INDEX DEFRAGMENTATION SCRIPT

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

FORUM

Maintenance Plan For Rebuild index

Maintenance Plan For Rebuild index

FORUM

Index maintenance

How to convince your colleagues to have index maintenance job

SCRIPT

SQL Server 2005 Index Maintenance

Index maintenance procedure using rebuild or reorganized based on fragementation level.

Tags
indexing    
performance    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones