1

I am looking at an execution plan and reading it from right to left.

Example:

1<-2<-3<-4
      ^
      |_5

So 4 and 5 execute in parallel. When both are fully done, then 3 executes in its entirely, then 2 run fully, then 1.

Is this correct or can there be case where:

  1. 3 starts execution even before 4 and 5 fully complete.

  2. 2 starts execution even before 3 fully completes.

3
  • 1
    I think you mean operator rather than member. In that case, your question may be a duplicate of this answer, which explains more about blocking versus non-blocking operators.
    – Dan Guzman
    Sep 9, 2021 at 10:29
  • Please share an execution plan via brentozar.com/pastetheplan. It's much easier to describe against a real plan Sep 9, 2021 at 11:46
  • Try running a longer query with "Include Live Query Statistics" and you will see which steps are executed in parallel and which are not. Sep 10, 2021 at 4:07

1 Answer 1

6

You are misunderstanding what these arrows mean. They do not represent the sequence of execution, but the flow of data.

Each operator does not execute in its entirety, then pass all rows to the next operator, unless it is a blocking operator. Nor do they necessarily mean that they execute in parallel.

Instead, each operator passes one row at a time to the next operator. The joining operator 3 will wait for a row from 4 before calling 5 for its first row. This is called an iterator.

So if 4 is a blocking operator (for example a sort) then it will generate all rows internally, before passing them one by one to 3, which in turn will request rows from 5. If 5 is also blocking then it will have to wait.

But most operators are not blocking, and therefore normally each row passes through one at a time. A row is fetched from 4 by the join at 3, then a row is fetched from 5 and joined, more rows are fetched from 5 if relevant, then the process begins again with another row from 4.


Operators 4 and 5 will only execute in parallel if this is a parallel plan.

In a parallel plan, the data is partitioned, and each thread works on part of the data, in a similar fashion to a serial plan.

2
  • Operators 4 and 5 will only execute in parallel if this is a parallel plan. - My understanding is that parallel plan means multiple parallel threads working on the SINGLE operator. Does Operators 4 and 5 will only execute in parallel if this is a parallel plan. also require parallel plan?
    – variable
    Jun 2 at 8:49
  • Yes only with a parallel plan. What happens (usually) is that the date going into 3 from 4 will be partitioned, so 3 is simultaneously working on multiple sections of it. For each of those sections it is simultaneously requesting data from 4. So one thread could be requesting data from 4 while another thread is requesting data from 5. Equally 3 and 5 or 3 and 4 could be executing at the same time Jun 2 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.