October 5, 2016 at 4:49 am
Over the last year our DBCC Check DB process has jumped from ~ 8-9 hrs to. ~ 25 hrs. I decided to breakup the maintenance plan into separate sub plans to identify where all the time is allocated as all our DBs are generally same size bar 1.
Subplan #, DB size
1. System DBs grouped
2. 1 x 300gb DB
3. 1 x 250gb DB
4. 1 x 250gb DB
5. 1 x 900gb DB
6. All other DBs (approx 100-200gb total)
I found that #5 (900gb DB) takes ~19 hours while the others take ~ 5-6 hrs in total.
I don't know if 19hrs is normal or not due to size / data etc and i have nothing to worry about as long as it doesn't throw errors.
Does anyone have some advice or tips to help identify why so long or just previous experience that its normal.
October 5, 2016 at 6:42 am
Hi, this is an excellent article to look at regarding why checkdb takes so long. You will notice the size of the database is his #1 item.
October 5, 2016 at 3:13 pm
RVSC48 (10/5/2016)
Thanks for that, so many possibilities but i think in mycase the DB growth is the problem. On my estimates I was expecting it to take ~ 18-20 hrs. So an extra 5hrs isn't too bad just trying to see if i could potentially bring it down.
I know when doing check DB - you can specify indexes which i do. I won't be doing this however as an argument would say rebuilding all indexes prior to a check DB have any benefits?
October 5, 2016 at 3:51 pm
I have never explored that option before so afraid to comment on. Another thing to note, Microsoft recommends that you use the PHYSICAL_ONLY option for frequent use on production systems. Using PHYSICAL_ONLY can greatly shorten run-time for DBCC CHECKDB on large databases. Also, if you had a copy of the database on another server like a dev or test you could offload the checkdb to that copy instead of the actual production copy. Hope this helps....
October 5, 2016 at 4:08 pm
I like the approach of restoring the Prod database to another environment and then running CHECKDB. It also helps with testing restores, backups. But it's also not always feasible for some due to various limitations in the other non-prod environments.
Another option is splitting apart the CHECKDB process into separate piece (e.g. CHECKALLOC, CHECKTABLE) and staggering how those are run through the week. Paul Randal covers that in his article on CHECKDB for large databases:
Sue
October 6, 2016 at 3:12 am
Unfortunately i can't do the approach of restoring a backup on to another env.
Is it not better to do both physical and logical? If you had too choose would you not do both?
Breaking it up is a good idea, but for now happy to keep as is. I guess we just do it in our quietest period
October 6, 2016 at 4:59 am
Tava (10/6/2016)
Unfortunately i can't do the approach of restoring a backup on to another env.Is it not better to do both physical and logical? If you had too choose would you not do both?
Breaking it up is a good idea, but for now happy to keep as is. I guess we just do it in our quietest period
You absolutely want to do both physical and logical. It's just that physical only is faster, so if you offload the logical to a secondary server, you save tons of time. If you can't do that, breaking up the process is your best bet to speed things up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply