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 the CTP to 5 by default in the 1990s when they were developing SQL Server 2000.
Defaults aren’t reasonable when based on hardware that's more than 20 years old!
When query goes parallel, you don’t necessarily want it to use all the 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.
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.
How 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.
How to change it with T-SQL script
sp_configure 'show advanced options'
,1;GO
RECONFIGURE;GO
sp_configure 'cost threshold for parallelism'
,50;GO
RECONFIGURE;GO
