Let’s talk about cost threshold for parallelism. This is one of the main deciders in a query going parallel. Do you know what it’s set to on your databases?
Cost Before Profit
The cost threshold for parallelism is the value where SQL Server will consider going from a serial plan to a parallel plan for execution. If SQL Server encounters a query with an estimated cost higher than the cost threshold, SQL can go with a parallel plan. The cost threshold for parallelism can be set anywhere between 0 and 32767.
How Low Can You Go
Microsoft sets the default value at 5 and claims that it’s adequate for most systems. I’m sure 5 is right for some systems but I would argue against that being the starting point for all.
5 is pretty low and is going to lead to a likelier chance of queries going parallel. Parallel plans can improve query performance compared to serial plans; however, that does not mean you want ALL of your plans going parallel, especially if it’s not going to provide any noticeable benefit.
A database workload that is primarily OLTP could be getting too much parallelism that actually leads to decreased performance. If you’re still sitting on the default, bumping up the cost threshold can be a quick way to improve performance.
For workloads that are mainly intense processing and generating reports, you’ll want more parallelism to handle the load because SQL Server’s effort to go parallel has better returns.
Setting Cost Threshold for Parallelism
What should you set as your cost threshold for parallelism? I usually go with 50 since I’ve seen that tossed around in various blogs and books. 50 isn’t perfect though. It’s a starting point that you can better adjust depending on your workload. Going slightly higher or slightly lower might be better. You want to find that sweet spot where queries aren’t needlessly going parallel.
You can run the following script to change cost threshold for parallelism to 50:
USE ExampleDB ; GO EXEC sp_configure 'show advanced options', 1 ; GO RECONFIGURE GO EXEC sp_configure 'cost threshold for parallelism', 50 ; GO RECONFIGURE GO
Cost threshold for parallelism can also be changed in SSMS by right clicking the server name in Object Explorer, selecting Properties, choosing the Advanced page, and modifying in the Parallelism section:
The change takes affect without needing a restart of SQL Server.
Thanks for reading!
3 thoughts on “Deciding Cost Threshold for Parallelism”
Oh no not another one throwing the 50 number as a default…
Wouldn’t you rather set your CTFP number based on facts rather than guesses?
There is actually a way to do that:
LikeLiked by 1 person
I agree, using scripts like what you posted or monitoring how your system reacts to cost threshold changes and adjusting is going to be better than 50. I’d start with 50 instead of 5 as a starting point and use facts/metrics to adjust from there.
Thanks for the comment!
LikeLiked by 1 person