Is there a list of all blocking statements? The only clear way I know to validate an operator is by locking to check its properties and see if the data is sorted. Is this the only way to check if an operator is blocking? IF not, what are other ways to verify that the operator is blocking?
The lock operator will need to process all the lines before transferring data, while the non-blocking operator will pass the lines to other operators and soon after reading and processing them.
below - definition of blocking vs without blocking
Iterators that consume input strings and produce output strings at the same time (in GetRow). We often call these iterators "non-blocking."
Iterators that consume all input strings (usually in the Open method) before producing any output strings. We call these iterators "lock" or "stop and move."
WHY I NEED THIS INFORMATION Mostly curiosity and personal knowledge. I am not doing anything crazy with him. Just read about blocking against non-blocking statements and would like to know which statement does.
It is easy for SSMS request plans
gh9
source share