You might have read about the term Query Buck or Query Bucks. The term was coined by Kendra Little according this Brent Ozar post.
Basically, in order for a query (or an operator to be more specific) to go parallel, SQL server will have to estimate more than a certain amount of execution “time” for a query. This “time” was allegedly trimmed in on a machine from 1997 (see for instance this post).
There are plenty of articles out there recommending you to up the “cost threshold for parallelism” configuration option. Most of you already know this, if not fire up your favorite search engine and spend a few minutes reading about it.
My aim here is to do a totally non-scientific test if I can translate these fictitious seconds to what they correspond to on a reasonably modern hardware – which is my laptop from 2020. Here’s the query I use and I tuned the number for TOP to get pretty close to 5 (seconds), by looking at he “Esimated Subtree Cost” for the SELECT operator in the actual execution plan:
SELECT TOP(60900) * FROM Sales.SalesOrderDetail
) AS i
ORDER BY OrderQty
I then looked at the operators in the actual execution plan for the aggregated seconds for the next-top operator:
So, for this query, 5 seconds on a machine from 1997 corresponds to 0.082 seconds on my laptop from 2020. I.e., my machine is about 60 times faster (50 / 0.082) than that machine from 1997. So, on Nick’s computer from 1997, “cost threshold for parallelism” of 5 mean that the query should go parallel if the estimated cost is higher than 5 seconds. If I want the “go parallel of the query is slower than 5 seconds” on my machine, I should configure “cost threshold for parallelism” to 300.
This is of course totally non-scientific. I only look at one query, for instance. And I assume that the aggregated seconds in the execution plan actually is a relevant way to measure this.
For reference, the CPU I’m using is reported by msinfo as below. Needless to say, I’m on an SSD disk.
Processor 11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz, 2803 Mhz, 4 Core(s), 8 Logical Processor(s)