April 26, 2012 at 2:48 pm
For at least the last 6 months (which is as far back as I have checked) the SQL Job we run to perform DBCC CHECKDB has completed on average between 38 and 42 minutes.
Starting a few days back I saw this time jump to 80 minutes. I checked the next night to see if this was a one time fluke and again it took nealry twice as long to complete as it had been previously.
I assume this large fluctuation in CHECDB is nt normal and should be chjecked out. SO far I'm not seeing anything to write home about. ANy ideas on what can cause this kind of change with CHECKDB? My IT admins are telling me no hardware changes hav occurred nor have their been any sowfatre/OS updates during this time.
Kindest Regards,
Just say No to Facebook!April 26, 2012 at 2:51 pm
Was this change in processing time across the board or specific to certain databases?
April 27, 2012 at 5:02 am
Data growth? Additional resource use leading to contention? Do you have memory, cpu & IO tracking over the same two time periods to compare how the whole system was behaving?
"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
April 27, 2012 at 10:14 am
Lynn Pettis (4/26/2012)
Was this change in processing time across the board or specific to certain databases?
Lynn
Its just the one database however its the only one on that SQL Server so theres no other databae to compare to/with. I ran the CHECKDB again last night (same time as the other 2 ) and once again it took twice as long.
Kindest Regards,
Just say No to Facebook!April 27, 2012 at 10:20 am
Grant Fritchey (4/27/2012)
Data growth? Additional resource use leading to contention? Do you have memory, cpu & IO tracking over the same two time periods to compare how the whole system was behaving?
Grant,
The dB hasn't grown any betwen the 2 times, at least not the data file itself and as far as the data in the file it has grown some but no more then any other normal day. SQL Monitor has been running the entire time (as well as ong before this happened) so I do have captured metircs but I have not had a chance to do any comparison yet.
I was kind of hoping with this being such a major change (duration to complete) that somene here would recognize this change and respond with something like like "Yeah that happend to me to, heres what you do".
The only change that has occurred between when this job last ran in 40 minutes and when it started taking twcie that is we have a new DPM (Data Proetction Manager) server that manages the SQL Protection group that this database is included in. I'm told tthat other then the new DPM server running version 2012 (the old one was using DPM 2010) the new DPM server is running the exact same protetcion group as the old and so this should have no bearing on the SQL Server or its database but its the only change of any significance.
Kindest Regards,
Just say No to Facebook!April 27, 2012 at 10:29 am
Usually a dramatic increase in duration indicates that there is some form of corruption and that CheckDB ahs to go back and run more checks to identify exactly what is wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2012 at 10:55 am
GilaMonster (4/27/2012)
Usually a dramatic increase in duration indicates that there is some form of corruption and that CheckDB ahs to go back and run more checks to identify exactly what is wrong.
Lynn,
The below is the results from CHECKDB from last night, the same reuslts I've gotten for a long time. COuld it be that its not corruption but something is on the verge so its slow or requires additonal checking but is not such that it rasies any errors?
CHECKDB found 0 allocation errors and 0 consistency errors in database
Kindest Regards,
Just say No to Facebook!May 16, 2012 at 10:24 am
UPDATE
I know it’s been a while since I asked about this but I've been to busy to revisit the site until now.
This will probably come as no surprise to some of you but the source of the change in duration to run DBCC CHECKDB was caused by a change to the MAXDOP setting on the server. It took me a while to find this because I never would have thought anyone else would have tried to adjust it without asking. I was however out for a while and based on a recommendation from an internet posting the found, the IT guy who covered form me while I was out changed MAXDOP and in doing so affected the DBCC CHECKDB job.
Thanks again for posting
Kindest Regards,
Just say No to Facebook!Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply