Best Buy
Thanks for watching!
Video Summary
In this video, I dive into the world of SQL Server memory management and how it can be a tricky beast to tame. I share my personal experience with a large table that exceeds the available memory on my VM, illustrating just how critical memory is for SQL operations, even when you think you have plenty. I also explore the often misunderstood relationship between buffer pool memory and non-buffer pool memory, highlighting why simply setting server memory might not be enough to ensure smooth query performance.
Full Transcript
Oh boy. Oh boy. Erik Darling here with Erik Darling Data to this very day. I’m thinking about taking data out of the company title. I don’t really, I don’t actually care for data. I actually, I actually sort of hate it. It seems to be a big problem for people. Either they have too much of it, they don’t know what they have, they have, they have stuff they shouldn’t have. I don’t know, it just seems like, seems like data is nothing but trouble. I think this would be the Erik Darling Fun Company where we only talk about fun, non-dreary. things because data is very dreary, isn’t it? There’s no good news. It’s all depressing. And speaking of depressing, today I’m going to talk about memory, something which I have very little of, personally. But thankfully, my laptop has a decent amount of. My laptop sitting, my new laptop sitting over here to the right of me has 128 gigs of memory in it. And I have a single VM with 96 gigs of memory in it. Unheard of, I know. Unheard of. A single laptop with 128 gigs of memory. Amazing. But this memory is going to have a tough time in life. Because despite the fact that there are 96 gigs of memory in my laptop, I have this table in this Stack Overflow database that is 120 gigs. 120.
And as we all know, SQL Server does not work with pages on disk. Doesn’t matter how good your disks are. Doesn’t matter how expensive they are. Doesn’t matter how much. You paid your storage vendor for all flash and memory, NVMe, whatever. Other fine words they have for these disks. It’s before my 9am cocktails. So this might not be the smoothest take that I’ve ever done in my life. But, yeah, so SQL Server doesn’t care about pages on disk. Anything that you want to do with your data.
Whether it’s a read or a modification, it must end up in memory first. Hmm? Hmm. So even if we were to completely overtake memory on this VM with data from this table, it’s still not completely fitting there. Now, right now, in the buffer pool, well, it went down a little bit. I don’t know why it did. I didn’t do anything.
But actually, this is a good thing. So the buffer pool. Boy, oh boy. Monte Carlo. I might need to pour myself a Monte Carlo if I’m going to continue recording this video. So if we look at right now, what’s in the buffer pool is about 76 gigs of data.
Dreary, dreary data. And if I come over here and I run this count, I’ve been bullied a little bit into changing my query formatting. And so I’m going to give it, I’m going to give this a shot. It’s a little spidery for me.
I’m going to send a little alias in there. So it’s a little spidery for me. I don’t know if I like everything on a new line and indent it over. I’m going to give it a shot. I’m going to see, I’m going to see how it works out.
But if I run this, and I come back over here, and I start running this, we’re going to see the buffer pool gradually go up a little bit, right? 86. Oh, it’s climbing a little bit. Oh, it’s fluctuating. But it’s right around 86 gigs, which is fine because I have max server memory set to about 88 gigs here.
Now, the second query is looking for non-buffer pool memory, right? And I have a filter on here that’s looking, that’s filtering out stuff to make sure it has at least a gig of memory assigned to it. But, oops, I’m going to see this pre-9 a.m.
This pre-9 a.m. cocktail stuff is rough. But if I look over here, there’s a whole bunch of non-buffer pool memory that’s probably, I don’t know, about 2 gigs worth of stuff. I’m not a math guy. I don’t want to write another query to prove it to you.
But I would imagine that the 86 gigs of buffer pool is diminished by 2 gigs because of all the other stuff in here, right? But right now, the thing that I care about is anything, any non-buffer pool memory that has more than a gig of stuff in it, right? And if I run this and look, it’s going to be about 86 gigs.
So that’s just about there. Okay, fine. Now, here’s where people get all messed up when it comes to SQL Server. They look at, you know, I don’t know, the amount of data they have, and they think, well, 64 gigs will do.
It doesn’t matter how much data is actually in there. It’s like, I don’t know. I’m looking at this cloud instance, and if I keep adding memory, then the cores keep going up, and it keeps getting more expensive.
So I’m just going to stop adding memory. But memory is important. And not just for this buffer pool thing, because I want you to watch what happens to the buffer pool as I run queries that need memory.
So I’m just going to select a top 1,000. See, this is very spidery looking to me. It’s quite spidery.
I’m going to select a top 1,000 from comments ordered by score descending. And, of course, I do not have an index on this comments table that puts the score column in order. So I am going to have to physically sort this.
And sorting data is one of those SQL Server’s tiny little baby hands comes and breaks out the, comes and starts ordering the data. But we need memory to do that. We need a memory grant to do that.
So if I come over here and I run this, and I start looking at the buffer pool and what other memory is getting used, you can see that SQL Server has granted that query 16 gigs of memory. And as memory gets loaned out to that query, the size of the buffer pool goes down. And if I throw another one of those in the mix, we’ll see non-buffer pool memory go up again and buffer pool memory go down again.
This is getting into a rough situation. My buffer pool is severely limited, right? So now let’s get another one in there, right?
Let’s get a third one in there. Now we can see non-buffer pool memory. It’s up to 50. It’s almost half and half, right? It’s almost half of our buffer pool is gone to memory clerk SQL reservation. So SQL Server has made a reservation at Shea memory clerk, and we have granted these queries memory, and we have taken that memory from the buffer pool.
All right? We have stolen that memory from the buffer pool. It’s ours now.
So this is something that a lot of people don’t plan for when they start designing or speccing SQL Servers or choosing cloud instances. The memory you choose is not purely for the buffer pool. The memory that you choose has many, many other tasks.
We saw that even before there was 50 gigs of memory granted out to these queries, what happened? There’s about 2 gigs to other stuff, right? Just other things.
I don’t know. It’s playing cache, other doodads, gizmos, whatever SQL Server has to do. I don’t really know. I’m not good at this stuff. But all these queries have finished running now, and what I want you to notice is that the buffer pool is down to 32 gigs. 32 gigs.
And it’s not immediately coming back up, is it? They can keep running this query, and it’s going to stay right where it is. Even though there’s nothing else down here, SQL Server isn’t immediately just like, oh, well, come on, get in the buffer pool. Come on, the water’s fine.
It’s beautiful in here. Is it? No, it’s not happening. It’s not happening. SQL Server is not immediately filling. Because what does it know? How does it know what we need in there?
It doesn’t. SQL Server is not that smart. SQL Server is dumb like me. That’s why I like it. But if we come over here and we run this count query again against the post table, we will slowly see the buffer pool start to fill back up. And here it comes.
Coming roaring back to life. But this is sort of a funny thing because this is a situation that people often confuse with parameter sniffing. Waiting for SQL Server to read a bunch of pages from disk into memory is not exactly a fast thing all the time.
Right? Depending on the size of your data or the type of the data that you’re reading in. Like all sorts of stuff.
Like what you have to read. You can end up waiting a very, very long time to read pages from disk off into memory. Now, this count against the post table just ran for about 40 seconds.
It doesn’t take that long. It doesn’t actually take that long to count records in the post table. It’s like it’s not a 40 second operation.
But going off to disk and reading stuff can be a not fun thing to do. And this is something that you need to think very, very carefully about when you’re designing hardware or picking hardware or picking an instance size for your SQL Server. It’s not only how much data you have.
What are the memory requirements of your queries? Right? Because this thing ran for 24 seconds this time. It ran for 40 seconds last time reading a bunch of stuff from disk into memory.
But we had a bunch more stuff in memory this time. So we actually had a slightly faster query. We look at the properties of this. And we look at the weight stats. Number one is going to be page IOL action.
What do you know? About 16 seconds of the time we spent in this query was reading pages from disk into memory. Crazy, right?
Crazy. Well, it is 9.05 about. And it is time for my 9 a.m. cocktail. So I am going to go have that.
And I am going to bid you a fun to do. Thank you for watching. And I hope you learned something. And I will see you in another video another time. Goodbye. Thank you very much. Goodbye. Bye.
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.