Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted February 24, 2021

Tip 74 – Changing Cost Threshold for Parallelism

By Greg Larsen

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;  
EXEC sp_configure 'cost threshold for parallelism', 50;  

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM