A Little About Memory Grants In SQL Server Query Plans
Video Summary
In this video, I delve into memory grants and SQL Server execution plans, explaining how they work and how operators can share memory within a query plan. I also discuss how SQL Server decides on the size of a memory grant for a specific query, focusing on sorts and the impact of string data types, especially those with large maximum lengths like `VARCHAR(MAX)`. By running through various examples and queries, I highlight how memory grants are calculated based on row count and estimated data size, and how they can vary depending on join algorithms. Additionally, I touch on parallel query execution and how memory grants are adjusted for such scenarios.
Full Transcript
Erik Darling here with Darling Data, the most widely used SQL Server consultancy in four towns. What, what, you’re not impressed by that? I don’t know what else to give you. This video is going to be focusing on memory grants and SQL Server execution plans. And a little bit about how they work, a little bit how operators are able to share memory within a query plan, and how SQL Server decides on the size of a memory grant for a specific, for a given query. Now, this is not going to cover memory grant feedback or any of that other stuff. This is just going to cover how SQL Server, out of the box, decides on a memory grant. So we have query plans turned on because, believe it or not, this is not my first take recording this.
Something stupid happened, and I had to start over again. Namely, my printer, which is apparently possessed, just started making all sorts of weird noises, and I found it distracting, and I found it detracted from the, the normally very high quality of my recordings. So, let’s run this query, which is just selecting the top 1000 IDs from the users table ordered by reputation, an ascending order because we have not specifically asked for descending, and of course, every sane database in the world defaults to an ascending sort when no direction is specified. So, let’s run this query, and let’s look at the execution plan. We have a finely ordered result set. Amazing.
Now, since we don’t have an index that leads with the reputation column at the moment, if you watched the last video, which I hope you did because you like all my videos and you subscribe to my YouTube channel, the thumb and the bell button, we don’t have that index anymore. I got rid of the execution plan. But if you look at the execution plan, we have a query plan. And because we have a sort in the query plan, SQL Server needed to ask for some scratch base in order to write the results that we’re selecting down in the order that we are asking for, the presentation layer order that we are asking for in the query.
That means we got a 182 meg memory grant to sort that ID column by that reputation column. Because that’s all we did. If we look at this arrow going into the sort, the estimated row size is 15 bytes, the estimated data size is 35 bytes. And since we’re selecting a whole bunch of rows, well, since we’re, you know, trying to get the top 1000 rows out, SQL Server does some multiplication and says, all right, 182 megs, that should do it.
Should set the boat straight. It does. We don’t spill. We don’t get a warning that we use too much memory. So apparently, this memory grant was Goldilocks, right in the middle, perfect memory grant for this query. If I run this query, where I join one result set of that select top 1000 to another, the memory grant here isn’t going to double.
And it’s not going to double because SQL Server is going to choose a hash join algorithm to make this query to join those two results together. Because we have the hash join in the query, SQL Server is able to share memory from one sort operator to another. So this portion of the query plan goes, gets all the rows, inserts them into the hash table that SQL Server creates to implement the hash join.
And then when this portion of the query plan starts probing in down here to get the rows that we care about, then this portion of the query plan runs and this sort uses memory that this sort operator gave back. So if we look at the overall memory grant for this one, it only went up by about a meg.
And it only went up by about a meg because that hash, so apart from sorting data, hashes also require memory to do their thing, to create their hash table and do some work. So this only goes up by about a meg.
So we know that those two sorts shared memory because neither one spilled, neither one seems particularly starved for memory. And again, we don’t have any warnings that we asked for too much memory.
Again, this is a pretty good Goldilocks zone grant for this query. If I change the query a little bit and I force a loop join, rather than letting SQL Server’s cost-based optimizer decide on the join algorithm, then the query plan in this one will show a memory grant that just about doubles here.
Actually, I think it exactly doubles here, if I’m remembering correctly. Rather than the hash join, we have the nested loops join. And if we look at the memory grant for this thing, we have a 364 meg memory grant, which is 182 times 2.
So I think anyway, at least from… Ran out of fingers at 5, 10? I don’t know. Thumbs are fingers.
Hot dogs are sandwiches. The world’s a strange place. But because the nested loops join operator doesn’t offer what my dear friend Paul would call any phase separation in the query plan, it’s all sort of streaming, then that memory grant can’t be shared.
And we need twice as much memory for each side of the query to do its sorting. So we’ve learned so far. SQL Server, to calculate a memory grant, looks at the number of rows and the size of those rows in order to figure out how much memory to be granted.
It also has to decide the memory grant on how many memory consumer… How many memory consumerating? Wow!
What a time to be alive. How many memory consuming operators are in the plan? And if memory can be shared across different portions of the query plan based on the operators that get chosen in there.
Now, where things get more interesting, I think, is when we start looking at string data. Now, you’ll notice that I have some numbers listed over here.
This should be 182. Don’t tell anyone. Forgot to update my entire script. But if we look at this query, the memory grant for this query, Now, these numbers are cumulative.
So if I just did web display name or website URL or location, we would get different memory grants. But if we sort of just quote columns in until we get down to about me, which is a max column and varchar max, then this will all lead up to about a 9 meg memory grant.
Some of the other memory grants in compat level and 160 have been inflating a little bit on me. So these numbers might be off by a little bit, but they are going to be large enough for you to get the general idea.
And of course, this one went up by quite a bit. This went up 14 gigabyte memory grant to sort this result set. The reason why we get a 14 gig memory grant here is because of the way that results get sorted by SQL Server.
Now, the easiest way for you to visualize this is to picture an Excel file, right? And that top left-hand corner of the Excel file, you have that magic button that highlights all of the rows and columns in the sheet.
And then you have the little sort button up at the top, and you hit that, and you can choose a column to sort by. And unless you are a deranged person, and you don’t choose to sort your entire spreadsheet by that column, if you just sort one column independently, you’ve ruined your spreadsheet.
If you sort all of the columns by that column, you’re probably in okay shape. But what happens is the entire spreadsheet, all the columns and whatever, and the spreadsheet will flip to match the sort order of the column that you chose.
That’s a lot like what SQL Server has to do when you ask for ordered data without a supporting index. You have to sort, you have to write down in sorted order by whatever column or columns you’re choosing to order by all of the columns that you are selecting.
So you write down reputation in order, but you also need to write down the rest of the attached row in that order as well. So all of these columns, all of the data from these columns is being sorted along with reputation.
That’s why the memory grants for string columns, especially big max data types, can get so wild and out of control is because SQL Server, when it needs to figure out string columns, it guesses that they’re all going to be half full.
So if you have a, let’s to make it easy, let’s just say you have a VARCAR 100 column, SQL Server is going to guess that every single row in that column is 50 bytes full.
So it’s going to guess at the half full mark because some might have less or fewer bytes in them. Some might have more bytes in them. And so it does that to sort of get right in the middle and just sort of give itself some fudge room on either side of that.
So if you have like a VARCAR 1000 column and everything in there is like a state code, like M-A-N-Y-C-T-N-J, other northeastern states, SQL Server is going to guess that they are all 500 bytes full even though you never cross like the two byte mark.
So that’s real depressing. And VARCAR columns, Unicode columns, of course, since they’re double byte stored, it’s just basically going to be whatever, whatever size you choose for the column.
SQL Server is going to guess half of, like half of that. So if you have like a, like in VARCAR 200, it’s going to guess, I guess it’s still like 200, but anyway.
String columns were a mistake. We should just have numeric codes for everything and translate everything into binary and never have to deal with this string. Now, one thing that, important thing that I want to point out is that memory grants are not, for parallel queries, are not the grant times whatever the degree of parallelism for the query.
The memory grant is divided evenly amongst parallel threads. The reason for that, of course, is that every plan starts off as a serial plan. Even if the cost is, like SQL Server has a bunch of like initial costing things to figure out the initial cost of the query.
But the initial plan that you’re going to get every single time is a serial plan. SQL Server only starts exploring parallel plans if you have a cost that breaks your cost threshold for parallelism, which I’m going to have a video about that soon, and related to that soon.
And if there is nothing in your query that would inhibit parallelism in any way, non-inlineable scalar UDFs, inserts into table variables, things like that.
Those are the two most common ones. Different cursor options will do it too. But if we run this query at max.8, we will see that the memory grant for this query does not get multiplied by 8 because that would be insane.
It gets divided by 8. So what you get is a memory grant of about 197 megs. Now, this goes up a little bit from before. It was like 182, 183 before.
Just because we do have some hashing in here. We have some sorting in here. And there’s some parallelism going on. So things do go up a little bit, but they don’t get multiplied by 8.
So what SQL Server does is it takes that initial serial plan, comes up with a memory grant for that. That’s why in the query plan properties, you will see under the memory grant info, you will have the serial desired memory, which is 188.032, which just about would have been what we got for the 183 plan.
Divide by 1024. That should get you pretty close to 183. So SQL Server comes up with the serial plan, a memory grant for the serial plan.
And then if a parallel plan is chosen down the line in the optimization process, the memory grant will get divided evenly amongst the number of threads or the degree of parallelism for the plan.
That is a little bit about how SQL Server comes up with memory grants for plans based on the size of the data that is getting passed into memory consuming operators and other stuff like that.
This video mostly focused on sorts because sorts are the operators that I see responsible for gobbling up the most memory. Bad estimates for the number of rows can over inflate memory grants.
Bad estimates for the size of data. Again, this comes back to like how you designed your schema. If you, you know, again, VARCAR 1000 column for, you know, very small bytes of data, you know, like one, even if everything was VARCAR, even if like you had a VARCAR 1000 and there were only under 100 bytes in there, you’ll be overestimating that by 400 bytes a row.
So the worse your estimates are for the number of rows, the worse your sort of schema design in for how big a data type you have assigned to your string columns, sort of the worse those memory grants can get on the high side.
And of course that sucks because if you’re, you know, if your server isn’t set up from a hardware perspective to, you know, cache like, you know, your, like your, like most important data in memory and also service queries, then those big memory grants are going to steal space away from the buffer pool where SQL Server stores all those precious data pages.
And it’s going to, you know, knock those back out to disk. And, you know, it’s just going to make things worse. You know, weird variable performance issues in there because, you know, sometimes you’ll be going to disk, sometimes not.
And of course you can hit issues with, you know, query, like, like, you know, like just like you can run out of threads and hit thread pool weights, you can run out of available query memory and hit resource semaphore weights.
So we’re going to, we’re going to talk more about that when we talk about how I use SP pressure detector. I’m going to do some videos on that shortly, sometime this week. And then, you know, that’ll be fun.
Yeah, more stuff. But again, kind of calling back to the last video, there are times when you can create indexes that put data in the order you care about so that when you choose your order by columns, then you can have data physically sorted by the, or logically sorted by the index ahead of time so that you don’t need to ask for memory for separate scratch space to write all that stuff down.
But that was the last video. Go watch that one. Anyway, that’s about all I have to say about this. I forget what the next video is going to be about. We’ll figure that out in a minute. But anyway, as usual, hope you enjoyed yourselves.
Hope you learned something. And, you know, do the old like and subscribe. It’s the thumbs up and the bell. So, appreciate that. Anyway, I’m going to go figure out what’s next. Thank you for watching.
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.