0

I am trying to understand the DBCC CHECKDB usage with MAXDOP settings-

If i run DBCC CHECKDB with MAXDOP = 0 on a SQL instance where my MAXDOP settings is 8, what is happening actually? Is all of my available say 98 schedulers would be used? or still just 8? or just few?

if i run DBCC CHECKDB with no MAXDOP say a default with server level MAXDOP 8 , should i see then all 8 schedulers used? or it can be less than 8

Why I am raising above questions is for same CHECKDB runs for some server it seems to use less schedulers and some server with same run all schedulers causing 100 % CPU spikes

Thank you

Improve this question
5
  • Out of curiosity, why would you explicitly say MAXDOP = 0 in the command? If you want it to use a specific number of CPUs that differs from the instance max, specify that number.
    – Aaron Bertrand
    Aug 3, 2020 at 19:41
  • @AaronBertrand: We are still testing those numbers to see as diff servers are giving diff numbers. Using 0 looked to be fast with con of CPU spiking to 80-90% during the run
    – Newbie-DBA
    Aug 3, 2020 at 20:04
  • @AaronBertrand: Also as mentioned with MAXDOP= 0 not all servers get those spikes. Based on the answer below i am understanding that SQL may choose a MAXDOP ( This is what i really am curious, how and what makes SQL comeup with that MAXDOP) between 1-64 and probably why when it goes to max i am seeing those spikes
    – Newbie-DBA
    Aug 3, 2020 at 20:23
  • 1
    I still feel like if you are bothered by the unpredictability of saying "please use all the schedulers you want regardless of instance-level MAXDOP settings" you shouldn't keep specifying MAXDOP = 0 in the DBCC commands. If you want that to be more predictable, you can specify MAXDOP = some number (like 8), or don't add the MAXDOP option to the command in the first place. MAXDOP = 0 is only useful in the case where (a) you have some instance-level MAXDOP AND (b) you want to ignore it.
    – Aaron Bertrand
    Aug 3, 2020 at 22:45
  • @AaronBertrand I see now, thanks. So even if my dbcc checkdb uses parameter with MAXDOP=0 , but my instance level MAXDOP is 16, it will ignore that MAXDOP of 16 and can go anywhere between 0-64 as mentioned in below answer
    – Newbie-DBA
    Aug 3, 2020 at 23:20

1 Answer 1

Reset to default
2

If i run DBCC CHECKDB with MAXDOP = 0 on a SQL instance where my MAXDOP settings is 8, what is happening actually? Is all of my available say 98 schedulers would be used? or still just 8? or just few?

Per the documentation, in this case "If MAXDOP is set to zero then SQL Server chooses the max degree of parallelism to use" just like it does with any other query where you override the default MAXDOP settings. SQL Server can use up to the maximum of 64 processors when MAXDOP is effectively set to 0.

if i run DBCC CHECKDB with no MAXDOP say a default with server level MAXDOP 8 , should i see then all 8 schedulers used? or it can be less than 8

Then SQL Server chooses a max degree of parallelism up to 8 just like it does with a normal query with a MAXDOP override specified.

You should check to make sure the servers are the same, have the same instance level CPU settings, the same MAXDOP (check database level MAXDOP as well), and ensure that the database being checked is the same. All of these can affect the performance and parallelism chosen.

Improve this answer
5
  • Thanks. But this is where exactly i am confused "If MAXDOP is set to zero then SQL Server chooses the max degree of parallelism to use" does that Mean if i have instance level 16 then its 16 then whats the difference of it running as default ?
    – Newbie-DBA
    Aug 3, 2020 at 19:35
  • @Newbie-DBA Clarified above - it is up to 64 if maxdop setting being used in effect is 0.
    – LowlyDBA - John M
    Aug 3, 2020 at 19:44
  • Thank you sir, makes sense now. However if we have over 100 core, you/doc means still max it can go is upto 64 processors? Also if i am not wrong, even though we say upto 64 , it may use 2,4,8,... any number upto 4 correct?
    – Newbie-DBA
    Aug 3, 2020 at 19:48
  • Correct, the most it will ever use is 64. MAXDOP sets a ceiling so it can utilize the maximum number or any number below that based on the query engine recommendations.
    – LowlyDBA - John M
    Aug 3, 2020 at 19:51
  • Thank you, now i see. However for your second part everything is same across the servers with only difference in db size. Even database structure wise same. Just amount of data varies. So i was wondering why one server always spike checkDB to 100% this is database with just 40 GB in size while database with 200 GB stay at 30% when both run with same MAXDOP
    – Newbie-DBA
    Aug 3, 2020 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.