Default Parallelism Cost Threshold
The parallelism cost threshold setting in SQL Server determines when the query optimizer should use multiple threads for query execution. A low threshold can result in SQL Server spending excessive time planning parallel execution for relatively simple queries, leading to inefficiencies. Conversely, setting the threshold too high may cause SQL Server to avoid parallelism for complex queries, reducing query performance.
The optimal setting varies depending on the workload and the server’s hardware. The default value is 5, but many experts and vendors suggest increasing it, especially on modern hardware or in virtualized environments.
Why Adjust the Cost Threshold for Parallelism?
- Low Threshold: If the cost threshold is set too low, SQL Server may attempt to run queries in parallel unnecessarily, which can lead to CPU overhead and longer query execution times, especially for simpler queries.
- High Threshold: If the threshold is too high, SQL Server might avoid using parallelism even for queries that would benefit from it, resulting in slower query performance.
Suggested Action
Consider increasing the parallelism cost threshold to a value like 50, especially if you're running on modern hardware or in a virtualized environment, to better optimize parallel query execution
How to Alter the Default Cost Threshold for Parallelism in SQL Server
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
RECONFIGURE
GO
How Aireforge Detects Suboptimal Cost Thresholds
Aireforge flags settings below 50 as suboptimal for many modern systems, recommending an increase to prevent potential inefficiencies. If the threshold is set below 15, this may result in excessive parallelism for simple queries, leading to CPU overhead. Aireforge suggests increasing the value to avoid these pitfalls and enhance performance.