The World's Worst Maintenance Plan

Below is the world's worst Maintenance Plan. Please try to ignore the fact that it is in Denali, and hence looks sexy!

This is a pretty standard maintenance plan. You might even have one like it in your environment, so what's so bad about it? Well, there are 3 issues, and they get progressively worse.

1) The first, is the fact that the Index Rebuild Task in SQL Maintenance Plans in "dumb". Or in other words, it blindly does through and rebuilds every index, regardless of whether it needs to be rebuilt or not. This is Ok if you have a nice big maintenance window, but if you in something approaching a 24/7 environment, as lots of us are these days, then you are likely to want to reduce your maintenance window as much as possible. Also, if you have Database Mirroring set-up (especially in SQL 2005) you might be flooding the network with your log stream. Even if you you don't have either of these considerations, I am still a great believer in not using more resources than you need to.

2) The second issue relates to the Update Stats Task. Now, not many people realise this, but when you rebuild your indexes, SQL Server takes advantage of having 100% stats at it's disposal, and updates the index stats based on a 100% sample. So at best, your Update Stats Task will be set to use 100% sample, and you are merely doing the work twice. In the worst case, you will be using a 10% or 20% sample to update your stats, and not only will you be doing the work twice, but you are going to end up with worse stats than you had originally.

3) Shrink is evil - Do not use! I mean it. The smallest problem with shrinking your database is that you should be managing your space pro-actively, and shrinking the files will just mean they have to grow again. The much worse side effect is that shrinking your files will cause hugh amounts of fragmentation, thus undoing all of the work your Index Rebuild has done.

In my next post, I will do a demo of the fragmentation caused by shrinking a database.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom