Non-Default
First, I’m not suggesting that anyone should be using the default value for Cost Threshold For Parallelism. It’s old and moldy and not a good fit for most workloads functioning on modern hardware.
My apologies to Azure SQLDB users who can’t change this setting and leave it up to Microsoft to maybe manage it for them based on ???
Some people out there really like fiddling with settings in a usually ill-informed reaction to Some Script They Found On The Internet, without reading the fine print.
Constantly pushing Cost Threshold For Parallelism up higher because there’s “too much parallelism” is usually a bad strategy, for several reasons.
You should be looking at:
- Which queries are going parallel
- Why parallel plans are being chosen
- The duration vs. CPU of parallel queries
Just to put some easy numbers out there, let’s say you have a query that runs at DOP 8 and:
- Runs for 8 seconds
- Uses 64 seconds of CPU
If you push Cost Threshold For Parallelism up to the point where that query no longer qualifies for a parallel plan and runs at DOP 1, it will:
- Run for 64 seconds
- Use 64 seconds of CPU time
See why this isn’t a bold and exciting strategy embraced by experts around the world?
Scaling
Of course, in the real world, parallelism rarely scales perfectly linearly. That’s okay, as long as you’re seeing a reasonable reduction in wall clock time.
In this picture, I’ve captured the same query running at DOP 8 and 1.
What I said above holds close enough to true.
- At DOP 8, we used ~6.5 seconds of CPU time in ~800ms of wall clock time
- At DOP 1, we used ~3.9 seconds of CPU time in ~3.9 seconds of wall clock time
While we used more CPU time at DOP 8, we only used about 2.5 seconds more of it, and we used it for about 3 fewer seconds overall.
This is the efficiency tradeoff parallelism offers, and this is especially desirable for queries that have to process a lot of data.
Sticking with DOP 8, I’d rather process 10 million rows across 8 threads than 10 million rows on a single thread (especially in Row Mode, but it would be still be preferable in Batch Mode).
Anyway, if you’ve got Cost Threshold For Parallelism set to the default, you should probably bump it up. I still think 50 is a good starting place to get you to a spot where you can make sane decisions.
Remember, global settings are the guard rails for your workload. They are the general rules you want queries to use.
After you set up the guard rails, it’s up to you to define the exceptions.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.