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.
Can I post a question here? I am working in SQL Server 2005 and currently our maintenance plans are set up as follows:
Check Database Integrity
Shrink Database
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History
Maintenance Cleanup Task
Based on your article should I remove Reorganize Index and Update Statistics?
I would say that yes, if you are rebuilding your indexes, you do not need to have reorganize and update stastics in there. Also, I would not recommend having shrink database in your maintenance plan if you do not need it. It can lead to unnecessary fragmentation of the database files if not careful, which could degrade performance.
Essentially, repeating what unixbomer says, and speaking generally, I would only recommend the following:
--Check Database Integrity
--Rebuild Index
--Clean Up History
--Maintenance Cleanup Task
--Backup Databases (you didn't list this one)
Of course, each situation can be slightly different and it is often hard to give general advice. But as it stands now, you are performing redundant tasks that don't need to be performed.
If you would like, e-mail me at bradmcgehee@hotmail.com and I can send you a presentation I do on database maintenance that might offer you some guidance.
In regards to the wizard, it is usefull if you don't want to write your own script. However, I think part of the issue for non DBAs is a lack of good material on creating maintenance plans. Flipping through my library, which is larger than most public libraries :), all of the books that are about SQL server, spend very little time explaining mainteance plans or are generally useless. One of the only books I can think of, that actually got it right, was T-SQl Querying, however it isn't a title I have seen on shelf of a lot non-DBAs. Maybe that should have been the conclusion of this article, don't by books about SQL Server 2xxx, just get TSQL Querying.
Not only is there nothing much useful on maintenance plans in BOL, there's not even much useful on backup. One interesting thing is that maintenance plans do let you create incremental backups - but maybe this is just as well: if your database server is also a domain controller and uses ntbackup to back up system state, ntbackup will break the database backup chains! Of course this affects log backup chains as will as increment chains, but the maintenance plan wizard is probably going to leave you using full backups where you might otherwise have used incremental backups, which means that you lose less when you need to restore and find the chain is broken.
Is there a "best practices" order in which the above tasks should be run?
If you are using the Maintenance Wizard, I would recommend the following (subject, of course, to your specific needs) steps in the following order:
Rebuild Index (or Reorganize Index and Update Statistics)
Backup Database (Full)
Backup Database (Transaction Log)
Also, you will have to decide how often to run these tasks. There are many options, including running a daily full backup, hourly transaction log backups, and then running the rest of the jobs on a weekly basis. But this is only one option, and may or may not be your best choice depending on your environment. This is very generic advice, but it is hard to give specific advice unless more is known about your environment.
Great post.
But, in this case, why would you backup the transaction log right after a full backup?
Although the list above might give the impression that you should perform a transaction log backup immediately after a full backup, that's not the case. What really happens, in the context of the Maintenance Wizard, is that you need to schedule a regular (daily) full backup first, then you schedule transaction log backups to occur on a regular basis (hourly), once the full backup is made. So the log backup won't occur until about an hour after the full backup is made.
