Increasing cost threshold for parallelism may improve your SQL Server performance

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.

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:

  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.
Shows the cost Cost threshold option in the server properties page.
Figure 1 – Cost threshold for parallelism property in SQL Server.

Also, you can use the TSQL script below to make the change.

  1. sp_configure 'show advanced options', 1;
  2. GO
  3. RECONFIGURE;
  4. GO
  5. sp_configure 'cost threshold for parallelism', 50;
  6. GO
  7. RECONFIGURE;
  8. 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

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.