March 4, 2009 at 1:09 pm
Hi all,
I have a question in regards to creating a Maintenance Plan using the wizard within the SQL Server Management Studio. When firing up the Maintenance Plan Wizard, you can select two different ways to schedule these-- Separate schedules for each task or a single schedule for the entire plan. Then later down the wizard it asks you in which order should they be performed.
Does the order matter? I ask this because we're using SQL server to hook up to a document imaging platform. The company gave us a "best practices" guide for our SQL practices and they suggest individual schedules for each task and go down the list of all the tasks we should be running. We don't know whether we should be really careful when scheduling these individual tasks or the order does not matter at all.
For example... do you want to run a check database integrity before you do a rebuild of index?
Can you rebuild then reorg the index? Will this break anything? Is it best practice to run one before the other?
Thank you very much for your help. We are so lost, but catching on slowly...
- Dan
March 4, 2009 at 1:33 pm
I generally do it as one job, with a specific sequence of actions to take. That way, if one takes a bit longer than you allocated for it, it doesn't try to start the next step while the prior one is still running.
As far as sequence goes, check database integrity first, then run backups, then clean up old backups, then do anything else with indexes, etc. That way, if anything goes wrong with a later step and messes up the database (very rare, but could happen), you can go back to the backup and be okay.
One specific point is that there's no reason to both rebuild and reorganize the same indexes in the same plan. Same goes for rebuilding indexes and updating stats, no need to do both in the same job. Rebuild takes care of both of the other things, so they become redundant if you're already doing that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2009 at 1:37 pm
Yes to both questions. Order of operation does matter. As you learn more about the specific operations (sometimes more than you care to know!) you'll catch on.
> For example... do you want to run a check database integrity before you do a rebuild of index?
No a database integrity check is usually run seperate plan that runs to make sure there is no corruption in the database (equivalent to a DBCC CHECKDB)
>Can you rebuild then reorg the index? Will this break anything? Is it best practice to run one before the other?
These tasks are mutually exclusive. You would usually choose one or the other based on the level of fragmentation that exists inside of the indexes. Based on who you read about 10% is the cutoff for reorgs.
Cheers,
Brian
March 4, 2009 at 2:07 pm
Thank you very much for your responses. They've been very helpful.
I'm sure I'll be back... 😉
March 4, 2009 at 4:28 pm
also dont forget to back up the database after the index rebuild as this could fill up the log file depending on database size and no of indexes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply