26 performance tuning questions and solutions, A list of Blocking/Non Blocking Iterators - SQL Server Q&A, blocking operators in sql server, Blocking/non-blocking aggregate operators, How to tune SQL queries, INF: Understanding and resolving SQL Server blocking, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, msbi skills, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Properties of Iterators, Query Optimization, Query Performance Tuning, Rob Farley : The blocking nature of aggregates, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, sql server blocking operators, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL Tuning Overview, The blocking nature of aggregates, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases
Execution Plan Operator – Properties, Types – Blocking / Non-Blocking?
In this post, I’ll discuss properties of iterators and non-blocking vs. blocking. Note that these things can affect performance of the query.
SQL Server has two kinds of execution plan operators. They are
• Non-blocking ( Stop-and-go )
Non-blocking iterator get rows in and sends rows out at the same time (in the GetRow method).
For instance, the Nested Loops iterator gets rows from its outer input and sends the matching rows on to the next iterator without having to wait for all of the input rows.
Compute scalar iterator is also an example of a non-blocking iterator. It read an input row, computes a new output value using the input values from the current row, immediately outputs the new value, and continues to the next input row.
Blocking iterator – Consume all of the input rows (in the open method) before it can send rows out to the next iterator.
For instance sort is a blocking operator. It has to consume all of the input rows and sort them before it can send them on to the next iterator.
Blocking operators may or may not consume memory. It is also possible that iterators have phases and one phase is blocking and another one is non-blocking. Example of one such iterator is Hash Join. In this iterator build phase is blocking and probe is pipelined.
A blocking operator may lead to concurrency issues and reduce performance. Non-blocking iterators are good for OLTP systems.
NOTE – Microsoft has not released a definitive list about blocking and non-blocking operators. You can mostly check which of the operators within a plan are blocking or not by reading their descriptions.
Also note that all iterators require some amount of memory to perform calculations and state management. Every query will have to acquire some memory to execute the query. This is called Memory Grant. You can view this memory grant in the first operator that Select, Update or Insert. Now if the server is executing other queries and does not have enough memory to grant, our query will have to wait until the grant is available. I will explain Memory grant in detail in coming up articles are this is a very big topic.
Now if the estimates are bad or we have not conveyed correct information to the optimizer and memory consuming operator requests a small amount of memory it will lead to spill data to disk during execution. Spills are not good for query performance due to extra I/O overhead.
Major memory consuming iterators are Sort, hash join and hash aggregate. Sort spills are very famous. We can track this using warning in the execution plan or using Sort Warnings in SQL profiler.
So all in all we have try to remove the blocking and memory consuming operators for OLTP systems to get good query performances.
That’s all folks; I hope you’ve enjoyed learning about operators and their types, and I’ll see you soon with more “Performance Tuning” articles.
Pawan Kumar Khowal