Updates to sp_QuickieStore and sp_PressureDetector!
Video Summary
In this video, I’m Erik from Darling Data, and I’m excited to share some updates to my free open-source stored procedures, SPQuickieStore and SP_pressure_detector. First up is an enhancement to SPQuickieStore, which now allows you to query all databases with Query Store enabled without specifying a database name. This feature can be particularly useful when dealing with environments where multiple databases have the feature turned on or when you’re unsure which ones do. I haven’t fully fleshed out the ability to specify a list of databases to include or exclude, but based on initial feedback and usage, I may add this in the future if it proves popular.
Next, I’ve added a disk latency report to SP_pressure_detector. This new feature provides detailed insights into disk performance metrics such as read and write stalls, which can be crucial for identifying potential bottlenecks, especially when monitoring memory pressure or CPU usage. The addition of these metrics is particularly helpful in scenarios where heavy TempDB activity might skew average values, allowing you to get a clearer picture of the overall system health.
Full Transcript
Erik Darling here with Darling Data. And we’re going to talk about a couple oh so exciting updates to SPQuickieStore and, sorry, SP underscore, underscore, underscore, QuickieStore and SP underscore pressure detector. I apologize for the little hat line on my head. I was out working on some stuff for me. I was out working on some stuff in the backyard. I’m a little sweaty, but, you know, this isn’t Smell-O-Vision, so I don’t think we have too much to worry about there. But, um, short video. Just because I haven’t recorded anything in a bit, I got a bunch of stuff in the queue to record, but, you know, life gets busy and hazy sometimes, so I don’t always have as much time to do this as I’d like. But anyway, in order to keep my promise of a brief video, we’re going to look at a couple of the things that I’ve added to my wonderful free open source stored procedures. Alright, so the first one is SP underscore, underscore, QuickieStore, the teeny bit of a tongue twister. I added a parameter, and I haven’t fully incorporated the feature yet. And by that, I mean, I haven’t added a way to, like, give a list of databases to get or give a list of databases to skip. I’m going to see how much this gets used before I spend what would probably be a silly amount of time dealing with that. But anyway, if before with SP QuickieStore, you used to have to give it a specific database name, and there are sirens out there, hopefully, hopefully, hopefully, that adds to the charm of my, my straight out of New York recordings. But it used to be you have to, you used to have to give SP QuickieStore a specific database name to look at. And for various reasons, like people having quick query store on in multiple databases, not knowing which databases have it.
me being lazy, I’ve added a way to just go look at all of the databases that have query store properly enabled, and return data from those. So if we execute this, we are going to get back a slightly longer result set than we normally would. And we’re going to get back, of course, data from multiple databases. So it’s not going to be all that interesting, because I don’t do it. a lot of work in the other databases on my server, you know, like I have my clean copy of the Stack Overflow database that I don’t allow any updates to. So if I mung anything up into my actual copy, I can just reset that data pretty easily. And of course, like everyone else, I have a database called crap, that I do random crap in a lot of what ends up in here is, of course, like, like, if anyone is ever so kind as to, you know, add, create table, and whatnot statements to a Stack Exchange question over on dba.stackexchange.com, then I use the crap database to sort of sandbox all that stuff.
So like I said, there’s not a whole lot of interesting stuff in these other databases. But the Stack Overflow 2013 database does see a bit more action than the rest. Well, actually, it sees all the action, because that’s where I do the majority of my my demo writing work, because that’s the one that I will use for training, pre-cons, blog posts, and all that stuff. So might as well keep it all as homogenous as possible. The other incredibly exciting addition, news from 2005, is I’ve added a disk latency report to SP pressure detector. This will only come up if you have, I should probably just stick this in the script, so everyone’s aware that what to check equals all. Well, that doesn’t say all, does it? That doesn’t say all either. That says all. Rocking and rolling. So what to check has to be set for all to pull this back. If you’re looking for just CPU or just memory, this check and the tempdb check get skipped.
So by that I mean if we run this for, let’s say CPU, we will just get weight stats back, and then we’ll get information about the CPU stuff. I’m a little on the fence about skipping weight stats, because often I use those in demos when I’m looking at something specific. And, you know, I kind of like having those show up no matter what. But, you know, if you have differing opinions, feel free to hit me up. But if you use CPU or memory, then things get skipped. But if we do all, we get a bit more back. We get back the weight stats. And then down here, this is the new bit. And by new, I mean, I mean, this stuff has been around in SQL Server DMVs for just about ever.
I was just, I don’t know, somewhere between hesitant and lazy about adding it in here, just because, you know, usually when I care about CPU or memory pressure, disk is a much smaller factor. But I did decide to add this in, just in case disk is also, like, especially for memory, if we care about, like, you know, let’s say we really care about how much page IOLatch is going on on the server, or how much write log is going on on the server. And, you know, there’s, like, a lot of it, and, like, you know, it’s, like, slow for something, like, it’s just way more of that weight than we would care to see generally.
Then I do kind of want to look at disks to see if there are any, you know, sort of inherent bottlenecks there. My first implementation of this, I only, I had, well, actually, the default value for this is 100 milliseconds for minimum disk latency MS. The default value is 100 milliseconds. I’m using 20 just to make sure stuff shows up here.
But this section here sort of mimics some of the other sections in here where it tells you how many hours we’ve been up, which drive the files are on. And I know I’m a bad DBA because I have everything on the C drive, but it’s a VM. Assume me. The disks are the disks. It’s not like they’re going anywhere special.
The database name that the files belong to. And then database file detail. So this tells us which file is involved.
If it’s a data file or a transaction log file, you see that changes there. And then it gives the full path to the file. I used to have this so that database name was sort of incorporated into this sort of string, this built-up string of details.
But, I don’t know, it kind of made it a little too busy, and I couldn’t think of, like, a good, like, I used a backslash to separate it. And then, you know, I figure a lot of people want to filter by which database they look at if they, like, paste this into an Excel file or something. So I left this separated out.
You know, I’m not crazy about it either way, but I don’t know. That’s just what I did. So deal with it.
Then kind of getting into what we get back. We get the size of the file that is listed here. We get the average read stall.
We get the average write stall. We get how many gigabytes have been read, how many have been written, the total read count, and the total write count. I don’t really care about the total, like, minutes or seconds or milliseconds of stall in these cases, just because the average is usually what people care about. Like, does this thing suck on average?
Like, how slow are the reads and writes on average from this? So I left that in, and then these are just sort of, you know, to get a sense of, like, how busy things are. I work on some, or rather I have worked and work on some systems where there have just been, like, terabytes of writes to TempDB for databases that are, like, 100, 200 gigs.
And in those cases, I do kind of like want to be like, well, what’s going on with TempDB? Because that’s, you’re doing a lot of stuff in there. What’s all the stuff you’re doing in there?
And that especially might relate to, you know, if we have, I don’t know if there’s any that are going to show up, but if we have any weights up here that are potentially related to TempDB contention, then we might care to know, you know, that stuff about TempDB. But, you know, like a lot of other DMVs and SQL Server, you know, the averages include when terrible things go on. So, like, with TempDB, if you’re the kind of born-in-the-wild fool who still does index rebuilds or, you know, you’re the kind of smart person who, you know, does DBCC check DB, you might see a lot of TempDB activity at some points on the server that might make the averages spike up.
But these, like, the averages here aren’t necessarily going to be related to user workload. So, like, I don’t have, like, a great way of distinguishing that because this is just a snapshot. It’s not like a time slice of when things went up.
If you need that stuff, get a proper monitoring tool. Should a proper SQL Server monitoring tool ever come into existence again, it would be nice if you got that and used it so that you could see that sort of thing. So, there we go.
There we have it. Two exciting new features in my store procedures. I’ll put the links to the GitHub repos for these things in the video notes. And, I don’t know, happy troubleshooting, I guess.
Thanks for watching. I hope you like, enjoy, use, find some value in these store procedures that I have spent many, many hundreds, maybe even thousands of hours in my life working on. If not, I don’t know.
I hope you’re using, I hope it’s because you’re using a different data. I hope it’s because you use Oracle or Postgres and you just, you just can’t run these there. Anyway, like and subscribe if you’re into that sort of thing.
If not, then, I don’t know. You’ll just have to wait until YouTube randomly recommends one of my videos to you based on prior search or viewing history. 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.