Updated procedure to format queries to suit current formatting memes I enjoy
Organized the procedure a little bit better, putting all the declared variables and temp table creation at the beginning
Fixed some inevitable bugs that come with formatting and reorganizing really long stored procedures
Cleaned up error handling
Added a check for the signed query hash action; I found the procedure failed on some earlier builds of 2014 and 2012
So uh, go get those. Use’em. Have fun.
Do a SQL.
Thanks for reading!
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.
In this video, I delve into the intricate relationship between queries and memory usage on SQL Server. Using my novel stored procedure, SP_pressure_detector, I demonstrate how to evaluate memory usage effectively by running a query designed to fill up the buffer pool and another that generates a significant 16 gigabyte memory grant. By analyzing these processes, viewers can understand how different queries compete for memory resources and the impact on overall server performance. I also explore practical scenarios where multiple memory-intensive operations are run simultaneously, illustrating the dynamic changes in memory allocation and the resulting effects on buffer pools and query execution.
Full Transcript
Erik Darling here with Erik Darling Data. And we recently got sponsored by BeerGut Magazine. Thank you for sending some beer so that I can hopefully someday be a centerfold in BeerGut Magazine. Appreciate you, your wonderful publication. Truly doing work that the journalistic world is afraid to do. Anyway, today I want to talk about, you know, queries and memory and how you can use my novel stored procedure, SP pressure detector, SP, was there an underscore? No, there’s an underscore. SP underscore pressure detector to evaluate memory usage on your SQL Server and how queries and different things might be fighting over it. Now, I have one query here that’s designed to just fill up the buffer pool. It hits the POST table, which is big, and cross-joins the VOTES table because it’s a demo and why not, I don’t care. And then I have this query down here, which will generate a 16 gig memory grant. One six. And we’re going to see how these things kind of play together. Now, if we look at how big the POST table is, we can see that it is 120 gigabytes. And that means it is larger than the amount of memory we have assigned to the server, which is just about at the 90, well, max memory, max memory is set to 90 gigs.
The VM itself has 96 gigs. If we go look over, oh, not there. If we go look over here, we can see 96 gigs. That’s 96 times 1024. We do correct math in these videos, by God. But anyway, let’s go look at what’s going on in the server right now, which is absolutely nothing. There is nothing happening. The server is wildly, wildly bored. If we look at some of the details here, we can see that the buffer pool is up around 86 gigs, just about close enough for me to say 86. If you want to argue, I don’t know, do it in the mirror, mirror in the bathroom. We can see that currently there are about three gigs of memory assigned to other things within SQL Server. What are they? I don’t know. Maybe just a whole bunch of little things like this. I don’t know. You’ll have to just deal with that too. But what happens when we run this store procedure.
And we come look over here. We’re never going to really get up to that 90 gigs of max server memory. Like we’re never going to, like the buffer pool is never actually going to hit that number. It’ll always stay just around the 86 gig mark. Sort of interestingly, if I turn lock pages and memory off, this thing will stay just around like 82, 83 gigs.
We do use a little bit more buffer pool when we have, when we have lock pages and memory turned on. I don’t know if that’s like the biggest deal in the world, but you know, it’s like, at least in this situation where it’s just like a few gigs difference. But, you know, it just may be something helpful to know. And if we look at what happens to memory when we run the memory thief, store procedure, and come look at SP pressure detector, we’re going to see immediately that this 16 point something gigs gets reserved.
And memory used is going to slowly, or the buffer pool is going to slowly decrease down to about 76 gigs. We’re also going to see the stolen server memory setting, and that’s going to slowly creep up as the query uses the memory that it’s been granted. We can see that the memory has been granted that 16 whatever gigs, and we can see that the memory that it’s used so far is only about 7 gigs.
But as that number creeps up, now this number, now we’ve used about 15 gigs, we’re going to see the stolen server memory setting, or stolen server memory metric creep up. So, stolen server memory is a snapshot of what is taking memory away from the buffer pool currently.
All right. If we kill this query, murder it, and it’s stopped, and we come look at SP pressure detector, we’re going to see this number disappear.
It goes away immediately. Stolen server memory also goes back down to about 3 gigs, but used memory hasn’t really recovered, right? We see that number is going to stay exactly the same because nothing is asking to get read back into the buffer pool and get things moving there.
All right. So, if we keep running this, nothing really is going to happen. Cool.
That’s good to know. We’ve set up some sort of, like, things in isolation that matter. Okay. Let’s abuse this server a little bit. Close this. We don’t need that open.
And we’re going to run four copies of our memory thief store procedure. And we’re going to run three copies of our buffer pool filling store procedure. And let’s see what happens.
All right. So, we’ve got our memory thieves. And we’ve got our pool fillers. And if we come over here and run this, this has jumped up to about 50 gigs. Stolen server memory is about 12 gigs.
But we’re going to see that the buffer pools slowly decrease as these memory grants start getting used, right? As these numbers start creeping up, these used memory numbers start creeping up, what happens? Buffer pools start shrinking down and down and down.
And stolen server memory creeps up and up and up. All right. So, you can see all that stuff happening there. And if we look at what’s going on with the queries, well, we’re going to see that the queries that need to, read a bunch of pages from disk into memory are, of course, going to be waiting on page IOLatch, SH.
All right. And we’re going to be waiting a long, long time on that. All right.
Reading pages from disk into memory. That’s what we have to do because we don’t have all the pages that we need in memory. And SQL Server is a curmudgeon about that sort of thing. All right. So, we come over here and look. We’re also going to have one query waiting on resource semaphore.
So, one of our queries that is asking for a memory grant is not getting it. It has requested that 16 gig grant, but SQL Server can’t give it out because it has reached the limit of how much it is willing to give out to queries. All right.
If we look over here a little bit, we will see that this thing is sitting in a queue, unable to do anything. And if we look down here, we’ll see that we have granted out all that. And even though we have almost the identical amount of memory that that thing has asked for available to give out, we’re not going to give it out to that query.
This is why we hit those resource semaphore weights. I’ve recorded a whole bunch of videos about that. But notice that what we end up with here is at some point, the buffer pool is shrunk down to 40 gigs.
And we have given out more memory to queries than we have kept in the buffer pool by about 9 gigs. All right. So, that’s fun stuff right there.
That’s really fun, interesting, messed up stuff. And if we keep running SP pressure detector, this situation isn’t really going to change much. If we just keep running this, eventually we’ll end up with more queries waiting on resource semaphore.
We’ll end up with the buffer pool just getting abused and beat up and dropping down to a way lower number. And, you know, things just not being fun for SQL Server. But that’s the new thing that SP pressure detector can show you.
I just added that recently while working with a client. This top result sit here. And as we kill off those queries, we stop things from running. Notice that we still have a very small buffer pool.
We still have not given anything back to the buffer pool. But we have reclaimed all the memory that we could give out to queries. So, that’s fun stuff there.
So, anyway, I think, you know, I’ve said it quite a few times in quite a few ways. But, you know, make sure when you are sizing your SQL servers, when you are tuning your SQL servers, and you’re trying to figure out why your SQL servers are slow and crappy and everything is falling apart.
Why doesn’t SQL Server scale? Well, probably because you didn’t give it enough memory. Because you are bad at guessing memory numbers.
Something. Bad memory fortune teller. But remember, you know, not only is the amount of memory that you need a function of how much data you have stored on the server, but it’s also a function of how much memory your queries are asking for.
If you want to figure out if SQL Server has enough memory, and using my store procedure, SP pressure detector is a great way to do that. Because it will help you determine if queries are waiting on memory.
You have resource semaphore weights, how much memory is generally taken away from the buffer pool. And, you know, you can look at the weight stats for individual queries and figure out, oh, we spend a lot of time waiting on reading pages from disk.
We spend a lot of time with queries waiting on, with the page IO latch weights. We spend a lot of time with queries waiting on resource semaphore weights, waiting to get memory. So there’s a couple of good things you can figure out.
Just with the hit magical F5 button, and you too can learn all sorts of horrible things about why your SQL Server is slow, and everyone is angry at you, and you will never, ever be a centerfold in BeerGut Magazine. Anyway, it’s Friday.
I hope you are too. And, I don’t know, I’m going to go enjoy my BeerGut Magazine subscription. And, I don’t know, I’ll see you in another video, or maybe you’ll just read me in another blog post.
Or, I don’t know, maybe once I become a world-famous model, world-famous centerfold, I’ll just quit all this SQL Server stuff and go live with the glitterati. Anyway, thanks for watching.
See you next time. Where’s that stop button? Where’s that stop button? And, I’ll just limit it to you in that very столpar,
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.
If you head to my GitHub repo, there’s an update to sp_PressureDetector, a free script I publish to help you figure out when your server is under CPU or memory pressure.
While working with a client recently, I found it useful to illustrate how much of their memory was used by different memory consumers.
Buffer pool
Total other
Top 5 other
It helps put in perspective just how many different things are asking for a piece of your precious RAM.
blue
This is a bad example from my personal server laptop, but you get the idea.
Anyway, that’s that. clink over to my GitHub repo to download it.
Thanks for reading!
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.
sp_PressureDetector now includes statement start and end offsets, in case you want to build plan guides for queries
WhatsUpMemory got a huge performance tuning, which I’ll talk about in tomorrow’s post!
Nothing else here. Happy downloading and analyzing.
Or analysing, if you’re from a refined country.
Thanks for reading!
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.
There have been a ton of improvements and fixes here. I realize it’s bad form to generalize like this, but I went into overdrive making sure things were nice and tidy for GroupBy. There have been improvements to XML querying and processing, how data gets pulled in and correlated, and finally how it gets displayed.
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.
