Blocking vs Non Blocking operators

July 25, 2017 Execution Plans 1904 Views

/_include/blog-images/article-images/execplans-128.png

Some operators stop the execution while they are executing while some others pass out pieces of data as they are execution. Some operators behave either blocking or non blocking depending on the situation.


From the execution perspective, Execution Plan operators fall in two categories:

  • Blocking operators
  • Non blocking operators


Blocking operators

Some of the operators in Execution Plans are blocking. They need to receive all input data first, then perform the logic they are meant for, and finally they start outputing results when the logic is all applied.

A good example of a blocking operator is the SORT operator. It needs to gather all the rows first so that it can sort data propperly. Once all data is processed, the sorted results can be output.

Blocking operators are also called Stop-and-Go operators.


Non-Blocking operators

Unlike blocking operators, non-blocking operators start outputing results while the logic is being applied. A good example of that is the Compute Scalar operator. This operators appears when you have a formula in you query. The Compute Scalar operator picks up a row, applies the formula and outputs.

Not all operators are non-blocking; it depends on the logic. One operator might be blocking in one execution plan, and the same operator happens to be nonblocking in another execution plan.

How do you rate this topic?


Further reading:
How to read Execution Plans
What does arrow thickness mean in Execution Plans?
What is an Execution Plan ?
Actual Execution Plan hides some queries!!!
Some SQL functions are constants