Lonny Niederstadt has a couple of posts on scaling DBCC CHECKTABLE based on degree of parallelism. First, he looks at running the command with physical_only:
So we can use this formula when dop, elapsed_ms, and cpu_ms are known:
DOP * elapsed_ms = cpu_ms + idle_ms
That allows the 8 checktable operations to be summarized in this graph. From DOP1 to DOP 8 the cpu_ms of the operation is extremely steady. From DOP 1 to DOP 4 there are significant decreases in elapsed time as dop increases. After dop 4, reduction in elapsed time is slight. Throughout the tested range, idle_ms increased at a nearly linear rate.
In his second post, he looks at full CHECKTABLE runs and not just physical_only:
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.
These are prologue posts to a discussion on the OLEDB wait type.