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.

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.