Learn T-SQL With Erik: Controlling Memory Grants
Chapters
- 00:00:00 – Introduction to Controlling Memory Grants
- 00:03:45 – Example Query and Indexing for Order
- 00:07:24 – Exploring Query Plans and Sort Operators
- 00:10:00 – Separating Informational from Relational Columns
- 00:11:47 – Using Cross-Apply for Smaller Sorts
- 00:12:09 – Summary of Key Points
Full Transcript
Erik, monitoring tool mogul darling here, home of, coming to you from Darling Data, home of the best SQL Server performance monitoring consultancy in the world, with the addition of a couple words I can make the lawyers happy and not have to say outside of New Zealand, because I don’t think, I’m pretty sure Paul White doesn’t build a monitoring tool, so if he ever starts we’re in trouble. So that’s what we’re going to do this week, we’re going to carry on with that, and we’re going to talk in this video about controlling memory grants, something that becomes important, not only if you’re tuning one specific query, but also if you need to make your queries as a workload, sort of get on with each other and be happy and all that stuff. So that’s what we’ll do today.
Down in the video description, everything in life that you would ever need to achieve has a link available in the description of this video. You can hire me for consulting, I can come work on your SQL Servers, either virtually or live and in person, you can purchase my training, which is probably one of the best SQL Server training deals on the internet, even in New Zealand. You can become a subscribing channel of the member, if you feel like forking over four bucks a month just to say thanks for all this stuff, you can do that.
You can also ask me office hours questions, those remain free on the house, right? On the Darling Data. I have an account for those.
And you can also like, subscribe, you can tell a friend, you can tell a foe, you can tell a frenemy, whoever you want to feel like telling about this channel, please do, because I like when my subscriber counts go up, that’s always nice. If you want the best SQL Server performance monitoring tool on the internet, you can download mine, also totally free, right? Open source, no email required to sign up.
No phoning home with weird messages. No weird telemetry. Not trying to figure, not asking what your indexes look like. It’s just all the important T-SQL data collectors you would expect. You know, we grab, you know, CPU, memory, we can tell you about memory grants, CPU, memory, disk, the works, weight stats, blocking, deadlocks, long running queries, all this stuff that a growing SQL Server should have looked at over time.
And if you want your robot companions to help you make sense of your performance monitoring data. I have a bunch of read-only built-in tools that know what that data should look like, the general shape of it, and can help you do that. I will be out in the world.
I have two things left this year, unless something else cool comes up. I will be at Data Saturday Croatia with an advanced T-SQL pre-con. You can go to that link there. That’s a hell of a link.
You might even just be better off Googling Data Saturday Croatia as, you know, I don’t know if you’d want to type that. But then I will also be at Pavel. I will also be at Past Data Community Summit in Seattle, Washington.
My use and utility there is a mystery as of yet, but I’m sure all will be revealed as we get closer to November 9th through 11th. So we have that to look forward to. But now it is May.
I have a chisel on my desk for some reason. And, well, let’s talk about memory grants. Let’s chisel away at some memory grants. So I’ve got a couple indexes created here. These will become important later.
Gaze at their majesty. One is keyed on user experience. One is keyed on user ID and score. The other one is keyed on user ID with scores and include. The reason for that will become obvious later.
So we’ve got this query here that I have pre-run because it takes a little bit to run. And I don’t want to stand here in the hot, light heat while I wait for all this. This query will ask for quite a big memory grant, both because it is written in a way with this derived join, which will force us to run this query and produce a result.
And two, because we are selecting all of the columns from the comments table, one of them being a column called text, which is in InvarCar 700. So just to sort of get ahead of things a little bit, this query asks for an 11 gig memory grant. If you want to fix a big memory grant, you have three basic things you can do for any given query.
One, you can add indexes that support the ordering sequence. That’s the stuff that you need to do. Two, you can separate your columns between the informational set and the relational set, generally so that you don’t need to sort as many columns when you select things and order by things.
You know, the indexing is useful because with an index putting data in order, you might be able to avoid a hash aggregate or a hash join, which, you know, if you have data already in order, SQL Server might find a nested list. It might find a nested list of loops or merge join more palatable.
And the third is you can write your query in a way that allows it to do more small sorts rather than one big sort. In this query, we do one big sort and we select a bunch of columns. And we, I mean, we obviously do not use either of the indexes that we had created because we just scanned the clustered index of the comments table and zoom it.
Once again, betrays me. So there is that. A lot of things can be solved with batch mode, right, as we’ll see here.
If we run this query, it will run much, much faster. It will, the CPU and IO boundness will go away. However, it will still ask for an 11 gig memory grant because we are still sorting all of those rows for all of those columns.
So batch mode, great for many things, right? And if this memory grant were inappropriate, the memory grant might even adjust that. It might adjust between executions.
But in this case, it uses the whole thing. But anyway, with that index that is keyed on user ID and score, if we force SQL Server to use this, not only will we get a query that is faster by a little bit, even compared to the batch mode one. We are down to 1.388 seconds from 2.5, 2.6 seconds.
But this query really only has 1.388 seconds. It has one sort operator in it here. And the total memory grant for this is 5,888 KB.
Wow, that’s a lot of 88s in this thing. 1.388, 5.888. Geez, a lot of 8s.
Anyway, this is an example of having our data in order since we are partitioning by user ID and we are, sorry, and we are ordering by score. And, you know, also C.ID down here. But you crud.
Also C.ID down here. Because the ID is the clustered primary key on the comments table, that ID column is immediately part of the non-clustered, both nonclustered indexes that we created as a key column because they are not defined as unique. Anyway, with our data presented to SQL Server in order, we don’t have to sort stuff.
This next query plan is a little bit weird. So usually when I, so I’m in the process of redoing all my queries for SQL Server 2025. And one thing that happens with this query is that we, by forcing it to use this index and changing the reputation to 10,000, we actually do end up with another sort in this query plan.
At least it did the last time I ran it. It also runs a bit longer, right? So, and we do end up with a sort down here again.
I’m not entirely sure why right now. I mean, because we put the data in order here to make the next query. We put the data in order here to make the nested loops join more efficient. But I’m just not sure why this sort shows up here just yet.
I’m going to have to figure that one out a little bit. So bear with me while I do some surgery on that later. But both of these queries are written in a way where we are able to do many small sorts rather than one big sort.
And we’re using cross-apply to do that. Cross-apply is sort of like a for-each loop, right? So for each user with a reputation over 10,000 in the users table, we go and run this query.
Technically, we don’t even need this partition by element here because it is sort of automatically partitioned by each seek into the comments table for a user ID that gets passed in from the users table. So we could even take that out here. For this query, what I’m going to do is force the index where score is just an include column, though.
All right. And run this. And, I mean, it’s sort of the same plan shape as last time.
But just to sort of make the point obvious, this is an apply nested loops join. We can tell it’s an apply nested loops join because it has this outer references thing in the bottom. And what apply nested loops indicates is that we come in and we seek to the user ID column each time we make a trip into here.
So rather than like with the original form of the query where it was like join and then this thing over here, we sort of for each trip that we go into, we only sort for that particular user ID, which can be very, very useful. So another thing that I want to sort of bring out here is separating informational from relational. So what we could do if we wanted to save it.
Save it. If we wanted to just keep running our query this way with the join, we could cut down the we could just get a narrow set of columns here, not select C dot star. And then outside of that, we could join back to comments.
And then we could get the text column out here from that join back to comments rather than get that text column in here where we’re generating the row number. Because like I’ve talked about, the sort operator has to write down all of the columns that we’re selecting by the things that we need to organize. By the things that we need to order them by.
Right. And when we do that, we still have a sort in here because we’re back to using the oh, I lied. We have a sort here now because this index scan happens in batch mode and SQL Server does not trust batch mode stuff to rowstore indexes to maintain order properly when batch mode is used.
But we still have a much smaller memory grant. Right. We still have a much smaller memory grant at 1234 megabytes because we get all our relational work done up to here.
Right. So the join between users and comments and the filter to get down to row number equals one here. That’s this thing.
That all happens like over here. And then we get down to 600 rows here. And then we join to the comments table with an adaptive join. Very batch modey.
We can tell by the thickness of the arrow that it uses this one. Right. And then we join back to the comments table in order to get the other informational columns that we have. So the three things that we’ve talked about in this video that can help you control memory grants is one, indexing to put your column data in the order that your queries are asking for it in.
Two, separating informational from relational columns. Doing the relational work earlier in the query. Getting the informational data later in the query.
And three, using things like crossfitting. Using things like crossapply to do a bunch of smaller sorts via nested loops join sort of a for each loop rather than doing one big sort like with the derived join. Anyway, it is hot in here.
My brain continues to leak out of my ears. I love you, but I need to go drink some water. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
And I will see you back next week on Tuesday for another rampaging Office Hours session. All right, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.