In SQL Server 2019 Memory Grants Are Higher With Batch Mode For Row Store Queries

Because I Got

---

Video Summary

In this video, I delve into the intricacies of SQL Server memory grants and compatibility levels, particularly focusing on how they behave differently between SQL Server 2017 and 2019. I demonstrate a practical example where I compare the memory requests for identical queries run at different compatibility levels to highlight the significant increase in memory grant sizes with SQL Server 2019. This comparison underscores the challenges faced by users of Standard Edition, especially when dealing with limited RAM resources or virtual machine constraints. By walking through these examples, I aim to provide insights and potential workarounds for those navigating the complexities of SQL Server memory management on a budget.

Full Transcript

Hello everyone, Erik Darling here with Erik Darling Data. I was so happy this week I got brand new stickers in, which can either be apparently a 45 pound weight plate or an LP, if you’re into that sort of thing. Whichever one you’re into more you can pretend that sticker is. Now, in case you can’t tell, I’m a bit under the weather. I’m not well, I’m sick. I got sick earlier this week. When I found out that Standard Edition of 2019 is still only allowed to have 128 gigs of RAM for the buffer pool. That’s how strongly I feel about what a bad decision that is. What a terrible decision that is. It made me physically ill. And, you know, I was starting to feel a little better. I was on the mend. I was taking my vitamins and drinking my OJ. And then, I started testing some of my old demos on 2019. I just got sick all over again. Now, the thing is that despite the fact that, you know, SQL Server 2019 only has 128 gigs of RAM for the buffer pool, you can use memory above that for queries, but those queries are now, they’re asking for more memory.

And this is going to be a real problem if you can’t have more than 128 gigs of RAM in your server for some reason. Or, like, you know, you’re on a VM where the host is small, or, you know, your boss is a cheapskate and won’t buy you memory. You know, just like lots of reasons, or like you’re in the cloud where there’s just not an instance size that has like a sane amount of processors and memory for SQL Server Standard Edition. Because there’s a whole lot of that going around. So what I want to do is I want to show you exactly what I mean. I have two query windows set up side by side. One of them is compat level 140, which will give us the 2017 behavior.

And the other one is compat level 150, which will give us the SQL Server 2019 behavior. And I’ve got two, I’ve got the same query on both sides. And what this query does is it forces SQL Server to sort data. I don’t have an index on reputation that will help me sort the data that I, in a way that’s meaningful to me. So SQL Server is going to have to break out its tiny little baby hands and sort that data for us.

So here’s what happens. SQL Server 2014. Run this. It executes. We get the execution plan. We see we have a sort here. And we see that this sort asks for 166.528 megs of memory.

All right. Cool. What about that same query and compat level 150? What now? What do you have to say for yourself now, SQL Server, especially when I turn on execution plans?

For some reason, I thought that would be global. I don’t know why I’m that stupid. Let’s run that. I can look again. 186 megs of memory. So about 10% more memory there, I think. At least I think it is. I’m not very good at math.

So that could be right. It could be wrong. So let’s call this 186. And that’s no fun. Now let’s take out a couple contenders here. Let’s bring display name into the mix.

And let’s see what happens now. On 2014, that’s 298 megs of memory. All right. We’re running that. Okay. We’ll go run it over here. What’s the big reveal? On SQL Server 2014, that is still 298.968. Okay.

And in SQL Server 2019 mode, compatibility level 150, 334. Eee. Sweet. Sweet. Summer rain. All right. So that was 334 there. All right. 334. All right. Let’s look.

Let’s add the website URL column in. Let’s see what happens when we do this. All right. We’ll come over here. We’ll run this one. Wait for this to finish. All right. There we go. And we’ll come over here and wait for that to finish. While we do that, we’ll see that this is still at 906.968.

So about 900 megs there. I’m still… Okay. Well, we’ll be fair. We’ll say that’s 906. That is what it was. We come over here and look. What do we have? Uh-oh. We are up over a gig. We are at just about… Okay. So like… I don’t know.

I don’t want to get into like too many decimal places. So I’m going to call this one gig even. All right. We’ll change this to reflect that. So we’re up at one… Well, one gig. Now we have to change it. 1.0 gigabytes. As some of my friends from Eastern Europe say.

Gigabytes. All right. Location. Let’s add this in because now I’m curious. How much is this going to go up? This is… This should be bafflingly fun. All right. That one’s done. We’ll come over here. Run this. All right.

What do we got? 1.2 gigs. Just as we expected. All right. Good stuff there. Happy, happy, happy. What do you ask for? SQL Server 2019 mode. 1.3 and a half gigs. So this went up a bit too.

So we’ll say that went from 1.2 to 1.3 gigs. Now this one asks for 9.7 gigs. 9.7. That’s nearly 10 gigs of memory. To do this. So let’s run this one. And this will run a little bit.

And then this will run this one. We’ll go look over here. We’ll see this. Memory grant. Oh, that one actually went down a little bit. I don’t know. I wonder why that was. Oh. Maybe because I have this VM running. Yeah.

That’s probably it. When I have memory from other things taking up space SQL Server, sometimes it deflates its memory grants. But we go over here. And apparently this is the max memory grant that we can give out right now because I have memory touching other things. So that’s good to know that SQL Server is kind to Camtasia and lets it use memory without trying to take it away.

But anyway. So these were both the same at just around 9 gigs. Now, the one thing that I want to point out is that if we run this query a couple times. We’re already in Compat level 150.

But let’s just make double, triple extra sure. If I run this query once and, ooh, CPU fans kicked in a little bit there. Run this query once. We asked for the 9 gigs of memory. All right. And we have this warning down here. Excessive grant, yada, yada, yada.

And if I run this again in 2019 compatibility level, I will get more sane memory grants. SQL Server will reduce the memory grant because it has this thing called memory grant feedback. And that’s really cool and nice.

But that’s not in Standard Edition either. Yeah. Yeah. So if you need help with Standard Edition, please call me. Because apparently Microsoft is not helping you with Standard Edition. So good job there.

Anyway, my name’s Eric. This thing, whatever. It might be a flying saucer. Who knows? Maybe when I take over the world, this will be the new currency. This will be how people pay each other with Darling Data stickers. That’d be nice.

I’ve always wanted to be on currency. Anyway. Thanks for watching. I’ll see you in the next video. Goodbye. Bye. Bye. Bye. Thank you.

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.