Enabling Growth Through Performance
Enabling Growth Through Performance

Cost Threshold for Parallelism and MAXDOP

There are a lot of “most overlooked” configuration settings in SQL Server, but I’m going to cover these two alone since they are quite closely related: Cost Threshold for Parallelism and Max Degree of Parallelism.

What’s all this about?

First let’s discuss Degree of Parallelism in its most simple form. When given a task to complete SQL Server has a lot of choices to make about how to get the work done. One of the most important parts of that plan-of-attack is whether to do all the work sequentially or to split the task into multiple smaller tasks and have the smaller tasks done all at once by different CPUs.

Gather Performance Metrics!

If you are here then its likely that you are dealing with some kind of performance issue or vague resource contention. If so, then you’ll need deep insight into these systems, which is why we developed the Vroom Performance Suite. So before we move forward (or maybe after the article), give the Vroom Performance Suite a try and put the days of poor performance behind you.

Back on Topic

Parallelism is easily one of the coolest things to happen in computer science but overuse can be just as detrimental as not enough. For one thing, there are only so many resources on a given server. If there are 4 CPUs and 100 connections then it doesn’t make sense for each of those connections to spawn 4 threads each. Imagine the contention! All of the parallel tasks would fight things like I/O bandwidth, memory and locks. Another thing is the overhead. Generating a parallel plan is not a trivial task and it takes time to set up all of the required threads, wait on their completion and piece all of the results from the smaller units of work back together.

How can we control this potential storm of threads?

Max Degree of Parallelism

That’s where Cost Threshold for Parallelism and Max Degree of Parallelism save the day!

Max Degree of Parallelism is basically the maximum number of sub-steps that SQL Server will be allowed to break a tasks’ steps into. MAXDOP=1 means all steps are sequential, MAXDOP=4 means that’s each step can be broken into 4 smaller steps and executed in parallel, and the scariest setting of all (which happens to be the SQL Server default!!) is MAXDOP=0 which means that SQL Server can at its discretion generate an unlimited number of sub-steps, which to me is just nonsense.

Cost Threshold for Parallelism

Next, we need to figure out how to prevent SQL server from generating parallel plans for rather trivial queries. That’s where Cost Threshold for Parallelism comes in. For those of you familiar with SQL Server execution plans, you’ll recognize the sub-tree costs. This is basically a proprietary estimate of how many resources a SQL statement will consume or how “heavy” as statement is or will be. The setting for Cost Threshold for Parallelism tells how high the cost of a SQL statement must be before a parallel plan will even be considered. The default value for this most crucial setting is a mere 5.

Putting it all together

When you combine the lunacy of an unlimited degree of parallelism with the rock-bottom low parallel plan creation threshold of 5 you will have problems. Under load these problems will crop up in the form of high CPU usage, slow query response times, lock contention, higher than normal deadlocks, etc.

There are a lot of opinions in the world of SQL Server performance tuning but luckily for you there is a pretty solid consensus that these defaults are baaad news.

What should I set them to?

Max Degree of Parallelism

The proper setting for Max Degree of Parallelism is a bit of art which will requires some knowledge of your CPU architecture, core count, logical threads (hyper-threading) and NUMA configuration.

  • For hyper-threading, the MAXDOP should be set at or below half of the number of available schedulers, which can be obtained via:
SELECT COUNT(0)
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
  • For non-HT CPUs the number should be set at or below the number of cores available to SQL Server.
  • If you have a NUMA configuration then the MAXDOP should be set at or below the number of cores configured for any one NUMA-node.

    NUMA is a memory design which allows a CPU to use “its own” memory faster than the generally available pool of memory in non-NUMA architectures.

Cost Threshold for Parallelism

While your mileage may vary, the recommendation for Cost Threshold for Parallelism is between 25 and 50. I’ve seen it as high as 100 and as low as 10…. but 5 is just crazy unless you have all the horsepower in the world and you are the only connection to the server.

2 Comments

  1. AIJAZ ALI on August 13, 2021 at 5:10 pm

    Hi Josh – Thanks for this article. I recently joined a new company and we have one prod SQL server running in AWS. It has 4 CPUs (Numa) with default values of MaxDop and Cost Threshold of Parallelism. With a small server like this would you still recommend to adjust the values? Like MaxDop = 2 and Cost Threshold between 25-50?

  2. Josh Patterson on November 16, 2022 at 10:17 pm

    AIJAZ ALI, you’ve asked a question for which the answer is sure to cause much controversy – but I’ll give you my take:

    (a) Using Microsoft guidance, a decent MAXDOP number for your server would be 4 (the logical CPU core count).
    (b) A decent starting point for Cost Threshold is 50. Some will say that’s wildly too low and other that its absurdly too high – but its a reasonable number for the threshold as corroborated by Ozar.

    Does that help?

    (a) https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16
    (b) https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/

Leave a Comment