The Max Degree of Parallelism, or MAXDOP, is one of the most known settings in the SQL Database Engine. There are guidelines of how to configure it that have intricate dependencies on the type of hardware resources you’re running on, and there are several occasions where someone might need to veer of those guidelines for more specialized workloads.
There are plenty of blogs on these topics, and the official documentation does a good job of explaining these (in my opinion). If you want to know more about the guidelines and ways to override for specific queries, refer to the Recommendations section in the Configure the max degree of parallelism Server Configuration Option documentation page.
But what does MAXDOP control? A common understanding is that it controls the number of CPUs that can be used by a query – previous revisions of the documentation used this abstraction. And while that is a correct abstraction, it’s not exactly accurate. What MAXDOP really controls are the number of tasks that can be used in each branch of a query plan.
For most use cases, talking about the number of CPUs used in a query, or the number of concurrent tasks scheduled won’t have a practical difference. But sometimes it’s relevant to know the full story, to go beyond the generalization, especially when troubleshooting those ginormous complex plans that involve dozens of operators – you know who you are ☺
So before we dive deeper into it, let’s make sure we’re all on the same page regarding a few important terms that will be used in this post, and you find in documentation. These my simplified definitions:
Let’s get on with the example. My SQL Server is configured with MAXDOP 8, CPU Affinity set for 24 CPUs across two NUMA nodes. CPUs 0 through 11 belong to NUMA node 0, CPUs 12 through 23 belong to NUMA node 1. I’ll be using the AdventureWorks2016_EXT database, and have enlarged the tables in the query 50 fold, to have the time to run all the DMV queries before the following query was done:
SELECT h.SalesOrderID, h.OrderDate, h.DueDate, h.ShipDate
FROM Sales.SalesOrderHeaderBulk AS h
INNER JOIN Sales.SalesOrderDetailBulk AS d ON h.SalesOrderID = d.SalesOrderID
WHERE (h.OrderDate >= '2014-3-28 00:00:00');
Here is the resulting actual execution plan, divided into its 3 branches:
In this example, looking at the plan properties, we can see more information about how many threads SQL Server will reserve to execute this plan, along with the worker’s placement on the NUMA nodes:
The Database Engine uses information about the plan shape (which allows for 2 concurrent branches in this example – more on this further ahead) and MAXDOP configuration (which is 8), to figure out how many threads to reserve. 2 x 8 = 16. So MAXDOP is setting overall how many parallel threads are spawned for the query (stay with me).
The threads can be reserved across all NUMA nodes, or be reserved in just one NUMA node, and this is entirely dependent on scheduler load at the moment the reservation is made at runtime. In this case, the reservation was split between both NUMA nodes. But a few minutes later, when I executed the query again, thread reservations were all on NUMA node 1, as seen below from the actual execution plan:
Back to the 1st execution: if there are 3 branches in the execution plan, why can only 2 branches execute concurrently? That’s because of the type of join in this case, and the Query Processor knows this. A hash join requires that its build input be available before starting to generate the join output. Therefore, branches 2 (build input) and 3 (probe input) can be executed sequentially (for more details on hash joins, refer to the documentation on Joins). Once the build input is complete, then branch 1 can start. Only at that point, can branches 3 and 1 be executed concurrently. So we see 2 concurrent branches.
The live execution plan gives us a view of this with branches 3 and 1 in flight, and 2 completed:
Ok, so how about tasks? Didn’t I say MAXDOP limits how many tasks are spawned for each branch? Let’s query the sys.dm_os_tasks DMV and find out what’s happening:
SELECT parent_task_address, task_address, task_state, scheduler_id, worker_address
WHERE session_id = 100 -- my session ID
ORDER BY parent_task_address, scheduler_id;
With the following result:
Notice there are 17 active tasks: 16 child tasks corresponding to the reserved threads (8 for each concurrent branch), and the coordinating task. The latter can be recognized because the column parent_task_address is always NULL for the coordinating task.
Each of the 16 child tasks has a different worker assigned to it (worker_address column), but notice that all 16 workers are assigned to the same pool of 8 schedulers (5,8,10,11,12,18,20,22), and that the coordinating task is assigned to a scheduler outside this pool.
So, MAXDOP set the limit of tasks per parallel branch, 8 in this case. Because each task is 1:1 with a worker, and each worker must be scheduled in a single scheduler to perform some computing work – the MAXDOP configuration/hint effectively limits the number of CPUs used in the query, just not directly as commonly perceived.
The fact the child tasks are on the same exact schedulers is by-design: once the first set of 8 parallel tasks on a branch was scheduled, every additional task for any branch will use that same schedulers.
The coordinating task can be scheduled on any NUMA node, even on a node where no threads were reserved. In this case it was on scheduler 3, which is in NUMA node 0.
If this were a normal server, where multiple requests are running, I'd likely not notice that a given parallel request is using 9 schedulers instead of 8. Because the scheduler associated with a coordinating task (through its worker) for a request 1 would be a scheduler used for parallel task execution in a request 2 for example. But the extra scheduler for request 1 is there, outside the scope of MAXDOP.
A picture is usually worth a thousand words so here it is, my SQL Server conceptually displaying several executions of the example query:
- Request 1 uses schedulers 0 through 7 for parallel task execution, and scheduler 8 for the coordinating task, which is also used by Request 2 for parallel task execution. Thread reservation was done in NUMA node 0 only, so all schedulers used for parallel tasks are in that node.
- Request 2 uses schedulers 8 through 15 for parallel task execution, and scheduler 16 for the coordinating task, which is also used by Request 3 for parallel task execution. Thread reservation was done in NUMA nodes 0 and 1, so some parallel tasks are executed in node 0 (schedulers 8 through 11) and some on node 1 (schedulers 12 through 15).
- Request 3 uses schedulers 16 through 23 for parallel task execution, and scheduler 15 for the coordinating task, which is also used by Request 2 for parallel task execution. Thread reservation was done in NUMA node 1 only, so all schedulers used for parallel tasks are in that node.
In this scenario, schedulers 8, 15, and 16 are used by a coordinating task from one request, and a parallel task from another request. If there were no bottlenecks with a coordinating task (which can do other work, but for simplicity, let's leave that aside now), then the observation is: "3 requests are using 8 schedulers each (the MAXDOP number), so MAXDOP is controlling schedulers per request." ☺
Note: In practice, we've seen the coordinating task doesn't have to be scheduled on the same NUMA node. And while the number of schedulers used for the parallel tasks within a given NUMA node is enforced, their placement is only contiguous if schedulers are available inside a given NUMA node (like in the picture), but the concept remains.
So, we’ve seen how a single request can spawn multiple tasks up to the limit set by reserved worker threads – 16 in this case – plus one extra coordinating task. The threads reserved per branch are limited by MAXDOP – 8 in our example.
Because each task must be assigned to a worker thread for execution, each worker to a scheduler, and all the schedulers assigned to parallel task execution are from the same pool, hence the perception that the MAXDOP limit is done at the request level (the query), but we've seen it's really not. And we established the coordinating thread is not part of the calculation where MAXDOP is used, so in the example above, with MAXDOP 8, effectively 9 schedulers were in use, not just 8 (so MAXDOP + 1).
About the perception of MAXDOP controlling schedulers per query (request), we can call it observer bias. The practical effect with multiple requests in-flight is that I see all schedulers being used, and the coordinating task may not be noticeable for the overwhelming majority of cases.
But to be accurate, we now know the MAXDOP limit is enforced at the task level, per branch, not at the request level, and that for parallel requests there will always be an extra task and thread needed, and therefore an extra scheduler in use.
EDIT (7/13/2020): Does this mean that MAXDOP configuration recommendations change? No. The coordinating task/thread is not very busy for the overwhelming majority of workloads, and while it is useful to clarify the scope of MAXDOP, you shouldn't be going out to do any changes to your SQL Servers for the sake of doing changes.
If you want to know more, refer to the SQL Server task scheduling section in the Thread and Task Architecture Guide.
Pedro Lopes ( @SQLPedro ) – Principal Program Manager
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.