There are logical and physical operators in the SQL Server query plan. When the SQL Server starts processing a query, first, Query Optimizer creates a tree of logical operators. Then it matches logical operators with physical ones. Physical operators can be of two types: blocking and non-blocking. Blocking operators collect all the rows from the input before they can release the first row to output. Non-blocking operators process data on a row-by-row basis. This post is about these two types of physical operators. I will explain why do we need to pay special attention to blocking operators during performance optimization.

I am using the ‘AdventureWorks2019’ database, SQL Server 2019, and compatibility level 150 for my examples here.

For a high-level picture, I need to start from that any query needs memory for a compilation stage, then it takes some memory to cache a query plan and, finally, the query will need memory during executing stage. In this post, we’ll look deeper into the query execution stage memory consumption.

Let’s start with a simple query:

SELECT p.FirstName 
FROM Person.Person AS p
ORDER BY p.FirstName

The query actual execution plan:

It is pretty simple: a table scan and a sort. If we look into operators’ properties and check memory for ‘clustered index scan’ we don’t see any memory consumption statistics:

The ‘Sort’ operator does consume memory:

For the query itself (SELECT operator):

These numbers are in kilobytes. In the last two screenshots, we can see some similarities since only one operator in the query needs memory (‘Sort’ is a blocking operator). About the last screenshot. Desired memory means how much memory SQL Server ‘wanted’ for the query regardless of available memory. Granted memory is the amount of memory the query got. Max used memory is quite self-explanatory. The number is the same in the Sort operator used memory because this is the only operator that consumed the memory.

My VM has a low workload and hence could provide all the memory that the query needed (to be honest query doesn’t require too much either 🙂 ). That’s why in this case DesiredMemory = GrantedMemory.

Let’s check what happens if the amount of memory is not enough. For this, I’ll use a query hint. In this case, the SQL engine reduces the amount of memory granted for the query based on the query hint parameters.

SELECT p.FirstName 
FROM Person.Person AS p
ORDER BY p.FirstName  OPTION (min_grant_percent = 1, max_grant_percent = 1 ) 

Here is the actual execution plan for the query.

The yellow sign on the Sort operator tells us that something is wrong. If I hover a mouse over the operator I see this picture:

Under the “Warnings” is what we need! The operator hadn’t enough memory for execution and it used tempdb. Of course, usage of tempdb makes the query slower.

Two things we need to take from here. First, some operators need memory in the execution stage. Second, if there is not enough memory it makes them slower.

Now, I change the database schema a little bit and check how it influences memory usage.

ALTER TABLE person.person ALTER COLUMN FirstName NVARCHAR(100);

The column’s size increased from nvarchar(50) to nvarchar(100). Execute the same query, but without the query hint this time, because we need to check how the memory grant changes.

SELECT p.FirstName 
FROM Person.Person AS p
ORDER BY p.FirstName

A query plan is the same, what we are interested more in is how memory usage changed. Here is the memory usage for the query.

This is interesting! Desired memory increased from 4464 to 6416. Now the Sort operator ‘wants’ ~44% more memory than before. If we increase it twice again it needs 65% more than the original amount. The demand for memory is not a linear function of column size!

Now we’ll check how it changes if the column’s data type is fixed length.

First, we change the datatype to NCHAR and clear the cache to force SQL to create a new execution plan.

ALTER TABLE person.person ALTER COLUMN FirstName NCHAR(50);
GO
DBCC FREEPROCCACHE;
GO

Execute the same script:

SELECT p.FirstName 
FROM Person.Person AS p
ORDER BY p.FirstName

Memory grant:

If we check the memory grant with increased column size, we’ll see that memory is also increasing. To make it simpler to compare I put everything into one table.

Column’s datatype	Memory grant (used memory), KB
nchar(50),nvarchar(50)	6256(2888), 4464(2976)
nchar(100),nvarchar(100)	7264(4888),6416(2976)
nchar(200),nvarchar(200)	12728(9200),7376(2976)

What we see here.

• Fixed-length datatype uses more memory than variable-length columns. So if data for the column is not fixed length it is better not to use (n)char.
• Increasing datatype alone increases memory grant even for variable-length datatypes and Intelligent query processing doesn’t help here. It is better to use a datatype as small as it is reasonably possible.

Conclusion

• There are two types of physical operators: blocking and non-blocking. We need to pay attention to blocking operators since they need memory during their execution and can cause performance issues. Blocking operators are the ones that perform ‘sort’ and ‘hash match’ operations. Inserts to a ‘columnstore’ index also need memory.
• SQL can grant more memory than needed or less memory than needed. It depends on many parameters: available memory, statistics, available indexes, datatypes and many others.
• Too much memory and too little memory granted for the query are bad. In the first case, we are wasting recourses, in the second it leads to performance issues because the query needs to use tempdb.
• It is essential to use proper data types in a database design stage.
• To fix issues connected with the memory grant, a query plan needs to be used to find a root cause. Some simple solutions can be: update statistics, add indexes, or rewrite queries. In more complex cases Resource Governor or query hints can be used.

Of course, this is just the tip of the iceberg of SQL Server operators and memory consumption. For more information please read docs online or my next posts. 🙂

Hope it helps! See you in the next post!