Non-blocking operators (also known as iterative or row-by-row operators) allow rows to pass through immediately after being processed, without waiting for the entire set of data to be gathered. These operators contribute to a pipelined execution of a query, enabling faster processing, especially on large datasets, since downstream operators can begin processing rows as soon as they become available.
Characteristics of Non-Blocking Operators
Ø Row-By-Row Processing: Non-blocking operators process one row at a time, allowing the flow of data to continue without delay.
Ø Low Memory Usage: They typically consume minimal memory since they don’t need to store or wait for large datasets.
Ø Efficient for Large Datasets: Ideal for large queries where streaming data without delay is beneficial.
Examples of Non-Blocking Operators in SQL Server
Some of the most commonly seen
non-blocking operators in SQL Server include:
Ø Nested Loops Join: This operator processes rows from the outer table and immediately matches them to rows in the inner table, one pair at a time.
Ø Index Seek: Returns rows based on an index condition and is non-blocking as it retrieves rows iteratively.
Ø Table/Index Scan: Reads rows sequentially from a table or index and passes them downstream as they are read, without waiting for all rows to be scanned.
Ø Compute Scalar: Computes a scalar value (e.g., a simple calculation or expression evaluation) for each row and immediately passes it along.
