Deciding Cost Threshold for Parallelism

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!