Properties of Iterators

In this post I will continue the discussion about iterators in the context of a query execution plan. This time, I will tell you about some of their most important properties and the things to take into consideration when investigating the performance of an execution plan.

First, we are going to talk about the memory consumed by operators. This is a very important property and you should take it into consideration when troubleshooting query plan performance.
Second, we will discuss about operators that are blocking and ones that are non-blocking. You fill find out soon what this means.
And last, I will tell you about dynamic cursors and dynamic cursor support.

There are other properties of iterators that are important, but for the moment, these three are enough for a discussion.

So let’s start with the first one on the list.

Memory consumption

The first property we’re going to talk about is memory consumption.
In order to do their job correctly, the iterators need to access a small amount of memory. This is used to store their state, perform calculations and so on.
SQL Server doesn’t reserve memory for an operator beforehand, but allocates it when the executable plan is cached. Like all things in life, this approach has its advantages and disadvantages.

The downside is that some iterators require more memory than this small amount. They need it to store row data and the needed memory is usually proportional to the number of rows that get into the operator.
These iterators should be inspected, because they can affect performance in several ways:

  1. Sometimes the memory is not yet available and because an operator needs to wait for memory to free up, the entire query needs to wait. As you can figure out, waiting is not a good thing for a query’s performance.
  2. If we have more queries that need to wait for memory,  chances are that the server will suffer from reduced concurrency and/or throughput. Again, this is not something that we want in our OLTP system.
    PS: This is not a major issue, however, in data warehouses.
  3. And last, but not least, we may encounter spills to disk if the operator requested only a small amount of memory when it needs more. Spilling is bad because the I/O operations are never as fast as the ones performed in memory. So we can witness again a performance downgrade. And also, if an operator spills too much data, it can run out of disk space on tempdb and cause the entire query to fail.

When we talk about memory-consuming iterators, the first that should come to mind are:

  • Sort
  • Hash join
  • Hash aggregation

Nonblocking vs. blocking iterators

The next important property is the capability of an operator to consume and produce rows in the same time.
There are operators that are able to produce output rows as soon as they receive the input rows. We call them non blocking operators.

And there are iterators that need access to all input rows before being able to produce output. These are blocking or “stop-and-go” operators.

To better understand this, let’s use the power of example:
I am going to create a test table called Products, that has two columns: an identity column (Id) and a string column, to store the products’ names. First, I check if the table exists in my database and I drop it if exists.

Notice the syntax I’m using, ( DROP TABLE IF EXISTS ), which is SQL Server 2016 specific. Find more information here.
I will insert three rows in this table, which are not sorted in alphabetical order: Cherry, Potato and Banana.

If I select everything from the table, this is how the plan looks like.

Properties of iterators - SELECT all

But if I want to get my rows in alphabetical order (and I add an ORDER BY clause), my plan has a new operator.

Properties of iterators - SELECT with order by

The “Sort” operator is a good example of a blocking one and I hope it is pretty clear why: in order to make sure that the output rows produced by it are correctly ordered, it needs to read all the rows before performing the sorting algorithm. Otherwise, it wouldn’t be possible to do the correct ordering of rows.

Another important blocking operator is Hash Join. I will talk about it in a separate post, a little later.
So basically the examples provided at the memory consuming operators also apply here:

  • Sort
  • Hash join
  • Hash aggregation

But there are also examples are blocking, but not memory consuming. So they cannot be included in both categories. For example, the COUNT(*) is blocking, because it needs access to all rows in order count how many they are, but doesn’t need extra memory for this.


Dynamic cursor support

The last property we’re going to talk about is support for dynamic cursors.
First let’s see what dynamic cursors are.

MSDN tells us that:
“Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates made outside the cursor are not visible until they are committed, unless the cursor transaction isolation level is set to read uncommitted.”

So because these dynamic cursors have a special behavior, the iterators used in a dynamic cursor also have special properties. So they are able to:

  1. Save and restore their state
  2. Scan forward or backward
  3. Process one input row for each output row (they need to be nonblocking).

In order for a query to be executed using a dynamic cursor, the optimizer must be able to find a query plan that uses only iterators that support dynamic cursors. Sometimes, this is not possible. This means that some queries can’t be executed using a dynamic cursor.
For example, queries that include a GROUP BY clause cannot be executed using a dynamic cursor, because a blocking iterator is needed for the GROUP BY clause.

Ok, so these were the most important properties of the query plan iterators and the ones we should keep in mind when we start the performance troubleshooting.
Follow the next clips to find out the several ways to read query plans. After that, you will be ready to start analyzing the plans and learn about the different operators. And you will be closer to achieving your goal of understanding the query execution plan.

If you have comments related to this article, please leave them in the comments section below and I will happily answer you.

Leave a Reply

Your email address will not be published. Required fields are marked *

six + nine =