Here it is, 2017. A full 20 years since SQL Server 7. Back then, there was a developer named Nick. Nick was on the development team, and apparently the query-costing-magic development was done on his machine. All of it. The magical metric was that for every second a query ran, it would cost another query buck. If a query ran for 5 seconds, meaning it cost 5 query bucks, the optimizer would start examining parallel plans.
These query bucks still exist today, but due to massive inflation, they are no longer worth the same amount. In the words of Joe McGrath “See this quarter? It used to be a nickel.”
They’re still a valuable reference metric, but they are, even for actual plans, still an estimated cost. Pay attention to them. Pay attention to the fact that THEY DON’T MEAN SECONDS ANYMORE.
The thinking, of course, is that if you gave a query more cores and more threads, it would run faster. If you give it another core, 2x faster. If you give it four cores, 4x faster. The math behind this was done by a guy named Gene Amdahl. I didn’t know anything about Gene Amdahl until Adam Machanic talked about him, and that’s still as much as I know about him.
Despite all that, I’m willing to allow that your server, even if it was built special for 2008R2, is better than Nick’s computer. I mean, my cell phone is better than your server, but still.
So why is it that you’d want to use 1997 settings on present day hardware?
Small queries, let’s say with a cost under 50, typically don’t see any stunning improvements when comparing parallel vs. serial execution. They may even degrade slightly just because of the startup and computational overhead of going parallel.
But the real stinker is something I’ve talked about a couple times before. You can actually harm concurrency and force yourself into some nasty THREADPOOL situations if you don’t control parallelism. Parallel queries use more threads. Your server has a finite number of threads. When every little query is going parallel, you run out of threads faster. When you run out of threads, no one is happy. It’s like running out of toilet paper. People start getting anxious.
You can also run into situations where, when many queries start going parallel, one of many threads may get blocked, or held up, and then SOS_SCHEDULER_YIELD kicks in. That’s SQL telling your query it’s time to take a break and let someone else try to run. If your server has long average waits on CXPACKET or SOS_SCHEDULER_YIELD, you can bet your bottom query buck that CPU contention is occurring.
This is beyond Cost Threshold, though it’s important to start here. Usually 50 is a decent number, but you can go up or down based on your workload. You’ll also want to set MAXDOP appropriately.
After that, it’s all sorts of nasty query and index tuning to get things under control.
You should call a professional for that.
Brent says: this is one of those areas where I wish Microsoft would publish better guidance (if they won’t flat out change the defaults). 5 is clearly wrong today, and I’m kinda surprised that they haven’t gathered better evidence from Azure SQL DB and 2016’s phone-home defaults.