Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Selectively rebuild indexes with SQL Server maintenance plans


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

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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

Solution
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



Last Update:


signup button

next tip button



About the author





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    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

http://www.mssqltips.com/tip.asp?tip=1791


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

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/


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

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!


Learn more about SQL Server tools