SQL Server Performance Tuning

Increasing Cost Threshold for Parallelism May Improve Your SQL Server Performance

Updated October 29, 20253 min read

Why a cost threshold of 5 is too low. Ploom hops easily over a hurdle barely above the ground.

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 ran 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

  1. Right-click on the instance name and select Properties.
  2. Click on the “Advanced” page and set the new value to Cost Threshold for Parallelism.
Figure 1 - Cost threshold for parallelism property in SQL Server
Figure 1 - Cost threshold for parallelism property in SQL Server

How to change it with T-SQL script

T-SQL
sp_configure 'show advanced options'
	,1;GO

RECONFIGURE;GO

sp_configure 'cost threshold for parallelism'
	,50;GO

RECONFIGURE;GO

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜