On a dop 8 query why would there be a exec_context_id = 9

GokhanVarol 2013-07-03 19:26:47

alt textI am monitoring some parallel queries and noticing some of them maxed out at dop = exec_context_id and few have exec_context_id > dop. What is the reasoning behind this, why would there be higher exec_context_id compared to the dop.
The attachment is also a script file with represent a join results of system tables during the execution and the estimated query which was executed.
Thank you

link text

GokhanVarol 2013-07-03 20:25:17
Seems to me like when the driver source is parallel demand partitioning it's adding another exec_context_id?
GokhanVarol 2013-07-03 22:22:23
Opps, I noticed other queries having exec_context_id = 17 even more with dop = 8. I guess the question needs to be what determines the exec_context_id?
SQLkiwi 2013-07-04 00:21:03
The are DOP threads per parallel zone (a.k.a branch) in the plan. Each thread runs in an execution context.

Branches are delimited by exchange (parallelism) operators. See my parallelism article for more information on branches and execution contexts.

In the plan shown, there are 8 threads running the operators between the Distribute Streams and Gather Streams exchanges. There is another thread (thread 0, a.k.a coordinator, always ecid 0) running the serial branch of the plan to the left of the Gather Streams. There is a final thread running the serial branch of the plan to the right of the Distribute Streams. 8 + 1 + 1 = 10 threads, and 10 execution contexts, as shown in your graphic.

GokhanVarol 2013-07-04 00:37:12
Switch between serial and parallel creates dop many exec_context_id and switch between parallel and serial zone create one additional exec_context_id and if the starting point was a parallel read then exec_context_id would be equal to dop to start with just to be aggregated on later kind of?
GokhanVarol 2013-07-04 01:00:16
Thank you, now I understood Parallel Page Supplier and execution context, awesome documentation. There is more to grab from each and every time I visit an article of SQLwiki 🙂
GokhanVarol 2013-07-04 04:44:46
2 queries, one the driver is a constant scan single thread and there is a repartition following, that ends up having 10 execution context(1+8+1), The other query starts reading from a driver table with a parallel read and it has 9 execution (8+1), both running on dop = 8. Does query #2 have an advantage over query 1 since there are fewer zones or it does not matter?
SQLkiwi 2013-07-04 05:22:06
In that case it does not really matter. In other cases, where the number of zones and threads gets very large, it can.