or
worldstar sql imported from SE
sql-server
I work with a really old DBA who says a lot of weird stuff. Dude has an O'Reilly book that only has an amoeba on the cover.

At lunch we were talking about parallelism, because our new server has 24 cores. He says that in a parallel plan, every operator gets DOP threads. So if you have MAXDOP 8 and your query has 4 parallel operators it'll use 32 threads at once.

That doesn't seem right because you'd run out of threads really fast. 

I also read that it might just be 8 for the whole query, which seems like too few.

[Why Do I see more threads per SPID in sysprocesses than MAXDOP?][1]

Are either of them right? 


  [1]: https://blogs.msdn.microsoft.com/sqlserverfaq/2009/01/05/why-do-i-see-more-threads-per-spid-in-sysprocesses-than-maxdop/
Top Answer
Paul White
>He says that in a parallel plan, every operator gets DOP threads.

**No. This is at best misleading, but closer to being simply wrong.**

In a serial plan, every operator 'gets' one thread, but that thread is the *same thread* for all operators. The principle is similar for parallel plans.

Each parallel operator is *run by* `DOP` threads, but those threads are *not exclusive* to a particular operator, they are *shared* among operators within the same **parallel branch**.

Branch boundaries are delimited by Parallelism operators (Demand, Repartition, and Gather Streams). The diagram below shows a parallel plan with three branches:

[![enter image description here][1]][1]  
*Reproduced from the article referenced at the end of this answer*

---

>So if you have `MAXDOP 8` and your query has 4 parallel operators it'll use 32 threads at once.

**No**. You can't just multiply `DOP` by the number of operators to get the number of threads. The number of threads reserved for parallel branches is the *number of parallel branches* (not operators) multiplied by `DOP`.

The number of threads that can be active at the same time for a single parallel query is limited to `DOP` in [SQL Server 2005 and later][2]. SQL Server achieves this by allocating threads to `DOP` schedulers.

---

>I also read that it might just be 8 for the whole query, which seems like too few.

At `DOP = 8` this would be correct for a plan with a single parallel branch. There can be multiple parallel plan branches in a parallel plan. For a plan with `n` parallel branches, the thread reservation for parallel workers is `n * DOP`.

See [Parallel Execution Plans – Branches and Threads](https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads) by Paul White.

Note: The number of branches reported in execution plans is normally the number of branches that may possibly execute concurrently (due to blocking operators, threads in one branch may sometimes be safely recycled for a later branch).

  [1]: https://i.stack.imgur.com/05MZo.png
  [2]: https://dba.stackexchange.com/q/205643/150519
In SQL Server, is parallelism per operator, or something else?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.