What is the CTP in SQL Server?
The cost threshold for parallelism (CTP) is the setting that controls at what point a SQL Server query can “go parallel” using multiple CPU cores.
Why should you not use the default value?
Adjusting the cost threshold for parallelism can make a significant performance improvement by a simple on the fly change. You can often resolve CPU, RAM, and disk bottlenecks.
Microsoft set to 5 by default in the 1990s when they were developing SQL Server 2000.
Defaults aren’t reasonable when based on hardware with more than 20 years old.
When query goes parallel, you don’t necessarily want it to use all CPU cores, as one bad query will impact everything SQL Server is doing.
Also, the default value mostly can cause a large number of fast queries to run in parallel, that would be faster if they run serially.
By the way, this check is a part of our SQL Server Health Check service.
How to set the cost threshold for parallelism
Given the speed and size of today’s hardware, a better value is 45 or 50 for the cost threshold for parallelism.
In some instances, you may still see high CPU usage and excessive CXPACKET wait types even after setting CTP. In this case, you may need to drill a little deeper and may also need to evaluate your MAXDOP setting.
To change the CTP default value using SSMS:
- Right-click on the instance name and select properties.
- Click on the “Advanced” page and set the new value to Cost Threshold for Parallelism.
Also, you can use the TSQL script below to make the change.
sp_configure 'show advanced options', 1;
GORECONFIGURE;
GOsp_configure 'cost threshold for parallelism', 50;
GORECONFIGURE;
GO
More information
Microsoft – Configure the cost threshold for parallelism Server Configuration Option
Ahmad Yaseen, SQLShack – Different Ways to set the Max Degree of Parallelism in SQL Server
Ahmad Yaseen, MSSQLTips – Parallelism in SQL Server Execution Plan
