Selectively rebuild indexes with SQL Server maintenance plans

ProblemWith SQL Server 2000 you had the ability to rebuild indexes using maintenance plans. This was great if you wanted or needed to rebuild every index in your database, but for very large databases or tables that were not changed that often you were forced to create scheduled jobs with the appropriate T-SQL to only rebuild indexes on the tables that you specified. With SQL Server 2005 this has all changed and now there is an option to select which tables or views you want to rebuild or reorganize indexes on vs having to rebuild every single index in the database.

SolutionWith SQL Server 2005 the process of creating maintenance tasks has changed. There are now two options; the first is still using the wizard and the second is by creating a custom maintenance plan. In addition, there are now two options for maintaining your indexes with maintenance plans rebuilding or reorganizing.

The following screen shot shows you the options that exist with SQL Server 2000 maintenance plans. As you can see you have the option to either reorganize the data and index pages or update statistics. Beyond that you had no additional control.

With SQL Server 2005 you have the option of rebuilding or reorganizing your indexes. First you need to create a maintenance plan and select the "Rebuild Index Task" option as shown below.

When you double click on this task the following screen appears. If you select one database from the "Databases" dropdown list you then get the option to select from the "Object" drop down list. The default option is "Tables and Views" for objects. With this option selected the "Selection" dropdown is disabled.

If you change the "Object" selection to "Table" you now get a drop down of all the tables that exist in the database you selected. At this point you can select which tables you want indexes to rebuilt on.

The same holds true if you select "View" you can see a list of views that you can select for index rebuilds.

In addition to the "Rebuild Index Task" you have the option of just reorganizing indexes using the "Reorganize Index Task".

The process works pretty much the same was as above. Select this task and double click on it to get the properties screen. If you select one database the "Object" dropdown is enabled and if you select "Table" or "View" from the "Object" dropdown you can then also select which tables or views to maintain.

Below is a screenshot of available tables for reorganization

Below is a screenshot of available views for reorganization

Although this gives you a lot more flexibility in controlling your database maintenance it would have been nice if they included an option to use the new dynamic management views to view fragmentation and base the maintenance on these numbers. This new feature does give you the option down to the table level, but it would be nice if it also allowed you to select an individual index vs all indexes for the table.

Overall this is a nice improvement over SQL Server 2000 maintenance plans, but there are definitely some additional options that would have made it a lot easier to use and also a lot more useful in more environments.

Next Steps

See if this makes sense for your environment. If you have a small enough database it is probably still easier just to do everything, but if you have a few large tables that are not updated all that often this new feature might come in handy.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Well documented, not the smart est because it takes too much manual work to choose what you want to rebuild. Best use a script to pick up conditions of indexes that you want to rebiuld and let script generate and execute alter index ...... command. See my posting. http://usa.redirectme.net/

Fantastic tip and well-written article. Relatively new to SQL Server 2005, 2am and I found myself wondering why a table wouldn't load in a web app that needed to be up by 6am. The tip was perfect.

Thanks!

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.