This would be my follow-up question because I am trying to understand the CHECKDB behaviors

While running CHECKDB without any MAXDOP specified on below 2 servers:-

  1. Server with 10 Vcores, MAXDOP at instance level 0, DB size 40 GB --> almost hitting 90% CPU spike

  2. Server with 10 Vcores, MAXDOP at instance level 0, same like above DB in terms of schema, but data size over 150 GB --> almost hitting 30% CPU spikes

Can someone help me understand with server with same settings of instance level MAXDOP why one server will get all cores used and while other very few, probably why lower CPU usage.

Just curious how is SQL server making the conclusion of how many schedulers to allocate based on server level MAXDOP setting when running DBCC CHECKDB without specifying MAXDOP.

Thank you

  • 2
    To make the comparison fair, you need a binary replica of the databases. Anyhow, one potential reason is that more data is in cache for the smaller database, while for the larger database you have lots of physical io, meaning the cpus are sitting and waiting for the data to come back. Aug 4 '20 at 16:48
  • @TiborKaraszi: Fair, makes sense. I can try may be by clearing data cache and see as this is one of test boxes. But i am really curious may be second part of my question, how is SQL server deciding based on MAXDOP say 16, if all 16 will be used or may be 2,4,6 ... and so on. Because i am thinking there is no fixed parallelism when checkdb is running
    – Newbie-DBA
    Aug 4 '20 at 17:01
  • 1
    Are you trying to predict how many CPUs will be used by CHECKDB? Why? From the docs, The degree of parallelism is automatically determined by the query processor. If you want to know the code path of how it comes to that decision, you'll need to get chummy with someone at Microsoft (or get a position there with source code access). Certainly it is influenced by things like how much data there is and where it lives, but there are likely dozens of other factors as well. Aug 4 '20 at 17:39
  • Also CPU% on the server is not necessarily directly indicative of how many cores CHECKDB is using. I can be using all the cores at 50% or half the cores at 100% and overall CPU will look the same. Did you look at sys.dm_os_tasks to make sure your assumption (that higher CPU must mean more cores are being used) is accurate? Even if it is, then what? You still don't have any insight into why the query processor chose that many cores. I'll go back to a comment on a different question - if you want to control this (prevent 90%), you can use WITH MAXDOP = n where maybe n = half the cores? Aug 4 '20 at 17:49

Also, CHECKDB in Standard Edition is single threaded, whereas on Enterprise edition it's multi-threaded - could this be the reason why the 2 servers have different performance?
More info : https://www.brentozar.com/archive/2020/08/3-ways-to-run-dbcc-checkdb-faster/

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