Tip 74 – Changing Cost Threshold for Parallelism

SQL Server has a lot of configuration values. One of these configuration values is the “Cost Threshold for Parallelism”. This configuration value sets a threshold at which the database engine will consider using parallelism for a query plan. The default setting for “Cost Threshold for Parallelism” is 5. Which means anytime the optimizer calculates a plan exceeds this threshold, the optimizer will consider using plans that will go parallel.

The problem with default values is they are not always appropriate for a given instance’s work load. The default value of 5 was created as a recommendation a long time ago. Since then, server hardware has changed considerably. Now you have wonder if this is still a valid default value.

If your server has a lot of simple queries then maybe this setting works. But if you have a mix of simple and complex queries then maybe not. If this setting is too low you will have a lot of queries considering parallel plans when they may not benefit from parallelism.

It is easy to change the “Cost Threshold for Parallelism” by running the following code:

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE  
GO  
EXEC sp_configure 'cost threshold for parallelism', 50;  
GO  
RECONFIGURE  
GO

This code changes my ”Cost Threshold for Parallelism” to 50. This change occurs immediately and doesn’t require a restart of the instance.

If you think your “Cost Threshold for Parallelism” might not be set correctly, first do some analysis of cached plans. This analysis should determine the average subtree costs of your execution plans. See my article here for how to do that analysis. By having the historical average cost of your existing plans should help you determine an appropriate setting for your “Cost Threshold for Parallelism” setting.