More Updates to sp_QuickieStore and sp_PressureDetector
Video Summary
In this video, I discuss updates to two of my favorite stored procedures: SP_Quickie_Store and SP_Pressure_Detector. Starting with SP_Quickie_Store, I added a user-requested feature that allows filtering queries based on whether they come from modules or are ad hoc pieces of code. This is achieved by checking the object ID in query store; if it’s zero, the query is considered ad hoc, while non-zero IDs indicate stored procedures, triggers, or functions. SP_Pressure_Detector received more attention with several improvements, including the ability to skip query results and weights, as well as adding CPU time to the result sets for a better comparison with uptime. I also truncated some decimal places in memory-related data points to make them more readable without losing precision where it mattered most. These updates aim to provide clearer insights into server performance and help you troubleshoot issues more effectively.
Full Transcript
Erik Darling here with Darling Data. Recently made amends with nice folks at Bare Gut Magazine. They offered me a bit of an olive branch, a peace offering. They named Darling Data the sexiest SQL Server consulting firm in all of America, Europe, Asia, Africa, I think I missed one. Definitely Australia, certainly New Zealand. So, you know, feeling pretty good about that, feeling nice about myself. Got my glow up. That’s the, that’s the, is that the right thing to call that? Anyway, um, I have some additional improvements, updates to two of my, favorite store procedures in the entire world. Uh, SP underscore, quickie store and SP underscore pressure detector. Um, let’s start with quickie store because, uh, it’s what the script is telling me to do. Uh, so the thing that I added here was actually a user request. Don’t get too many of those. That’s cause I write such good scripts, but, um, uh, what they wanted to do was to be able to get, uh, uh, queries, uh, queries, uh, either only from store procedures or only not from store procedures. And I guess I should take that back a little bit only from modules or only not from modules. See, uh, in, in query store, uh, there’s, uh, this is object ID column in one of the, one of the DMVs. And if the object ID is zero, then it is an ad hoc query or it is dynamic SQL or it came from an application or something. It did not come from a query.
from a module inside of SQL Server. If it came from a procedure or a trigger or a function or something like that, then, uh, the, the object ID will not be zero. It’ll be greater than zero. So, uh, I’m not really taking any steps right now to differentiate between the different types of modules. Um, it seems like it would just be kind of annoying to do. Uh, honestly, that’s it. It sounds annoying to do. I don’t want to do it. So, uh, but you can use, um, cookie store to look for either ad hoc pieces of code like this, where everything will say ad hoc, or you can look for, uh, modules. Now I’m going to, I’m going to let you in on something. Uh, I don’t, don’t, don’t currently have any store procedure stuff, uh, from any like demo workloads in my query store. So that came back empty.
But, um, I’m going to let you in on something here. The only thing that this checks is if, uh, the query type starts with a, mostly because, uh, some people are going to put a space in ad hoc. Some people aren’t. You can do either one. Um, I don’t, it doesn’t matter here. Uh, but, uh, people are going to type something like procedure or function or whatever in here. Uh, and, uh, I’m, uh, I’m only really checking if this starts with a at the moment. I’m not taking any heroic steps to, uh, to, to, to get you more information there.
Uh, for that I do apologize, but, uh, mostly people just want stuff that came from something or that came from nothing. That’s generally how it works. Uh, and of course, uh, the ever helpful help parameter will get you information on, uh, all sorts of things like new additions to parameters. Cool. Hope you like that one. Uh, we also have updates to SP underscore pressure detector.
Now, this one, uh, got a little bit more attention this time around, mostly because, um, uh, the more stuff I add to it, the more stuff I realize I don’t always want to see. There is some stuff that is opportune to see, uh, if you’re just trying to get, like, a complete picture. But when you’re really trying to drill into certain things, you don’t always want, like, sort of semi-unrelated results back.
So, um, one thing, one thing that I added, uh, was the ability to skip, uh, getting query results. And by that I mean getting a list of running queries. Uh, I’ve talked about in previous videos about SP pressure detector, how you can, um, uh, look at, find queries that are contributing to CPU and memory pressure currently on your server, which can be very useful if you’re continuously troubleshooting stuff.
Um, I should probably add a query-only parameter, but, uh, I just use SP who is active for that, so… I don’t know. Maybe I won’t. Uh, so we can skip queries.
Uh, we can also decide if we want to skip weights. Maybe we don’t want to see weight stats every single time. Uh, that’s a, it’s a interesting thing to have there. Uh, some other stuff that got added.
To the result sets. Uh, a couple tweaks that I made here is, uh, I’m going back to one of the, uh, resource governor DMBs that, uh, tallies, uh, all the CPU time that workload groups have used. And, uh, I’m getting now the hours of CPU time.
And I’m getting that because sometimes when you look at the hours of uptime, and you look at the hours of weights, the weights can look really low compared to, uh, how many hours your server has been up.
And that’s, like, if a server is just, like, busy during the workday, like, 9 to 5, and then does nothing from, you know, 5 p.m. to 9 a.m. Like, there’s not really a whole lot of overnight activity on there.
Uh, then the weights aren’t going to be accumulating so much then. And, um, I wanted to add CPU time to the results to kind of give a better picture of, like, okay, the server’s been up for this long.
And that time, queries have used this many hours of CPU, right? And since we have that, we can kind of figure out, like, sort of generally how the weight stats compare to, like, how long queries, like, how much CPU has been used, like, how long queries have been running, stuff like that.
So, uh, I mean, I know it’s not perfect because, you know, parallelism and other stuff, but it just kind of gives you a, like, a sort of, like, different data point, like, a different angle on, like, okay, like, you know, maybe, you know, hitting, you know, X amount of hours of some weight and, you know, like, some much larger number of hours of uptime doesn’t look that bad.
But, like, compared to, you know, how much time you’ve spent, like, actually running queries, how high is that weight? So some servers are, like, off the rails and, like, have, like, you know, like, 80 times, like, parallelism weights compared to uptime, like, like 100 hours of uptime and, like, 8,000 hours of parallelism weights.
And you’re like, oh, well, we have some things to talk about. But I wanted to add this for the servers where that distinction isn’t as apparent. Another thing that I did was I realized after a lot of time of using this that having nine decimal places in some of these results was not terribly useful.
So all of the not terribly useful nine decimal place results have been truncated to two decimal places. This used to be a whole mess of stuff going across here. I shortened that down to two.
I’ve also done that for the memory stuff because, you know, having nine decimal places of a memory grant isn’t saving anyone’s day. The places where I didn’t change that are in any weights that are related to time. So if you get query weight stats, or sorry, if you get, like, when you run a query, when you run the store procedure to get queries that are currently active, like how long the queries have been running, how long the queries have been waiting on, like, a particular weight, that isn’t truncated at all.
Like, I want you to know exactly, as exact as possible how long those things have been happening so you have a better picture of just how fast or slow a query is. All right.
Cool. Some other stuff that I did specifically for the memory section. Just to sort of keep with the basic theme of the script, I moved the section of queries that are asking for memory down to the very bottom so it matches what the CPU pressure section looks like.
So you get all of the sort of, you know, above-board information up here, you know, how memory is currently being used, if you’ve had any low memory stuff, what the current, you know, semaphore query, like, query memory grant information stuff is, and then finally the queries that are asking for memory grants.
I also added to this section. This section is a real, like, sort of, I don’t know, it’s a potpourri of different data points. And I really like it because it gives me a better idea of, like, what’s asking for stuff where.
So, like, having the total database size compared to the total physical memory in the box, knowing what max server memory is set to compared to the total memory in the box, knowing if lock pages and memory is turned on.
All this stuff in here is all sort of very useful when we start looking at, you know, weights correlating, like, if there’s pressure on the server, like, what can we do? Like, how, like, where is it coming from, right?
So one thing that I, a column that I added to this result set is the total number of forced, of, sorry, of reduced memory grants that have occurred on the server. This should generally be sent startup unless someone alters their workload groups and clears that.
I don’t think that’s a very common thing. But, you know, that can happen. I haven’t really thought of a good way to alert people if that’s been done or not, just because I think it’s going to be such a rare event that I just don’t think I want to deal with it.
So this is good here. Because, like, all this information going to, like, sorry, like, there is sort of what’s going on currently. And I think it’s very useful to have this data point knowing how many times a query has been forced to run with a reduced memory grant because it sat there waiting to get memory, like, the amount of memory that it wanted and couldn’t.
So I think that’s a pretty good thing to have. And that’s a pretty good data point to have. And there is, like, historically, how much has memory pressure sucked on this server? Some stuff that I added to specifically the CPU section.
One thing I did to make the, well, two reasons that I did this. One is so that it better matches other places where there are XML clickable columns. I separated this back out.
This used to be down in this result set, but I moved it out to its own result set. And another thing, like, A, it matches the rest of the server procedure a little bit better, where, like, if you run this to get everything, let me just go give you an example of why.
So if we run this to get everything, like, the tempDB info is a separate thing, and the low memory clickable is a separate thing. So now the CPU details are separate things.
So the other reason that I did that is because I added some new columns to this section. I added total active request count, so how many, like, requests are currently active. This column is in the DMV.
It’s not documented terribly well what it does, but it looked interesting to me. So I added it. We’ll see if it survives the test of time or not. How many, the total blocked task count.
So if any queries are blocked, we can get a count of those to kind of figure out, like, okay, well, like, you know, compared to how many things are, like, how many requests are there, how many are blocked? It could be very interesting to see that on servers where, you know, you’re troubleshooting a blocking issue.
And I also grabbed the total active parallel thread count. I think this will make thread pool demos even more interesting. So those are all available over here.
It’s a total active request through total active parallel thread count. These four columns are brand new. I also added a change a little bit. Well, I mean, I didn’t change the way I get thread pool weights.
I used to just select from the DMV if there were thread pool weights. And if there weren’t on, then you got, like, a blank result set. So now I’ve changed it so that if there are no current thread pool weights, it tells you there are no current thread pool weights.
And if there are thread pool weights, then you will see information on the thread pool weights. It will be a list of queries with, like, you know, their null session IDs, how long they’ve been waiting, and the thread pool weight. So that’s a little bit more specific there.
As usual, for all of these things, SP Pressure Detector has a very helpful help section. If you have questions, comments, or concerns about the store procedure, you should always use the at help parameter. And if you run into issues with any of these store procedures, please, pretty, pretty, pretty, pretty, pretty, pretty, please, use GitHub to ask questions, report bugs, request features, all that stuff.
My GitHub repo is listed pretty much everywhere in the world, including, you know, usually where you have to go get these things from. It’s not like you can download them from my website. Go to GitHub, get the most recent version.
And then have a lot of fun. That’s it. All right. Cool. Well, those are the new updates, changes, additions, no subtractions to SP Quickie Store and SP Pressure Detector. I hope you like these store procedures.
I hope you find these store procedures to be of some utility to you. You know, I spend a lot of time working on them. And, you know, if I could only use them for me, well, I don’t know.
Maybe the world would be a less safe place for SQL Server. I don’t know. Anyway, thank you for watching.
And if you enjoyed this video or you enjoy my other videos but not this video or some mix and match there, remember to like and subscribe. You know, it’s the thumbs up and the bell thing so that you can be alerted to all of my miraculous, wonderful, now officially awarded sexiest SQL Server content on the planet. And I’m going to record some more stuff after this because it’s Saturday and I finally don’t have anything else to do.
Oh. All right. See you soon.
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.