patternsqlModerate
In SQL Server, is parallelism per operator, or something else?
Viewed 0 times
operatorpersqlelseserverparallelismsomething
Problem
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?
Are either of them right?
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?
Are either of them right?
Solution
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
Branch boundaries are delimited by Parallelism operators (Demand, Repartition, and Gather Streams). The diagram below shows a parallel plan with three branches:
Reproduced from the article referenced at the end of this answer
So if you have
No. You can't just multiply
The number of threads that can be active at the same time for a single parallel query is limited to
I also read that it might just be 8 for the whole query, which seems like too few.
At
See Parallel Execution Plans – Branches and 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).
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:
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. 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 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).
Context
StackExchange Database Administrators Q#205768, answer score: 17
Revisions (0)
No revisions yet.