Query memory grants part 1: Where does the memory go?

Let’s talk about how queries use memory, specifically in the execution plan. One of the query operators that use memory is sorting. To emphasize this, I’ll write a query with an order by clause, and look at the execution plan.

Query that will request a memory grant

Using StackOverflow2010 (Thanks to Brent Ozar and the Stack Overflow team for their data dump):

SELECT Id, Type
FROM dbo.PostTypes
ORDER BY Type

Here’s the results if you’re curious.

We want to look at the execution plan.

First, let’s look at the properties of the SELECT operator.

This is the memory grant for the entire query. Since we know that the Sort operator uses memory, let’s take a look at that operator and confirm that it used memory.

Where does the memory grant go?

We can confirm that the sort operator was requesting memory by looking at the operator’s memory fractions.

It gets harder from here. The execution plan is very useful for determining where the memory grants are coming, but once there’s multiple operators requesting memory grants, it gets much more complex.

For example, memory grants can be reused between operators in execution plans (source).

I want to continue this series and talk more about query memory usage. For those posts, please stay tuned!