In theory, the SQL Server Maintenance Plan Wizard is supposed to make it easier for non-DBAs or novice DBAs to create database maintenance plans that help to optimize the performance of their SQL Server databases. The problem is that unless you really know what you are doing, you can hurt the performance of your SQL Server if you make poor choices. Unfortunately, the Wizard does not tell you if you make poor choices.
For example, check out the following screen, which is part of the SQL Server 2008 Maintenance Plan Wizard.
If you are an experienced DBA, your first thought should be, “Why are all the maintenance tasks checked?” This is because experienced DBAs know that you only need to perform some of these tasks, not all of them, and in fact, if you choose all of them, you can cause your server to waste a lot of valuable resources. For example, performing all three of the following tasks: reorganizing, rebuilding, and updating statistics is redundant, as rebuilding your indexes essentially performs the same tasks as reorganizing and updating statistics. In other words, if you rebuild your indexes, reorganizing and updating statistics is a duplication of effort. There are also some additional problems that can occur when selecting all of the options, but I don’t have time to write about them all now, but I think you get the point.
Now, let’s look at the above screen from the perspective of the non-DBA, or novice DBA who isn’t familiar with what maintenance plans do, or how they work. From their point of view, don’t you think that the more options you select, the better off your SQL Server will be? Isn’t more always better than less? What has prompted me to write about this topic is because I just reviewed a SQL Server instance that was installed and set up by a non-DBA, and as you have already probably guessed, they selected every maintenance task on the menu, thinking that more is better than less. Because the non-DBA made this choice (which is not all than uncommon), the maintenance plan took more than twice the time it really needed to run, wasting a lot of the server’s resources. There were some additional problems as well, but that is another story.
The point I want to make is that the Maintenance Plan Wizard can sometimes be useful in the hands of an experienced DBA, but it can be very dangerous in the hands of a non-DBA or novice-DBA. In fact, most experienced DBAs I know write their own maintenance plans because using the Wizard just has too many limitations and problems. If you are an experienced DBA, you know what I mean. If you are a non-DBA or novice DBA reading this, please take heed of my message, more is not better. If you decide to use the Wizard instead of writing your own custom maintenance plans, please do your research before you begin, learning what should, and what should not be done, within a maintenance plan.
| Share this post : |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
