The cost threshold for parallelism (CTP) is the cost threshold we define at the Instance/Server level which determines which query will use a parallel execution plan. For each query executed in SQL Server, the optimizer calculates the overall cost for its execution. If that cost is greater than the value configured for the cost threshold for parallelism, then the SQL Server will use parallelism according to the value configured for MAXDOP.
SQL Server will not consider the cost threshold for parallelism (CTP) value and will ignore it if the MAXDOP is configured with a value of 1 OR if the server machine consists of a single logical processor.
The default value of the CTP is 5, which means when the cost calculated by the SQL optimizer is greater than 5 then SQL Server will divide the query plan and will run in parallel to make it run faster. In a few cases, the default value of 5 might be better but usually, this value is considered low and you might need to increase this value to efficiently use parallelism for long queries.
Modifying the value of the cost threshold for parallelism does not require SQL engine restart, so you can examine the SQL server performance with different settings of the cost threshold for parallelism. Although, there are no fixed settings for the CTP but many experts from the SQL Server community suggest a value of 45 or 50 to be used for optimal performance.
Configure the Cost threshold for parallelism using SSMS
To configure the cost threshold for parallelism using SSMS, follow the below-mentioned steps.
- Open SQL Server Management Studio (SSMS)
- Right-click on the SQL Server Instance
- Click on Properties
- Select Advanced on the left pane
- On the right side, Under the Parallelism
- Set Cost threshold for Parallelism value
Configure the Cost threshold for parallelism using T-SQL
To configure the cost threshold for parallelism using the T-SQL command, Execute the below-mentioned T-SQL.
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure ‘cost threshold for parallelism’, 50;
GO
RECONFIGURE WITH OVERRIDE;
GO
The above code will change the cost threshold for parallelism value to 50. You can adjust the value according to your hardware profile. It is recommended to monitor the performance of the SQL Server for some time after changing the value of the cost threshold for parallelism.
You can confirm the changes by checking the Instance properties using SSMS or by executing the following code.
EXEC sp_configure ‘cost threshold for parallelism’;
GO
It will show you the currently configured value for the cost threshold for parallelism as shown in the below screenshot.
Conclusion
In this article, we discuss one of the most important settings in SQL Server which is often ignored to be configured properly, that is the Cost threshold for parallelism. We explain what it is and we share all the steps required to configure this value using (SSMS) and T-SQL.