Important concept today:
Sometimes when we run a query with MAXDOP setting (query hint) as 1, we see that multiple threads are used to execute the query. Why is it?
Short Answer:
MAXDOP query hint does not indicate the number of threads (or parallelism) for the entire query; It only indicates the number of threads for each operator in the query execution plan. So if there are multiple operators, then each operator could potentially get a dedicated thread, resulting in more threads (parallelism) running the query.
Details:
This is a popular misconception. Though Microsoft has documented the underlying behavior, this myth seems to persist.
Sql Server optimizer is very good at coming up with most optimal plan for executing any given query. The better artifacts it has about the data (stats, etc), the more efficient the execution plan. It makes several calculations, about the best way to perform the execution plan. Where needed, it uses parallelism to carry out larger workloads more efficiently.
We have options to specify some query hits to suggest our execution preferences to the Optimizer. While it honors our suggestions, the final say would be based on its own internal calculations.
MAXDOP is one such query hint.
The general understanding is that when we specify MAXDOP to a higher number, Sql Server uses that many worker threads to complete the workload. On the surface it seems correct, but there are some nuances.
Question: If we specify MAXDOP = 1, does Sql Server run the query with just one thread?
Answer: It depends.
For some queries with larger workloads, if the query execution plan has multiple operators (clustered-index-scan, index-seek, nested-loops, etc), then each Operator will get its own thread, even though MAXDOP = 1. Resulting in total, more threads than 1 for query execution.
If MAXDOP=2, then each Operator could get up to 2 threads. Resulting in total, more threads than 2, for query execution.
Conclusion:
MAXDOP setting is used to limit the number of threads per Operator in a query execution plan. It does not indicate the total number of threads used for the entire query.
Suggestions:
To prevent such events, see if MAXDOP setting could be reduced or add any helpful indexes to minimize the time/effort needed to process the query data.
Note: To illustrate this scenario, the sample table code in this post will help quickly create a large table with many records.
Important Note 1:
Please note that the above comments are an explanation to the scenario when Sql Server seems to utilize more threads than MAXDOP setting. This does not mean, it will happen every single time.
It all depends on Optimizer & Execution plan. If Sql Server decides that it is more efficient to carry out the workload with a single thread, then that’s what we’ll see in the sys.processes.
Hope this helps,
_Sqltimes
Playing with database servers... 