A Quick Look at scalability [Part II]: #SQLServer Checktable without physical_only option

In my last post, we looked at the scalability of 'dbcc checktable... with physical_only' for two tables, from dop 1 to dop 8.

A Quick Look at Scalability [Part I]: #SQLServer Checktable with physical_only
http://sql-sasquatch.blogspot.com/2017/05/a-quick-look-at-scalability-sqlserver.html

While the operation achieved high utilization of a single vcpu for both tables at dop 1, as dop increased cpu utilization did not keep pace.  Total cpu_ms for the operation was fairly stable.  The results was that elapsed time did not decrease in the same proportion as cpu resources were added by increasing dop.  The main decreases in elapsed time were realized by dop 4, further decreases from dop 5 to 8 were minimal.  The following two graphs summarize these findings well.

 

All right.   What if we look at full checktable operations, without the physical_only option?  Does that operation scale the same, better, or worse than physical_only?

Here's the results from TableA.  Cool!  Once again, cpu_ms for the operation remained fairly stable.  Rather than continuing to increase as dop increased, idle_ms reached an early peak and remained fairly stable afterward.  The result is decreasing elapsed time with each increase in dop from 1 to 8.  It looks as if elapsed time may continue to decrease beyond dop 8... someday I'll get to test :-) But the gains after dop 8 look as if they'll be smaller and will almost certainly drop off by dop 12.

The results from TableB sent me back to test again, and dig further into the data I gathered.  Check it out.  The cpu_ms is still fairly stable.  Elapsed time decreases with each increase of dop.  But... whoa.  What an unexpected shape from idle_ms.

I thought the 'hill' must be due to a 'one-off' abnormality, not likely to be repeated.  So I repeated tests for dop 2, 3, 4, and 5.  Plugged the results into the graph and... whoa.

Here's one more test run.  This is probably the most characteristic... but so many factors can throw the numbers off.

So the good news for today is that checktable operations without the physical_only option scale farther/better on my test tables than checktable with physical_only.  While with physical_only scaling benefits in elapsed time are primarily seen only to dop 4, without the physical_only option elapsed time benefits to increasing dop extend at least to dop 8.

And we saw that the shape of scalability graphs is pretty volatile :-)  That's largely because modest changes in elapsed time are multiplied by dop in this calculation to arrive at the idle_ms number - that idle_ms number is the one that changes shape most readily.