Maintenance Plan

Last Post 02 Feb 2011 05:29 AM by russellb. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

01 Feb 2011 10:23 PM

Is it necessary to run the following checks on the database ?

run database integrity checks.
update database statistics.
reorganise data and index pages.

I asking this because the database is quite huge at 190GB and running the above tasks will take a long long time.
May take as much as 19 hours.

Hope you can give some recommendations.

thanks in advance.
New Member
New Member

02 Feb 2011 05:24 AM
Don't have to run all of them daily. We do integrity checks (dbcc checkdb) and rebuild index weekly, takes about 20 min for dbcc on 200gb db. We updata stats daily with sp_updatestats, more efficient than maintenance plan. Reorg index daily for certain dbs that have large amount data changes, none of them takes 19 hours or 200gb db in sql2k8.
New Member
New Member

02 Feb 2011 05:29 AM
Absolutely. Let's take 'em one at a time.

Integrity Checks. These can take a while. One strategy is to restore backups to a different server and run the DBCC CHECKDB on that. This way, doesn't matter how long it takes. If you aren't running integrity checks at all, you're going to have a giant problem if you ever get corruption, only to discover that your backups are corrupt too. I strongly suggest you read this article: While 190 GB isn't a VLDB, the article is still relevant for you.

Update Statistics. First, make sure you have auto-update statistics turned on. I update stats manually with full scan periodically on selected tables. Even with auto update stats on, they can get out of date quickly when many inserts are performed -- particularly with identity and date columns. Bad stats will render a good index useless.

Reorg data and indexes. Depending on your situation, you can rebuild or reorg your fragmented indexes and data pages. I look for the most heavily fragmented indexes and rebuild them. This will usually reduce the number of reads performed by queries. Query optimization is almost ALL about reducing the # of reads. don't bother rebuilding small indexes ( less than a few thousand pages ). I won't touch 'em unless they are at least 25% fragmented and usually wait til much worse ( 45-50% )

Acceptable Use Policy