CTP in SQL Server Can Improve Your Performance
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 not use the default value?
Setting the cost threshold for parallelism can provide a significant performance boost with a simple change on the fly. You can often solve CPU, RAM and disk bottlenecks.
It was set to 5 by default in the 1990s when Microsoft was developing SQL Server 2000.
Default values are not reasonable when based on hardware more than 20 years old.
When the query goes in parallel, you don’t want it to use all CPU cores, as a faulty query will affect everything SQL Server does.
Also, the default value can often result in a large number of fast queries running in parallel, which would be faster if run serially.
How to determine the cost threshold for parallelism?
Given the speed and size of today’s hardware, 45 or 50 is a better value for the cost threshold for parallelism.
In some cases, you may continue to see high CPU utilization and excessive CXPACKET wait types even after setting CTP. In this case you may need to dig a little deeper and also evaluate your MAXDOP settings.
To change the CTP default value using SSMS:
- Right click on instance name and select properties.
- Click on the “Advanced” page and set the new value to Cost Threshold for Parallelism.
You can also use the following TSQL script to make the change.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'cost threshold for parallelism', 50;
GO
RECONFIGURE;
GO
