February 5, 2017 at 5:16 am
Hi All,
We have some circa 5TB databases that take an age to run DBCC CHECKDB against. Quite often we get errors concerning lack of space and other resource issues, so I've written an alternative script using DBCC CHECKFILEGROUP that breaks it up into smaller chunks. Last night it ran for the first time, and although it ran round the filegroups during the allotted window fine, I noticed that CPU hit 70% for the duration. Running a full DBCC CHECKDB on the same database only creates about 10% CPU load on the same server. There's stuff in the script that logs the filgroups it's checked, a window to run in, etc. but in essence, the only command it executes is DBCC CHECKFILEGROUP ('fgname') WITH NO_INFOMSGS, ALL_ERRORMSGS.
The server is SQL Server 2008 R2 Enterprise Edition, SP3. 196GB RAM, 24CPU.
Why would DBCC CHECKDB consume so little CPU compared to DBCC CHECKFILEGROUP?
I know I can use trace flags, hints and resource governor, etc. to control the behaviour/resources used, but does anyone know if this is expected behaviour?
Thanks,
Andrew
February 6, 2017 at 3:25 am
anyone?
thanks
February 6, 2017 at 6:58 am
I assume same options? The only thing I can think of is that the first part of checking system items goes slowed in checkdb than checkfilegroup, and it uses less CPU. There are, IIRC, some parallel places in the CHECKDB process and some not parallel.
I assume same options for both?
February 6, 2017 at 7:10 am
CHECKFILEGROUP doesn't do CHECKCATLOG. No idea what parts go parallel, I would like to know too.
February 6, 2017 at 7:31 am
Hi,
Thanks for the reply. Yes, the same options are used in both cases. I thought CHECKALLOC was the only single threaded part... and as it's done by both CHECKDB and CHECKFILEGROUP, it should be fairly consistent.
Any other ideas?
Thank you
February 6, 2017 at 8:20 am
adb2303 - Sunday, February 5, 2017 5:16 AMHi All,We have some circa 5TB databases that take an age to run DBCC CHECKDB against. Quite often we get errors concerning lack of space and other resource issues, so I've written an alternative script using DBCC CHECKFILEGROUP that breaks it up into smaller chunks. Last night it ran for the first time, and although it ran round the filegroups during the allotted window fine, I noticed that CPU hit 70% for the duration. Running a full DBCC CHECKDB on the same database only creates about 10% CPU load on the same server. There's stuff in the script that logs the filgroups it's checked, a window to run in, etc. but in essence, the only command it executes is DBCC CHECKFILEGROUP ('fgname') WITH NO_INFOMSGS, ALL_ERRORMSGS.
The server is SQL Server 2008 R2 Enterprise Edition, SP3. 196GB RAM, 24CPU.
Why would DBCC CHECKDB consume so little CPU compared to DBCC CHECKFILEGROUP?
I know I can use trace flags, hints and resource governor, etc. to control the behaviour/resources used, but does anyone know if this is expected behaviour?
Thanks,
Andrew
I currently manage a 10 TB database ( after page compression ). I had to fight quite a bit to get a full clean DBCC checkdb solution. After reading through Paul Randall's articles i decided to split the process across 7 days. For some very big tables i runn dbcc with phyiscal_only reason is because this system is highly chatty, i always run into disk contention or space issues, atleast getting physical_only will get some level of check. I use OLA's script with some of my custom changes on it ( i think the upgraded version has those features) . With out knowing much about system i can't speak for this but if you have a DR box in close proximity just do a restore of full backup there and run full dbcc there since there will be less contention.
February 6, 2017 at 8:37 am
Hi,
Thanks for the comment. In our case, an iterative DBCC CHECKFILEGROUP approach seems to be okay. We run the same version of my script at both our live and DR sites; one with a physical_only flag set, the other the full DBCC CHECKFILEGROUP. It does what it can during a maintenance window, then the following evening, it picks up where the previous night finished. The piecemeal approach has helped out with disk space and other resources contention quite a bit. However, it does seem to use quite a bit more CPU for some reason. I'm probably going to go with Resource Governor anyway as we can cap the resources used, plus, based on Jonathon Kehayias' SQL Skills blog post, it might run faster as well due to a "speculated" memory grant cost estimation gotcha. I'm just interested to find out why the extra CPU. By the way, the extra CPU is for the entire duration of the CHECKFILEGROUP.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply