Tag: memory grant

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!

Changing max memory: be careful!

If you’re increasing your server’s memory, simultaneously adding more max memory for SQL always seems like a good thing. Let’s talk about what changes when you change the setting for max memory. For the basics, start here.

In SQL Server, increasing max memory will give more memory for data stored in buffer and all sorts of other good things. There is one bad side: your queries that request memory grants may change how much memory they request.

Hopefully your servers have more memory than my blog/presentation laptop. This demo scales, so don’t worry about the small size in my test.

Starting position: max memory at 3 GB

We need a query that wants a memory grant, so I’ll use a query with a sort that returns a lot of rows.

SELECT Tags, Title
FROM Posts
WHERE PostTypeId = 1
ORDER BY Title

Here’s the memory grant from the actual execution plan’s properties. The query wants 615,264 KB of memory, but it was only granted 548,256.

More power! What happens with increased SQL Server max memory?

Same query but with more max memory. In this case, the execution plan stayed the same but the memory grant increased.

This time, all the desired memory was granted. With the lower max memory setting, the query requested 548,256 and now it requests 615,264.

When changing max memory, test

I wanted to write this post to prove that changing SQL Server max memory has an impact on your query’s performance. For this query, a higher memory grant could be a good thing.

If you’re changing SQL Server settings, testing the impact on a test system is recommended.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close