February 15, 2005 at 4:40 am
I tend to keep maintenance plans to either a single database or a small collection of related databases.
I am reaching the stage where I have a large number of maintenance plans and I am not sure how they are best scheduled.
Should I work out the execution time for each on and run them serially, should I stagger them, should I just start them all off together?
February 15, 2005 at 4:59 am
David,
My own experience says that if your database files are on the same data and log volumes as each other, it's probably a good idea to stagger the maintenance plans so that they don't overlap.
Disk I/O operations can be very intensive on the hardware, which is why they're usually run at night because of the performance / response time impact they have on users.
Whether rebuilding indexes, shrinking databases or backing them up, you'll find that doing them in series is probably a better idea than trying to run them in parallel. This is because if the database pages are reasonably ordered, the disk heads should be reading from disk in a *mostly* sequential manner, which minimises disk head movement. If only one database is being rebuilt at a time, this means the disk head(s) not moving around much, which reduces latency (disk head track-to-track movement).
However, if you're rebuilding one database's indexes whilst shrinking another and backing up a third, the disk heads are going to be given a good work out, constantly moving backwards and forwards across the surface of the disk. This increases disk head latency, which although often only a few milliseconds each time the head has to move, when multiplied by a few million times during an index rebuild or database shrink, can add up considerably overnight. Therefore I'd suggest you run them in series.
The only exception I'd add is if your data files and logs are on separate physical volumes for each and every separate database (unusual, but...) In this you may make some gains by running the plans in parallel, but I'd advise you to experiment.
Jon
February 16, 2005 at 4:04 am
I prefer not to use the maintenance wizard, but to have procs that backup databases and do maintenance (reindex or index defrag, update stats and recompile). This gives you a lot more flexibility in your scheduling. There are a lot of default scripts that you can find to do this and IMO you stop relying on a wizard that hides a lot of complexity.
February 16, 2005 at 11:58 am
In the past I've used both methods (EM Maintenance Plans and 'homegrown' scripts). It's pretty much a question of execution time on a per database basis and the amount of server resources you are consuming. I also consider database size extremely important as well.
The 'wizard' approach is fairly 'generic' and usable for most things (preventative maintenance) on databases of less than 20 Gb of actual data. Anything larger than that, I feel it's got to be custom.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply