sp_PressureDetector: Which Queries Are Causing CPU and Memory Pressure?
Video Summary
In this video, I continue to explore the insights provided by SP\_pressure_detector, a powerful tool for monitoring query performance under CPU and memory pressure. We delve into specific sections of the output that highlight queries running during times of high CPU and memory usage, showcasing how these queries can be identified and analyzed in detail. By focusing on these particular aspects, we gain valuable information about which queries are causing bottlenecks and how SQL Server manages resources under stress. I also take a brief break to joke about my lunch plans, which involve an adventurous approach to eating a whole live chicken—a bit of light-hearted humor to keep things engaging while discussing some serious technical topics.
Full Transcript
Erik Darling here, representing Darling Data. And this video is going to be continuing to look at what you can see about queries that are running in SP underscore pressure detector. Now, we’re going to sort of replay some of the demos that we looked at for the CPU pressure and the memory pressure stuff. because one set of, I mean, well, I guess technically two sets of results that I didn’t really explore much are information about the queries that are running while things are going poorly. So, we’re going to do that. We’re going to have fun. We’re going to have a lot of fun. And then we’re going to eat some lunch because it is getting close to that time and, uh, I don’t know, really. I don’t really like to, uh, I, I, I, I, my balanced breakfast was either three or four, uh, double shots of espresso. So, um, my balanced lunch is probably going to have to be chicken or something. A live chicken. A whole live chicken. Not, not, not de, not de-boned, not de-feathered. I’m just going to, I’m just going to rip it off.
I’m just going to rip into it. Chupacabra. So, anyway, uh, this video is sponsored by New York City tap water. Hmm. It’s not radioactive yet. What else can you say? All right. So, important stuff. Uh, we’re going to use just this setup. We don’t need to do anything else on this one. Uh, cleared out weight stats.
We don’t have to worry about that. Let’s make sure this is highlighted. So, future executions do not also clear out weight stats, even though they’re not really important for this, for this part. Uh, you know, whatever. We’re going to find various ways to enjoy ourselves. All right. So, let’s kick off, uh, that CPU pressure demo. And let’s unhighlight that. Let’s be really counterproductive right off the bat. And we’re going to give this a few runs just to kind of, uh, let things warm up a little bit.
And, you know, we’re going to see the weight stats pile up for, like, you know, CPU related stuff, which is, you know, about what you’d expect for a CPU pressure demo. But let’s scroll down a little bit here. And let’s look at this section. So, this section will, uh, tell you kind of more specifically about CPU related stuff, uh, on a server. And one thing that I want to show you is, uh, this, these two columns over here. So, uh, under intense enough CPU pressure. I just want to go back a little bit.
So, this section up here will only show you queries that are asking for a memory grant. Uh, if queries are not asking for a memory grant, they will not show up here. There’s a, there is some differences in the results. And, you know, you can use the at what to check parameter to look at CPU or memory or both or, you know, whatever. But as we scroll down through here, under significant enough CPU pressure, notice that we have these queries that all of a sudden, uh, this is all the same query running.
This is my system. And there are so many of these things that cannot possibly be terribly contaminated by other stuff. And we can scroll back over to the query text to see this is all the salon, the select count query that I’m executing. Right. And we have some queries that under significant enough CPU pressure, right, where we’re hitting thread pool weights, right, for a long period of time in here.
Uh, the notice the dop suddenly drops off. All right. I’m going to cancel this just so it’s sometimes this one, well, you know, sometimes it takes a little while to kill other times. It’s just immediate. So that’s, that’s nice work, work quickly while recording.
That’s always a, always a welcome, always a welcome surprise. So if you remember the video that I posted, uh, talking about how bad of a feature dot feedback is in SQL Server 2022. Um, now in preview in Azure, um, now in preview in Azure, the number of Microsoft features for SQL Server 2022 that are in preview as of RTM, sort of astounding.
You know, like, uh, Microsoft’s like, ah, well, just preview in prod. Why not? Uh, test that for us. We’re afraid. It’s a little, it’s a little amusing.
But, uh, one of the, one of my main, one of my main beefs, one of my main grievances with the dot feedback feature is that, uh, it’ll, it’ll downgrade and downgrade and downgrade, uh, but it will never go to dot one. And here we have SQL Server on its own, under memory pressure, uh, downgrading a whole mess of queries from dot eight to dot one because we did not have significant enough CPU resources. And if we scroll over back this way, and I’m just going to highlight this bottom row because we know this is dot one and we can make a reasonable assumption that this is the select count query that we’re running from a SQL query stress.
If we look at the query plan for it, SQL Server is still showing a fully parallel execution plan for this query, even though at runtime it is only getting one thread. So, dot feedback people at Microsoft. You can do it. You can make it happen.
You can do something smart with this feature. I don’t know why I’m whispering. This isn’t a secret. The feature stinks.
Uh, so there’s that. All right, cool. So other stuff we can see about, uh, queries executing in here. Uh, you know, session ID, database name, how long they’ve been running for, query text, query plan.
Uh, you can get some statement offset and whatnot from this just in case it’s part of a, you know, larger batch or whatever. Uh, I like to give you the plan handle in case you want to go look stuff up. Uh, you get the status if it’s being blocked.
Uh, any, you know, important weights for these. You see some of the, um, CX consumer weights up here. These queries run for 20 seconds and they’re just pounded in CPU. So, um, you know, that’s, that’s fine.
Uh, you know, other, other interesting stuff. Uh, CPU time, elapsed time, uh, reads, writes, memory, isolation level, and of course, dop and parallel worker count. So, uh, pretty good set of, uh, information there if you want to, uh, get information about queries that are, uh, using a bunch of CPU.
Now, let’s switch over a little bit and let’s look at the, uh, memory pressure demo. It seems like a reasonable thing to get to. And let’s, uh, let’s execute this.
And let’s run SP pressure detector. Let’s give this a couple few seconds to warm up. We’ll know that, um, we’ll know that things are starting to warm up when we start to see, uh, resource semaphore weights show up in here, right?
That’s when we know things are getting good. Uh, more some of the last demo, we do have, uh, uh, the thread pool weights piling up here, right? We have about eight and a half seconds per weight.
Not a good sign, right? CPU pressure sign. But now let’s, uh, scroll down and let’s stop a little bit earlier than before. And let’s look at the section that gives you queries that are asking for a memory grant.
Again, no memory grant. You don’t show up here. This thing specifically looks for stuff that are, these queries are asking for memory grants. I didn’t want to mix the two because I kind of show you different things from each, right?
So session ID, database name, how long it’s been running, query text, query plan, yada, yada, yada. Where these, where these differ is, uh, these show you queries that have, well, when they requested memory, uh, when they were granted memory, how much memory they requested, uh, how much, uh, was actually granted of that request. Uh, of course the ideal memory.
So the ideal memory was way higher than what was granted, uh, the required memory. So like, um, if you end up with a, uh, sorry, I’m looking at the wrong section. So if you scroll down a little bit and you look in this section and, uh, you see stuff in this forced grant count row, uh, this is the required memory.
So if you have a query that gets forced to run with a lower memory grant, that’s where you’ll see that. Uh, I’m just going to kill this off because boy, oh boy, is my, are my computer fans tired? Uh, you’ll see how much memory of the grant was used.
I should maybe rearrange these columns a little bit. So they’re a little more, a little more lined up with things. I was actually, you know, I was going to make a couple of notes here, mental notes.
Uh, I would like to have a date diff on the request and grant time to see if there was, uh, there was any significant, uh, delay in a query asking for a grant and getting for a, getting a grant. That’d be nice, right? I should probably put that, uh, probably put a difference between, uh, well, I don’t know.
We’ll figure some stuff out. I don’t know if there’s a difference between like used and requested or used and granted. That seems like a good idea.
So if you scroll over a little bit further, uh, you’ll see some information about, so like if a query got a memory grant, these will be null. If a query is waiting for a memory grant, these will not be null. You’ll see which QID they’re in, the weight order of the queries, zero based counting.
How nice. You’ll see if a query is the next candidate, which, you know, could change from Q to Q. And then, uh, how long the query has been waiting for a memory grant, which, I don’t know, maybe that’d be interesting to factor into the, uh, the, the, the weight, waiting for a grant equation.
Wouldn’t it? I think so. So we got that.
And then we see some of the, the queries that are up there running, that got their grants, because this stuff is null, waiting on CX consumer, parallel queries. Yeehaw. Uh, but the queries that are waiting are all waiting on resource semaphore.
Hmm. And, uh, you know, some other information that looks a little bit like below the DOP, reserve work account, use work account. So there’s sort of like a mix of information in here.
But again, there’s enough different that I didn’t want to sort of pollute one result set with data from another, because we would end up with a scroll bar that cuts off an arm and a hand from everything. It would not be fun or enjoyable. So, uh, that’s that there.
Now, do you always have to look at both of these? No. But, uh, as a whole, these results can give you a whole lot of great information about the various types of pressure that, uh, SQL Server can be under. Right.
CPU and memory, those are the most common. Um, and you can also see some good information about the queries that might be causing that. Um, you know, the plan handles are good things to copy off for queries, so you can go do some digging later. Of course, the query plans and query text are good things to dig into there.
But you can get more information via the plan handle from various sources. Uh, sort of calling back to another video about SP underscore quickie store, you can use that plan handle, uh, to look up queries there. So, uh, all fun stuff in there.
And, uh, I don’t know. I think, I think that’s about it for that. It was just those two results that I wanted to go over because, uh, the other videos about, uh, CPU and memory pressure specifically cover the rest of the results. And those were about 20 minutes each.
And this one’s going to be about 15 minutes. So, you might get some pretty good understanding of why I didn’t mix everything in because then you would have been sitting around for 45 minutes watching me on YouTube double or triple speed. Or I would have sounded like a, one of those sped up soul samples trying to explain CPU and memory pressure to you.
And that, that is not a good, not good for my voice. My voice does not do well with, with the speed up. Cool.
Cool. So, again, SP underscore pressure detector might be the finest or procedure ever written. Uh, I do encourage you to look at the, uh, the source code.
Behold its majesty. Behold its elegance. And once you do, I think you’ll understand why Darling Data is indeed the hottest, hippest, most fire SQL Server consultancy on planet Earth. And again, I’ll get the lawyers out if you disagree.
So, anyway, thanks for watching. Uh, I hope you, uh, enjoyed yourselves in some way. Um, hope you learned something.
If, uh, you enjoy either something physically about me or my voice or my SQL Server abilities, then, uh, I would encourage you to like and subscribe to the channel. Um, if not, well, have a, have a, have a great weekend. You find folks working on the Dopp feedback feature?
Well, you, you, you can do a serial plan. You can do, you can assign a single thread to a query with a parallel plan. It does not require a recompile.
You can do it. I have faith in you. I haven’t met you. New crop of summer interns. But I have faith in you. You can do it.
You fix generate series. You can do anything. All right. Cool. Thanks for watching.
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.