Selectively rebuild indexes with SQL Server maintenance plans

By:   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Fragmentation and Index Maintenance


With 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.


With 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, April 15, 2010 - 10:17:36 AM - admin Back To Top (5245)

I agree.  The maintenance plans are pretty limiting, but for some people this may be the only option they are using.

Here are some other options as well:

Custom SQL Server Index Defrag and Rebuild Procedures

Thursday, April 15, 2010 - 9:59:31 AM - Repriser2010 Back To Top (5244)

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.

Wednesday, November 12, 2008 - 1:29:44 AM - paotech Back To Top (2195)

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.



get free sql tips
agree to terms