SQL Server Memory Grants: Exposing the Silent Performance Assassin

Part 1 Memory Grant Introduction

If you’ve been dealing with performance issues, you’ve probably focused on logical reads,
duration, CPU usage, and other metrics. However, with the latest versions of SQL Server 2019 and
2022, it’s time to pay more attention to memory grants. These are the silent assassins that
can easily be overlooked.

In my upcoming blog posts, I’m diving deep into memory grants to show just how crucial they are. This post is just the beginning, so you can follow along easily—I could practically write a whole book about this topic!

What is a Memory Grant?

Think of a memory grant as a special allocation of server memory for demanding tasks like
hashing and sorting. When you’re working with complex queries, these operations need extra
memory to temporarily store row data while sorting and joining. When a query runs, the
optimizer estimates the required memory and creates an execution plan. The memory grant is
based on the estimated rows and the operators used, such as sort, hash join, or hash aggregate.
Memory grants can lead to performance issues if not managed properly.

Someone came to me with questions: “What does that mean? What exactly do the operators do? How do they function? And how can I recognize them beyond just looking at the plan?” Don’t worry—I’ve got the answers covered for you!

Sort and Hash Operators

Both sort and hash operators perform stop-and-go blocking operations, meaning they must
consume all their input before they can produce any rows.

Sort Operator

The sort operator is used when data needs to be ordered. To perform a sort operation, memory
allocation is required, and the query must receive a memory grant. This grant allocates memory
based on the size of the sort operation.

Why does a sort operation occur?

A sort operator is necessary when using clauses like GROUP BY, ORDER BY, or DISTINCT, as these require the data to be sorted.
A MERGE JOIN in your SQL plan can also trigger a sort operation.

Hash Operator

A hash operator helps speed up data searches by converting data into hash values and storing
matching rows in memory. It’s especially useful for dealing with larger tables that aren’t sorted
using an index.

How Hash Joins Work

Hashing: First, the data gets converted into hash values.
Build Input: The operator creates a hash table in memory, storing these hash values
along with their corresponding rows of data.
Probe Input: A second table is then read and compared to the hash table, returning all
matching values.

When Do You Encounter a Hash Operator?

You’ll come across a hash operator during
operations like:

Distinct: When you need to remove duplicate rows.
Union: When combining results from multiple queries and removing duplicates.
Aggregate Functions: Such as MAX, AVG, SUM, and MIN.
Joins: Especially when the input tables aren’t sorted according to the join column.

Things to Watch Out For

Performance Issues: If the build input is much larger than expected, it can lead to insufficient memory grants and cause data to spill over to the TEMPDB, slowing things down.
Causes for Hash Match: A hash match might occur if there is a missing index or if a WHERE clause is non-SARGable, preventing the use of an existing index.

Why Can Memory Grants Cause Performance Issues?

Memory grants can impact performance in significant ways based on their estimation:

Underestimating Memory Grants: If the memory grant is underestimated, it can result in more data processing spilling over to disk, which slows down performance.
Overestimating Memory Grants: If the memory grant is overestimated, it can lead to situations where other queries have to wait due to excessive memory allocation, causing delays and impacting overall system responsiveness.

Here’s a REAL LIFE example of an oversized memory grant (real query names changed):

You’ll notice that only three calls resulted in 357,996 logical reads. Any developer would be
thrilled with these outcomes, right?

Well, what if I told you that those three queries have a total granted memory of 46 GB? And the Total ideal memory would be 54 TB?

My husband’s first reaction was, “Where the heck is Jango Fett? Your fans will be disappointed,” and “No server would need that much memory for small queries.”

Summary:

In SQL Server, performance isn’t just about reads and CPU—it’s also about memory grants. These allocations of server memory are crucial for tasks like sorting and hashing, which are essential for complex queries.

Memory grants are allocated based on the data size and operations needed, like sorting or joining data. Mismanaging memory grants can lead to performance bottlenecks—underestimation may cause data spills, while overestimation can result in resource contention and delays.

But Amy, where is the query that caused the memory grant? How do I solve them? What other things do I look for? Don’t worry, this is just the beginning. I want my followers to be fully informed of memory grants. This is my way of peeling the onion and not overloading your brain.

Stay tuned for more insights into how memory grants impact SQL Server performance with examples. This scenario has become increasingly common with SQL Server 2022, and if you know me, you know how passionate I am about it.

Other good sources to learn about operators:

This is a good book written by Grant Fritchey that goes over operators and execution plans, I highly recommend it if this stuff interests you. Most of my information and knowledge comes from his books.

AAA-DBA.com