Select Page

Quick Scan Report – Cost Threshold For Parallelism

The Cost Threshold For Parallelism setting in SQL Server controls the level at which the query optimizer decides to have a query be processed using parallelism, or multiple threads.

The default setting is 5 which may have made sense 15 years ago in SQL Server 2000 or older, but with more modern versions of SQL Server, with much faster processors, more memory, and all around major improvements, the default of 5 doesn’t make sense any more.

I usually set it to 50, then evaluate system performance and adjust it from there. Here is a SQL Script to set the cost threshold for parallelism to 50.

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

You may need to set this to something much higher than 50, this really depends on your overall SQL Server performance.

Related Links: