March 16, 2011 at 3:57 pm
I’ve re-written this several times trying to figure out how to word it and be brief so apologies if I leave out something important.
I know that there is no formula for determining how long it will take the DBCC CHECKDB command to run against a database however once you are running it and you continue to run it against the same DB at the same time of day each day of the week and you see that it is taking the same amount of time for the job to complete (+/- 1 minute) is it logical to assume that the job will continue to take the same amount of time to complete so long as all other conditions remain the same?
In other words is DBCC CHECK DB consistent assuming the same environment?
Kindest Regards,
Just say No to Facebook!March 16, 2011 at 4:39 pm
Yes and no. DBCC is subject to contention issues the same as almost any other process. Assuming no connections and no activity, yes, I'd expect it to run more or less consistently as long as nothing changed, no additional data, no changes to the structures of any kind, etc. But another user running a query in the middle of the DBCC check could cause the behavior to change.
"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
March 30, 2011 at 1:21 pm
Grant,
In my scenario the environment doesn’t change normally and so when it runs all factors are the same with the exception of some data growth. I have seen times when something out of the ordinary ran at the same time and so DBCC took longer but this was not a surprise. What makes me think something is happening or happened is the increase in duration to complete, a big jump, from what was the norm without an accompanying change in the schema and no growth of the DB that is outside the normal daily growth.
Just to recap, if I setup the PUBS sample DB and had a job that ran during the data to insert some data (so as to re-create DB growth on a daily basis) and then set up a SQL Job to run CHECKDB at night, then unless something out of the ordinary occurred the time for the command to complete should remain fairly consistent, yes?
Thanks
Kindest Regards,
Just say No to Facebook!March 30, 2011 at 6:31 pm
Again, maybe, maybe not. You're also looking at the resources on the system. How much memory is there? Is it possible that you've pushed the DBCC check to the point that now it doesn't have enough memory to complete in time... there are just so many factors. Knowing exactly how long it will run in a situation where things are changing, normal growth, means that you just can't predict what happens. I wish I could say otherwise.
"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
March 31, 2011 at 3:02 pm
Grant Fritchey (3/30/2011)
Again, maybe, maybe not. You're also looking at the resources on the system. How much memory is there? Is it possible that you've pushed the DBCC check to the point that now it doesn't have enough memory to complete in time... there are just so many factors. Knowing exactly how long it will run in a situation where things are changing, normal growth, means that you just can't predict what happens. I wish I could say otherwise.
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply