The SQL Server Performance Tasting Menu: How Memory Grants Work

Posted on by Erik Darling

Grunt Life

Video Summary

In this video, I delve into the intricacies of memory grants in SQL Server, particularly focusing on how they scale based on the complexity and nature of your queries. I demonstrate through a series of examples how sorting operations can significantly impact memory allocation, showing that the size of a memory grant isn’t always directly proportional to the number of sorts or columns involved. By running various queries and analyzing their execution plans, I illustrate how SQL Server allocates memory for different parts of a query plan, emphasizing the importance of understanding these mechanisms to optimize performance effectively.

Full Transcript

Well, I’ve been alerted to the fact that it is nearly 6 o’clock Eastern and that my wine was getting lonely. So it is that time of the day, my people, where I start to put wine in my face. And video quality remains at a consistent low. Won’t be able to tell the difference, I promise. It’ll be seamless. Except for the gentleman yelling out in front of my apartment about I don’t know what.

Anyway, we’ve talked a couple times about memory grants in the context of sorts and like how big they get. for different things. And now I want to record this video to go a little bit more in depth on that so that you can get a slightly better idea of just how big a memory grant can get based on the rows and columns that you are selecting. So, we are armed, we are locked and loaded with query plans turned on. And I realize that this query is written in sort of a funny way.

But it will make more sense when I need to do more things to help you understand how memory is apportioned or something in a query plan. So, let’s run this. We got query plans on. I’m going to run this query and I’m going to get the top thousand rows from the users table ordered by reputation. And since I do not have an index, I do not have my very helpful index on the users table that puts reputation in order, I need to sort my data.

There it is, that sort operator. And because I need to sort data, I need to ask for memory to do that. And I asked for just about 166 megs of memory to get that. You can see that memory grant right there, 166528. Cool. Now, keep in mind that 166 megs of memory is only for a thousand rows of just an integer column. Okay? Cool.

So, 166 megs for that. Let’s look at a situation where I need to sort two sets of data. Now, you might think that because I’m going to have two sorts in this query plan, that I’m going to ask for 166 times 2. But I don’t. I ask for 167.872. Now, you might ask yourself why that is. Well, good question.

The way that this query is optimized, I get a hash join. We talked about this in the sortables video, where hash joins and sorts are blocking operators, not to other queries, but internally, all the rows have to show up in order for them to start running. Because rows stop at that hash join while the hash table is built, those two sort operators don’t execute concurrently.

So, excuse me, we’re able to take memory that gets sent to the sort operator. We can go into the ever useful properties and look at memory fractions, and we can see… So, one is basically 100 for our conversation.

We can see that it absorbed 100% of the memory grant, and it released nearly all of it. The hash match is going to use a little bit of the memory grant as well, but then the other sort is going to take this number, and it’s also going to release that number. So, over here we have this other sort sharing the memory grant.

Where this doesn’t work is when we have non-blocking operators, or stop-and-go operators, as they’re sometimes called in the query plan. So, I’m going to change the way this is written. I’m going to force a loop join here. And when I run this query, the speed makes… the speed is not the thing here.

What I want to show you is that now we ask for just about double. So, what is about 166 times 2? We ask for 333 megs of memory. And this sort and this sort both got independent memory grants.

So, if we look at the properties here, the memory fractions output, this one got half, and this one also got half. So, what does that mean? It means we got 333 megs, about 166 one here, and about 166 one here.

Cool. Great. Wonderful. Fantastic. Everyone’s in a great mood knowing that. Now, we understand that memory grants will scale with whether operators can share memory or not. So, if we can’t share memory, then we can reuse the parts of a memory grant.

If we can’t, then we need to ask for more memory so that we can hand it all out evenly. The size of the memory grant will change depending on, again, not only the length of your data set, but also the width of your data set. And now, if I run this query, this will go up to 9-ish gigs of memory grant.

If I select fewer columns, then the memory grant will go down. But this about me column is in barcar max, and that’s really where the big memory grant comes in. Now, the way that SQL Server estimates memory for string columns is it estimates that they will be half full.

This is to give it some fudge factor because some of them might be quite full, some of them might not be very full, and some will probably be somewhere in the middle maybe, but SQL Server just guesses 50% will be full, and it comes up with a memory grant based on that.

If we look at the execution plan for this, we can see that it indeed asked for 9.7 gigs of memory to sort 1,000 rows with these columns. Now, remember, it was about 166 megs for just the ID column. If we selected ID and display name, it would be 300.

If we added website URL, it would be 900 megs, location 1.2 gigs, and about me goes right up to 9 gigs. So the next time someone tells you that it doesn’t matter if they choose oversized columns for strings to avoid truncation errors, you can now punch them until they stop moving, which is usually a pretty sound approach to that comment.

Unless you just want to teach them this, that’s also okay too. Depends, right? On what, I don’t know.

So, that sucks. And I would not want to see you running into this because, I mean, I have 64-ish gigs of memory in my laptop. I have 50-ish assigned to the SQL Server instance.

So, asking for a 9.7 gig memory grant takes a lot of memory away from the buffer pool. If you needed to run multiple copies of this query, you might end up having a hard time doing that because there’s only so much memory that SQL Server is willing to give out as query memory grant memory at once.

So that’s probably not awesome. Alright? It’s not great.

And if we, I mean, 9.7 gigs is as high as I can get a memory grant for here. There’s a cap on that as well. So, like, if I selected more rows, it wouldn’t go up. But if I had more memory, then it certainly could.

And if I had, if I selected fewer rows, then it could probably get it to go down a little bit. But where’s the fun in that? We’re only selecting a thousand rows and we got a 10 gig memory grant. Now, this goes back to, I can’t remember if I, if I, if I mentioned this earlier.

This goes back to what needs to get sorted. So, it’s not just reputation that we need to sort. Even though that’s the only column we’re saying order by, that is not the only column that we are ordering.

We need to sort ID, display name, website URL, location, and about me by reputation. So, all of those columns need to get written down in memory too. So, think of it like an Excel spreadsheet.

I mean, I know it’s not. But just mentally, this is a good way for people to understand what’s going on. Think of it like an Excel spreadsheet, where when you push that little button in the top left-hand corner, and it highlights everything, and then you choose a column to sort by, and the entire spreadsheet flips to how that column is sorted.

That’s very much like what happens here, where we need to flip all those columns into the order that we’re asking reputation to be in. No bueno. Now, you’re starting to, you’re starting to think about that, that informational versus relational column stuff.

And you’re starting to think, boy, that, that Erik Darling might be right about a thing or two. I can sense it. I can feel it. I can feel it. Another thing that I want to clear up is that memory grants are not the grant times degree of parallelism.

I’m going to run this query at max.8, and we’re going to see that the memory grant goes up a little bit beyond 166. It’s going to go up to about 180 megs. But it’s not going to go up to 166 megs times eight.

In a parallel plan, each thread is considered its own execution context, meaning that each thread, or rather, the memory grant that SQL Server comes up with will be split evenly amongst each thread. There is a little bit of memory overhead for parallelism, the CX packet exchange buffery doodads.

There’s a little bit of exchange overhead in this plan for the hash join. But there is not 166 megs times eight. There’s not like a 1.2 gig memory grant for this query because it’s parallel.

Well, the memory grant gets split up amongst threads. So there is that saving grace, sort of. Anyway, I believe that’s it.

Yes. Cool. All right. So sorts. Be careful with them. Be careful with which columns you’re asking to be ordered by. If possible, split your queries up into the informational versus relational column thing that I showed you before.

There’s probably some other stuff, but, you know, I think you’re smart and that you’ve probably, you probably can just hit rewind if you’re really interested. Right?

Yeah. I think, I think you’re cool. I think you’re good. But remember also that you can fix a lot of sorts by adding indexes and we’ll probably cover that in a different video coming up later. A little bit later.

Not a lot later. A little bit later. All right. See you later.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts