MAXDOP value

Question

MAXDOP value

Bilal H. Bhatt 136

Hi,

I have a production server with 256 cores/512 logical processors hosting SQL Server 2019 dedicated instance. What value should be optimal for maxdop to run checkdb?

i tried with 32,64 maxdop value but it is taking long time around 36 hours.

I have another server with 72 cores and the same instance, the checkdb with maxdop 24 is just taking 31 hours.

Please advise

0 comments

3 answers

Your answer

Answer 1

Rodger Kong 370

Create a Data Collector Set within Performance Monitor will help to locate the issues.

You can record Avg. Disk Queue Length of PhysicalDisk for each physical disks that contain data&log files to indicate disk workload when DBCC excuting.

Recording metrics of SQLServer: Wait Statistics will help to find where the bottleneck happened.

There are many more indicators to help.

By recording and analysing the same metrics with different MAXDOP value, it might show you what happened after your change MAXDOP.

enter image description here

0 comments

Answer 2

博雄胡 745

It's an interesting question, and I'm curious, because it's as confusing to me as how to set up MAXDOP in production.

I think maybe the essential question is what factors affect the parallel benefits and the parallel costs, so that the cost of the parallel eventually outweighs the benefits of the parallel.

IO is obviously a big source of time, if I understand it right, assuming a speed of 500MB/s, the time to create a consistent copy and read it must be at least 14.5*2=29 hours.

0 comments

Answer 3

Erland Sommarskog 134.1K MVP Volunteer Moderator

Obviously, how long time it takes run DBCC CHECKDB depends on the database. Foremost its size, but maybe also its complexity. These databases must be quite big if it takes this long time to run CHECKDB.

Also, CHECKDB takes a lot longer time if there actually is corruption, since it performs a lot of extra works. Many moons ago, I looked at the logs on a server to determine what execution time I could expect for the DBCC I was about to start. I found that in some cases it had taken twenty minutes, and in this case DBCC was clean. On other occasions it had taken 6-7 hours, and in this there was corruption in the database.

I'm afraid that the only way to find out is by some trial and error. And how much CPU power you want to permit for DBCC.

Bilal H. Bhatt 136 Reputation points

2024-11-16T09:49:40.92+00:00

The database dont have any corruption as the checkdb complets without error.. but only thing is i want to know the value of maxdop where checkdb shud complete in minimal time.
Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2024-11-16T09:54:37.1033333+00:00

I guess the gist of my answer is that I don't know, but you will have to experiment.

How big are the databases?

I did some quick googling. Maybe this post from Brent Ozar can be helpful.
Bilal H. Bhatt 136 Reputation points

2024-11-16T10:47:15.0633333+00:00

it is 25TB database...
Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2024-11-16T10:56:41.1733333+00:00

OK, that's a beast.

Keep in mind that CPU is not all, but the speed of your I/O subsystem also matters. You would have to monitor the server when the DBCC operation is running to see if the CPUs are maxing out. Maybe they are only waiting for I/Os to complete, and in that case MAXDOP is not that relevant.

But, obviously, making such an observation in a live system is not easy. And I assume that you don't have the luxury of doing these experiments with an idle server that happens to be sitting there. (Although, ideally, you should run DBCC CHECKDB on a restored backup, so that you test the backup as well.)
Rodger Kong 370 Reputation points

2024-11-18T02:08:27.5033333+00:00

I agree with Erland, with a DB size of this scale and this job, IO might be a bottleneck. You can use Performance Counter in Windows Performance Moniter to log the metrics you want to analyze.
Bilal H. Bhatt 136 Reputation points

2024-11-18T04:20:55.2766667+00:00

Hi,

I/O is performing well as it is always below 5ms. Also i have 256 cores out of which only 9% is only used when the checkdb is running.

i run checkdb with maxdop=64 --> it took 36 hours

maxdop=96 -- it took 23 hours

Actually this is the new server where i am going to migrate exisiting system.
Erland Sommarskog 134.1K Reputation points MVP Volunteer Moderator

2024-11-18T20:46:08.69+00:00

Start DBCC in a new window (or Agent job). Get hold of the spid for that process.

When it's been running for a while, you can query sys.dm_exec_session_wait_stats filtered for that session_id, order by wait_time_ms descending. What wait types do you see on the top?
Bilal H. Bhatt 136 Reputation points

2024-11-20T05:41:14.22+00:00

the only wait type i see is CXPACKET and that is normal when u run the job in parallelism

Share via

MAXDOP value

3 answers

Your answer