sp_PressureDetector: What SQL Server Looks Like Under Memory Pressure
Video Summary
In this video, I dive into how SP\_pressure detector can help identify memory pressure issues in SQL Server. I explain two primary forms of memory pressure: queries requesting large memory grants and the buffer pool being depleted to fulfill those requests. By walking through a demo with a specific query that requires a 10GB memory grant, I illustrate how SP\_pressure detector displays real-time data on stolen server memory and SQL reservations, highlighting the impact on overall system performance. Additionally, I demonstrate how concurrent workloads can exacerbate these issues by competing for buffer pool resources, leading to increased page IO latch weights and resource semaphore waits. This video aims to provide practical insights into managing memory pressure in your SQL Server environment.
Full Transcript
Erik Darling here with Darling Data, the hippest, hottest, most fire SQL Server consultancy on the entire planet. Get my lawyers involved if you say otherwise. And I meant to record this video yesterday, but I don’t know, I hit various weird things and some time constraints and then my wife forcing me to watch Vanderpump Rules, did not get a chance to do it. So here we are, day two, and we’re going to talk about how SP underscore pressure detector can help you identify memory pressure in SQL Server. Now, memory pressure generally comes, or I, as a consultant, I generally see memory pressure in two forms. The first form is queries asking for big memory grants, or even just lots of queries sort of asking for like this swarm of memory grants, and other queries not being able to get memory to execute SQL Server.
And we’ll talk about this in a little bit more detail. SQL Server has only so much memory it is willing to give out to queries to run at a time. The second form of memory pressure is that that memory has to come from somewhere, and that somewhere, because most servers just don’t have enough memory in them to begin with, that somewhere is the buffer pool. Now, memory grants don’t use the buffer pool. Memory grants share memory with the buffer pool. So there’s this tug of war between queries who are like, hey, I need some memory to run, and the buffer pool being like, hey, I need to cache all these data pages to give to queries. And, I mean, you know, there’s query memory grants come along, a bunch of pages get knocked out of the buffer pool, queries finish running, pages come back into the buffer pool, maybe, if you’re lucky.
And then queries run and then queries run and then queries run and ask for memory grants, pages get knocked out of the buffer pool. So please try to give your SQL Server enough memory so that you don’t have this constant gas station sushi effect of memory just dumping in and out various things. So, by default, SQL Server will give any query up to around 20-25% of your max server memory setting to run.
And also, by default, the single query memory grant is something that you can tinker with a bit, either with resource governor or with the max grant percent hint. Another default I find that is rather cruel but is not currently a setting that you can change is that SQL Server is willing to give out around about 75% of your max server memory setting to queries to execute as a whole. So if you have three queries come along and ask for that, like, 20-25% mark, SQL Server will give it to all three.
The fourth query asking for that big memory grant comes along, it’ll get stuck. But other queries that ask for smaller memory grants may be able to sneak in depending on how much is currently loaned out to those. There are some good ways to deal with that.
You know, resource governor, if you are on Enterprise Edition, the max grant percent hint is one way to deal with that. If you are not or if you, you know, want a more focused, targeted approach rather than forcing every query to use a lower grant. But resource governor is something that I end up using quite a bit, especially on client servers that have lots and lots of memory, like terabyte, two terabytes of memory in there, because 20-25% of two terabytes is a pretty gosh darn big memory grant.
Standard Edition is, you know, a different story. You get that 128 gig cap for the buffer pool, but you can use memory over that for other stuff. So my general advice is if you’re on Standard Edition and it’s a server you care about, it should be Enterprise Edition.
But barring that, Standard Edition, you should give 192 gigs of memory. I was really disappointed that SQL Server 2022 didn’t increase the 128 gig buffer pool limit on Standard Edition. But, you know, I can’t fight the bean counters, dorks.
But anyway, you give SQL Server Standard Edition 192 gigs of memory. Set max server memory around the 180 or so gig mark. You have 128 gigs for the buffer pool and then 50 or so gigs for other memory consumers like query memory grants, lock manager, plan cache, all that other stuff to sort of deal with.
So I have, even though I have like 96 gigs of memory assigned to this humble virtual machine, I’ve turned max server memory down on this one because I want to exacerbate my problems. 96 gigs of memory is, you know, pretty good for the size of the database that I’m dealing with.
So I turned it down a bit to make things a little more close to what I see in real life. So if we look at this execution plan and we look at what this query got for a memory grant, it is 10 gigabytes. All right.
So this query gets 10 gigs of memory. This is about the most that a query can get on my server with max server memory set to around the 55 gig mark or so. So and this query returns zero results.
Why does it why does it return zero results? Because I am I have a row number function in here and I am filtering to where row number equals zero. So I want nothing to come back at all, because I don’t want to wait for SSMS to show me a bunch of stupid results.
But I want this whole thing to have to be expressed before this filtering happens. So the query plan is, you know, do all of this work in here. Right.
And this is what this is the work that happens inside that CTE and then filter on that work there where we get nothing back because SQL Server for some reason doesn’t know that a row number cannot be zero. So in this context, which is a little odd, but works to my advantage. And I don’t want to ever.
I don’t ever want that to change. Microsoft, please don’t file that as a bug. Now, I have this query running in batch mode because I really want because it’s just better that way. A lot of things are.
But I have this query running in batch mode. So it finishes pretty quickly. It finishes in about six seconds. This helps with the demos. But in row mode, this thing runs for like 20 seconds. And it makes the demos less interesting.
So let’s come over to this window. And let’s clear out wait stats so that we don’t have to care about anything. And oh, come on.
Get out of there. There we go. Now we’re really clear. So let’s start sort of with how this thing looks with nothing going on. We have a little bit of SOS scheduler yield because even though I just cleared out wait stats, there’s always something going on.
SQL Server is always up to something. It’s probably that customer experience improvement program thing running, ratting on me, ratting me out to Connor about all the things I’m doing in SQL Server. Trace flags.
Using the debugger on things. So some stuff that SP underscore pressure detector will show you about your SQL Server off the bat. Top memory consumers.
So we have this section up here. And the first thing that I want to show you is how much is consumed by the buffer pool. I want to show you. Let me get rid of this blue highlighting. That just makes the pink look weird.
All right. There we go. I want to show you how much memory is currently kind of being taken away from the buffer pool. And then under that, I have the top five non-buffer pool memory consumers on your SQL Server.
One thing that I find a lot in here, which is something that I have a script to help with in my GitHub repo, is a memory clerk called UserStoreTokenPerm. And that’s a security cache.
And I find lots of weird things happen to SQL Server when that gets above like the 2 to 4 gig mark. And clearing that out often alleviates a lot of weird issues. If there were any queries running that required a memory grant, we would see those in here.
And then we also have this low memory section. And this will tell you any point in which SQL Server had a low memory warning inside of it. So this can be useful to figure out how often your SQL Server is kind of tanking out on memory.
Down below that, I have a whole bunch of information about database size, how much memory is in the server, what max server memory is set to. Some of this data is duplicative just because I wanted it all to be in one place.
And so there are some extra rows in there. But sue me. I don’t know. Whatever. I should. I don’t know.
I like it. Works for me. If you’re using lock pages in memory, you’ll see that here. And then you’ll have information about various memory pools in SQL Server. Typically, the ones that we see action in are the zero pools right here, like this one.
This one here is usually the one that I see fluctuate as queries ask for memory grants and such. But you see the target memory of the pool, the total memory, the available memory in the pool. If any memory has been taken out of the pool, we’ll see that here.
Lots of good information. And I don’t know. I guess pool ID 2 is the better thing to sort of keep in mind there. I show all of these.
I used to filter these down some. But then I found in, like, some servers, the pool that I was filtering down to was not the one that was in use. So you’ll see this especially a lot in Azure, various Azure implementations, SQL DB, stuff like that.
Because that has a whole bunch of extra things in here for some reason. So I try to be kind to Azure SQL DB, even though I sort of hate it. Aside from the fact that it uses read committed snapshot isolation by default.
All right. So let’s dive in to what SQL Server looks like when queries have memory grant pressure on the box. Now, I have that same CTE query in here.
And we’re just going to – we don’t need 10 threads. We don’t need 10 iterations. But I want to do this so that you can see – well, we kind of want you to see a bloodbath a little bit. So now let’s kick this off.
And let’s give this a run. And we’re going to see some stuff change in here. All right.
We have this section here. We still have – I don’t know, we’re running about 9 gigs there. But now we see this has popped up to about 10 gigs. So the two lines that I want to focus on sort of off the bat are these. All right.
And so stolen server memory is, I mean, in this case, like in isolation, how much memory – like queries have used of their memory grants that have, like, really taken stuff away from the buffer pool. And the second line, SQL reservations, is how much memory – these queries have been given for memory grants. If we come down here, and we’re going to focus on this section first.
We’re going to look at the line that I was talking about before, where we have 40 gigs available. Well, we have 40 gigs in total, but we only have about 10 gigs available now because about 30 or so gigs has been granted out. All right.
31 gigs or so has been granted out here. All right. So we have all these queries running. All right. And of that 30 gigs that has been granted – oops, let me focus on that a little bit better. Of the 30 gigs that’s been granted, about 12 gigs has actually been used by the queries.
We have three queries that have been given their memory grant and seven queries that are waiting on memory grants. If we give SP Pressure Detector another run now, we’re going to see this resource semaphore weight show up. And we’re going to see this is the result of queries waiting to get memory grants to execute.
All right. So if we focus in a little bit on this line, we have had 24 tasks wait on average about 27 seconds to get a memory grant here. Now, all well and good, except I also want to show you what happens when we have queries sort of fighting to get memory on top of queries that are sort of fighting to get data pages into the buffer pool.
So I’m going to clear out wait stats because I don’t want any remnants in here. And now I’m going to kick off a second workload. And this second workload is going to just select counts from these tables.
Now, I don’t have any nonclustered indexes on these tables. So in order to do the count, we hit the entire clustered index. I realize that there are other ways to get a full count of like rows from a table that don’t involve getting a count from the table.
It’s not always realistic in all circumstances. Sometimes you need to filter certain values in the table, which querying DMVs will not allow you to do. So let’s let’s go.
Let’s run this by itself first for a minute. And let’s look here. Now, none of those queries are asking for a memory grant. So there’s nothing in here. But we’re going to start to see these page IO latch weights crop up.
And we’re going to see buffer pool memory start to come up as well, because we’re going to be reading data pages from disk into the buffer pool here. Right. Cool.
Now, let’s throw this one back into the mix. And as we do that, we are going to see this buffer pool slowly drop down as stolen server memory and SQL reservations crop up. All right.
So we went from 40 something gigs to 30 something gigs. Now we’re down to 20 gigs here. And I guess we went down to 24. So I think this stabilizes around here. But now we have a whole bunch of queries that are asking for page IO latch.
Well, that are emitting page IO latch SH weights. Right. So now all these queries that are running, they need to go read pages from disk into memory all over the place. And, you know, granted, this is a laptop with good local storage on it.
So the page IO latch weights aren’t going to be too, too terrible. All right. We, you know, we have about 2.5 milliseconds on average per weight, which isn’t awful. But, you know, these resource semaphore weights are still cropping up.
So right now, this is a, I think, what I would consider probably one of the more common scenarios that I see in SQL Server, where we have queries trying to put data pages in the buffer pool. They can’t because SQL Server has given chunks of the buffer pool out as memory grants to queries.
And so queries have to go out to disk, which is, of course, even with good local storage, not as fast as going to main memory. Right. And we can run this and kind of observe things a little bit as they go.
And, you know, we’re going to see things pretty, I don’t know, they’re going to stay pretty, pretty much the same where, you know, the buffer pool is going to stick around the 20 gig mark. The stolen server memory and, you know, all these reservations are going to stick around the 30 gig mark.
And, you know, the page IO latch and resource semaphore weights are going to keep sort of ticking up. And, you know, we’re going to see more waiting tasks. We’re going to see more wait time.
The average milliseconds per wait on resource semaphore, of course, over time has dropped down to about nine and a half seconds. Oh, there’s my, there’s the end of the green, there’s the end of the camera right there. That doesn’t look weird at all.
So, but anyway, that’s sort of the deal with that. So let’s kill these so that you can’t hear the laptop fans running in the background. And that’s probably, it’s probably good enough of an example of what happens when SQL Server is under memory pressure.
And, of course, these are the two, what I, in my experience, most common types of memory pressure, where we have queries that are fighting with each other to get memory to run. That’s a resource semaphore.
That’s memory grant stuff. And then those memory grants directly affect other memory consumers in SQL Server, like the buffer pool, because they reduce the size of the buffer pool while they’re running. All right, because memory has to come from somewhere.
SQL Server doesn’t work with pages directly on disk. Any pages, any queries that execute and ask for data, if that data isn’t in the buffer pool, we go to disk, we get it, we put it in, we start handing it out to queries, and then we go from there.
So, this is how SP, this is what you should look for in SP underscore pressure detector in order to figure out if there is indeed memory pressure on your server. Resource semaphore weights are a good sign.
Lots of page I.O. latch, underscore, probably most commonly, underscore SH and underscore EX. You might see those pop up to the top. Of course, we only see the underscore SH weights here, because all we’re doing is reading data.
We are not taking any exclusive latches on data pages to modify them. So, that’s kind of what you should look for. Of course, this is another good section to review in here.
If you are seeing any, if you see your buffer pool consistently a lot lower than, say, your max server memory setting or something like that, you see lots of stolen server memory, and the SQL reservations memory clerk might occupy a large percentage of that.
And then down here, you might see stuff along one of these lines where the available memory line drops off significantly. The granted memory line goes up significantly. You might see some queries that are granted memory, some queries that are waiting to get memory to run, and other stuff like that.
Another good counter to look at when you’re looking at this is the forced grant count. This happens when there is such significant memory pressure on the server. The SQL Server tells queries, well, screw you.
You’re just going to go run anyway. You’re just going to run with the minimum memory grant that you can get. So, that’s nice, isn’t it?
It’s really nice. Cool. All right. So, that’s it for this one. Thank you for watching.
I hope you learned something. Hope you enjoyed yourselves. Please, like and subscribe, if you don’t mind. If you have a YouTube account, if you have a Google account, you can do that.
And I will see you in another video sometime soon. Thanks 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.