SQL SERVER – Why Suddenly DBCC CHECKDB Running Very Slow?

Recently during Comprehensive Database Performance Health Check, I was asked a fascinating question. They wanted to know why suddenly their DBCC CHECKDB process started to run very very slow.

My customer who has no full-time DBAs in their organization. They have developers who are extremely smart and manage their database. This is the reason every other month they hire me to help them with their SQL Server Performance Optimization. Earlier I had helped them with tuning their system and had set up their DBCC CHECKDB process which is essential to help them understand if there is any consistency error with them or not.

When I had setup DBCC CHECKDB for my customer it was running extremely fast, however, during the time, it had actually slowed down very much. Earlier it was finishing in just 8 minutes, and now it was taking around 24 to 35 minutes. They really wanted me to check into their database and see if there was some major issue with consistency which was creating this kind of long delay.

When I started to look around inside the server, my first question was if they have changed anything inside the server. Incidentally, they had changed something, which was slowing down the DBCC CHECKDB. Let us see what the change was and how I was able to fix that in a second.

Remember my customer was using SQL Server 2016 and the feature I am discussing today is only applicable for SQL Server 2016 and later version. You can read my earlier blog post over here.

Original Setup

When I had helped them to set up DBCC CHECKDB, I had noticed that customer had 16 logical processors. After proper analysis of their system, I had supported them to set up their Max Degree of Parallelism to 2. (Please note, do not change this setting just reading this blog post. It is entirely possible that value of MAXDOP for your system is different from the what my customer needed on their system)

Now, the next thing was to setup DBCC CHECKDB, and after running quite a few tests on their database, I determined that it would be optimal to run their DBCC CHECKDB with 8 logical processors. I figured out that when I ran DBCC CHECKDB with all the 16 logical processors, it was not running any better than running with 8 logical processors. After running this test, I had written following command for their DBCC CHECKDB.

DBCC CHECKDB ([WideWorldImporters])
WITH MAXDOP = 8;

Bad Configuration Change

Now, during a time, one of their developers noticed my script with MAXDOP with 8. He checked the server and saw that there are 16 logical processors and I am limiting the query to run with only 8 logical processors. He immediately removed my command WITH MAXDOP = 8. As per his understand now the command should run with all the logical processors as there is no restriction. Actually, this was the bad configuration change.

When we change Max Degree of Parallelism, it applies to entire server as it is server-level settings. So when he removed the command WITH MAXDOP = 8, it actually made the DBCC CHECKDB run even worst. Previously it was running with 8 logical processors, and now it started to run with 2 processor as the global settings of MAXDOP to 2.

I went back and added WITH MAXDOP = 8, and their CHECKDB started to work faster.

Using All the Processors

If you want your query to run with all the available processors, I suggest you run your command WITH MAXDOP = 0 as described in the following script.

DBCC CHECKDB ([WideWorldImporters])
WITH MAXDOP = 0;

Remember, one should not be limiting their processor without doing proper benchmarking tests. In general, if you are not sure, just keep the original settings as it is and ask an expert to take a look at your settings. Sometimes changing a small configuration can make a big difference in your performance. I have seen a similar negative impact on index rebuilding process as well.

Anyway, if you want to discuss your server settings, you can send me an email at pinal @ SQLAuthority.com, and we will consider, how you can change few of your settings and get optimal performance from your system.

Reference: Pinal Dave (https://blog.sqlauthority.com)