Best Practices for Maintenance plans - how to best use the various tasks

Question

Best Practices for Maintenance plans - how to best use the various tasks

Maxer

SSCrazy Eights

Points: 8964
More actions
September 12, 2008 at 9:50 am

#123611

I have mine set to do the standard backup and another separate plan for t-log backups.
But what about database integrity checks, index reorganization or rebuilding, and updating statistics?
What are some of the best practices for these, and what are people doing with these?
Are you running them nightly, are you checking yourself to see how things are holding up, do you run them and have them notify you if something is awry and you fix it yourself?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

We don't use maintenance plans much, but we do run an integrity check job, using DBCC CHECKDB for all user databases, twice a week and an update statistics job once a week. We run index reorg when necessary.

Greg

Maxer SSCrazy Eights Points: 8964 More actions · Answer 2

Is there any harm in running the integrity check and a full index re-org every night as part of your nightly maint?

Say a full database backup, followed by those?

(Granted it is going to take longer and slow things down, but since we are a 9 to 5 type place for most intents and purposes, no one will notice)

So other than slowing things down, is there a good reason NOT to do that?

GSquared SSC Guru Points: 260824 More actions · Answer 3

I run CHECKDB before I do nightly full backups. I can't see the purpose to backing up a corrupted database. Kind of defeats the purpose of doing the backup.

I reorganize indexes overnight. Some databases, it's weekly. Some it's daily. Two, I have a more complex plan that does some indexes in the first week of every month, other indexes the second week, etc., because running it all one night would take longer than the window I have available. Performance of the databases has been much better since I did that.

I rebuild if the fragmentation is over a certain amount, reorganize below that.

In all cases, if the index is too small to bother with, I don't bother. Same reason I don't bother with indexes on really small, static tables.

- 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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

Wrong with it? No. If you have the bandwidth and the time then by all means you can run those nightly. However, if you have extra time do you mow your lawn even if it doesn't need it (assuming you live in a part of the world that would promote such waste 😀 - as I do). The answer is no.

So, understanding when we need to run our index maintenance can be just as important as doing it. I'm not opposed to doing them as a blanket statement but still take the time to understand what your needs are in regards to that.

As for the integrity checks, run them as often as you desire. There is no harm and better to only have 1 day gone with corruption than multiple.

Hopefully this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

GSquared SSC Guru Points: 260824 More actions · Answer 5

Oh, one other point that matters: If you rebuild and index, don't bother with a stats update. Rebuilding an index updates the stats for you. Not true for reorganizing an index, just for rebuilding it.

- 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

Maxer SSCrazy Eights Points: 8964 More actions · Answer 6

GSquared (9/12/2008)
Oh, one other point that matters: If you rebuild and index, don't bother with a stats update. Rebuilding an index updates the stats for you. Not true for reorganizing an index, just for rebuilding it.

I wondered about that (I figured rebuilding would handle stats at the same time since it well... rebuilds the whole thing), but I wasn't sure about a re-org.

Thanks!

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 7

I've run integrity checks daily, as mentioned above, before backups. Alert someone if you find errors.

For rebuilds, I've moved to procedures that look for fragmentation and rebuild those ones only. You can do them all if you have time, but it's a good habit to get into, especially as you might get large tables in some places over time, to only do those that need it.

Maxer SSCrazy Eights Points: 8964 More actions · Answer 8

I have my backups first, then the integrity checks, BUT the reasoning behind that is I don't want to abort ALL my backups if just one database has an issue.

So I'd rather have it back it all up (the whole process takes less than 10 minutes in my case) and then check integrity.

I have it set to email me if it fails that task in the maint plan, otherwise it moves onto the rest of the tasks.

But I certainly can see how larger databases would want to make sure they were "clean" before starting a backup process that could take hours and burn through several tapes (or TBs) depending on how it is being done!