February 27, 2020 at 5:23 am
Hi All
We are running DBCC CheckDB in our server during low activity time.
However it takes 2 until 3 hours to complete and CPU usage is 95 - 100 % during that time
I want to make the process faster and reduce CPU usage if possible
FYI :
CPU = 2 - Development Server , SQL 2016
CPU = 8 - Production
I heard about DBCC CheckDB with Physical Only option which will reduce the amount of time , HOWEVER I am afraid this option will skip the important thing from DBCC CHECKDB ..
Another thing is using CheckSum as in Oracle, Anyone knows this ?
Your feedback is much appreciated
Thank you
February 27, 2020 at 1:25 pm
There is no single switch you can throw to make DBCC faster. None. It's going to take the time it takes. If you're seeing 95% CPU when you run it though, maybe you need better CPU, because that's not been my experience with it.
However, the best way to deal with improving DBCC is to split the logical and physical checks, as you suggested. You can do this by taking a database backup, restoring it to a non-production server somewhere, then running the DBCC logical checks there. Any logical inconsistencies will be in the backup (by the way, one of the biggest reasons why you should be checking your logical consistency is because when it goes, it's in your backups, so a restore becomes a nightmare). That radically reduces the load on a production machine. You still need to do the physical checks on the production server. Those can't be offloaded. However, that should help.
"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
February 27, 2020 at 3:32 pm
Grant nailed it there - do your checkdb on a different server.
you can even include it in the restore process, then you get a restore test and a checkdb all in one process
MVDBA
March 2, 2020 at 12:49 pm
Hey,
According to Paul Randal:
"I strongly advise not using BACKUP DATABASE ... WITH CHECKSUM as an alternative to DBCC CHECKDB - it will not detect errors introduced by memory problems, SQL Server bugs, or IO subsystem corruptions of pages using torn-page detection."
Like Mr. Fritchey said, offload your checks.
Also Paul Randal:
"Bottom line - you can't avoid running DBCC CHECKDB - don't fall into the trap of running something faster. Offload your checks to a non-production server but continue running DBCC CHECKDB - it's the only thing that will find all corruptions".
Also there's a link regarding DBCC checks from sqlskills.
How big is your database?
when we had a case, one of our VLDB 2+ TB took 3 hours to complete a DBCC, perhaps you might have to check your I/O subsystem or CPU as Grant said,
Regards,
March 2, 2020 at 1:13 pm
that raises an interesting point - I'm slightly misquoting Alejandro here
"memory problems, SQL Server bugs, or IO subsystem"
All of this morning My DR instance has had all 3 of these and every restore database failed 🙁
Guess what - windows update... thankfully it's not a production server
but i cant do DBCC checkdb when my DR instance is down.
Just putting that idea out there
MVDBA
March 3, 2020 at 4:41 am
Thanks for the response All
How about if I run Full DBCC CheckDB in Mirror server and DBCC CheckDB with Physical Only in Primary server ?
as We have ALWAYS ON configuration ( Primary and Secondary server )
in Dev server , database size is approx. 2 TB , CPU - 4
in Prod , size is approx. 2.5 TB , CPU =8
as you can see , database size is different between Dev (Non - Prod ) and Prod so I don't think it makes sense to run physical in Prod and logical in non-prod.
also is there a command to run LOGICAL only ?
I only know DBCC CheckDB with Physical Only 🙂
March 3, 2020 at 9:33 am
CHECKDB is actually a series of commands. Just break down the commands and do them separately. The documentation is online and elsewhere.
Your prod and non-prod databases have to be the same (a restore) for this to work. If you've got totally different databases, doing checks on one and not the other is a waste of time.
"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 3, 2020 at 9:07 pm
Its also worth noting that, because you ran DBCC CHECKDB on a secondary to offload and it didn't presented corruption, it doesn't mean you don't have corruption on the primary database.
It just tells you that your secondary isn't corrupted, doesn't tell you about the primary, i think i heard these words off from Gail Shaw once.
The only way it might work is if you are using some sort of SAN Snapshot copy of your database, in our case we have a SAN and we make snapshots of the databases, we restore these snapshots and these snapshots use the same blocks that are being used in the SQL Server production environment, that might help you with the PHYSICAL part of the database, there's also corruption at the memory level and the SAN doesn't help you with that case.
The best way is to make the DBCC Checkdb on your primary database to make sure you don't have corruption on that one.
Also, today i got the daily mail from SQLSkills and whenever i get the email, the part im most excited off of it is "The Curious Case of..." it's usually stories from customers or questions they ask Paul Randal and he make blog posts regarding these topics.
The Curious Case of… whether corruption can propagate to secondary databases
regards,
March 3, 2020 at 11:54 pm
Thanks for the response
Another concern is the alert that we get if CPU usage is very high or Disk is busy more than 50% ...
As a DBA , I need to get rid of the alert ( at least try reducing the number of coming alerts )
If I run dbcc checkdb on primary and dbcc checkdb with physical option in secondary , I will still get the alert coming from Primary server telling me that CPU usage is very high
Honestly I feel this combination is good as Primary is more important than secondary so we need to make sure we run FULL CHECK DB in Primary server ( not the opposite )
just thinking about the alert now :p
March 3, 2020 at 11:55 pm
Sorry ,
My bad … it should be DISK IS BUSY MORE THAN 90% ( not 50% ) 🙂
March 4, 2020 at 9:01 am
Thanks for the response
Another concern is the alert that we get if CPU usage is very high or Disk is busy more than 50% ...
As a DBA , I need to get rid of the alert ( at least try reducing the number of coming alerts )
If I run dbcc checkdb on primary and dbcc checkdb with physical option in secondary , I will still get the alert coming from Primary server telling me that CPU usage is very high
just thinking about the alert now :p
which is why you offload this to a remote server, your primary and secondary are protected
MVDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply