SQL Server - Cost Threshold For Parallelism

The check for Cost Threshold for Parallelism check has be added to the Database Health Monitor – Quick Scan Report.

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.

If you are adjusting Cost Threshold for Parallelism you may also want to take a look at adjusting the Max Degree of Parallelism at the same time.

If you haven’t tried Database Health Monitor, now might be a good time to give it a try. Its a free application created by StedmanSolutions, LLC, and available at http://DatabaseHealth.com

SQL Server – Cost Threshold For Parallelism

Related Links:

Leave a Reply Cancel reply

Login