By: Greg Robidoux | Updated: 2006-12-08 | Comments (3) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Fragmentation and Index Maintenance
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
- 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.
- Take a look at some of the other maintenance tasks tips
- Database Maintenance Plans and Backup File Management in SQL Server 2005
- SQL Server 2000 to 2005 Crosswalk - Index Builds
- SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS)
- SQL Server 2000 to 2005 Crosswalk - Index Rebuilds
- SQL Server - Performing maintenance tasks
About the author
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 MSSQLTips.com. 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
Article Last Updated: 2006-12-08
