sp_PressureDetector: Now With PerfMon Counters And Server Sampling

sp_PressureDetector: Now With PerfMon Counters And Server Sampling

---

Video Summary

In this video, I discuss two recent updates to SP Pressure Detector, a tool I’ve been working on for monitoring SQL Server performance. Despite the late hour—my usual night owl tendencies led me to stay up until four in the morning, sans any celebratory drinks—I managed to add some new features that should help you better understand your server’s current state and recent trends. The first addition is a suite of Perfmon counters, which I’ve ordered by object name and counter name for clarity, though feedback on this ordering would be greatly appreciated. Additionally, I’ve introduced the ability to sample your server over a specified period, allowing for more granular analysis without long wait times. While these changes are still in their early stages, I’m excited about how they can enhance troubleshooting workflows. As always, any suggestions or critiques are welcome at Darling Data; we’re dedicated to making our tools as useful and user-friendly as possible.

Full Transcript

Erik Darling here with Darling Data. And in this whirlwind video, I’m going to talk about a couple new additions to SP underscore pressure detector. And unfortunately, my audio tech who adds the echo to all of my storefront videos, store procedure announcements is out sick today. So you’re just going to have to deal with an echo-less store procedure announcement. So the two big things that I added to SP pressure detector recently, it was a weird night where I didn’t drink. And I felt very motivated.

And I ended up staying awake until like four in the morning, adding all this stuff in. I’m not going to walk you through the code because it’s kind of boring. It’s just like, you know, a loop and a go-to and a second pass and things for one thing. And it’s just a bunch of perfmon stuff for the other thing. And if in your whole entire long-legged life, you have never seen a sys.dmos performance counters code, you’re actually probably pretty lucky because everything you have to do to query that dynamic management view is real ugly.

Whoever designed that has a pure hatred for humanity that I don’t think can ever be matched, at least not by another human. If aliens ever invade Earth and start inflicting torture and cruelty upon the entire human race, like in that John Travolta movie where he’s an alien and really likes gold, I think it’s a Scientology flick. I can’t really remember. I can’t really remember. It’s something Earth, Battlefield Earth maybe.

Then maybe someone could equal, probably not surpass, but at least equal the hatred that whoever designed this management view has for humanity. So we’re going to skip that part because I care about you. We care about you a lot. That’s why I stay up until four in the morning dead sober writing these things.

So, yeah, we’re going to just dive right into it then. So I’m using SSMS 20, brand new release. Very excited about the fact that Azure Data Studio is no longer packaged in with SQL Server Management Studio 20.

There are a couple of downsides, though. One, I’m a little lazy, and I haven’t quite set up everything in here the way I want it to. And also, SQL Prompt does not yet detect SSMS 20.

I went to install it, and it was like, you need to have SSMS installed. And I was like, I have SSMS installed. So I guess it’s just a waiting game on that. All right.

Anyway, I’m going to give SP Pressure Detector a run like this. And I only have the minimum disk latency thing up here set to one so that results actually come back from it. Because I have good disks in my laptop, my very expensive laptop.

And gosh darn it, there’s not a lot of latency in there, which is both good and bad. All right. Okay.

So we’ll give this a run. And the new thing that I added to the results here is Perfmon counters. So I’m still tinkering with this a little bit. I’m not exactly sure if this will be the final set of Perfmon counters that I include.

I might add some. I might take some away. I might change the way that the results are delivered. But for the most part, I’m okay with what I get back now.

Like, it’s working, and it just needs some tinkering. Right? Because, again, you stay up until like 4 in the morning doing something. You know, you make some editorial choices that, you know, next day you might not agree with.

That goes for probably whether you’re sober or not. Probably more so if you’re not. But, you know, 4 in the morning does desperate things to the soul.

So right now I’m returning a bunch of Perfmon counters. The ordering of them I like, but I’m not in love with. I order by object name and counter name and then totals within that.

I tried just ordering by, like, totals, but I found that, like, the object name stuff being… The object name and counter name stuff being all mixed up made the results, like, confusing to read. And it was kind of nice having everything, like, grouped together and then ordered within those groups.

But, you know, you might like it a different way. And if you do, if you prefer it a different way, go to my GitHub repo. We can maybe figure out if there’s, like, some custom ordering that I can add in there.

Or maybe just you have a better idea for the ordering. We can go with that. I don’t have so much pride of authorship that I think I do everything right on the first try. Especially at 4 in the morning.

So, yeah. Feedback is always welcome at Darling Data. So, yeah, this section, you know, pretty normal Perfmon counter stuff. Again, you know, I’m not really sure if everything is going to stay in here.

Or if I might subtract some stuff or I might add some stuff. I don’t know how I feel about, like, having, like, metadata lock stuff in there. Like, there’s probably going to be some time when, like, it’s, like, the problem.

And I’ll kick myself if it’s not in there. So, for now, I’m just going to leave it in. But if there are any Perfmon counters you feel like they’re missing from this list, you can see if you go into the El Sorso Codo from this store procedure.

I’m only going to scroll down to the list of them because that’s the stuff that I would like feedback on. So, if there’s anything from this list that you feel is missing or that you feel is stupid, again, feedback.

Always welcome at Darling Data. We are voted by Beergut Magazine the friendliest and most willing to accept user feedback. SQL Server Consulting firm, the entire known universe.

And apparently that includes whatever the aliens from Battlefield Earth were, wherever they’re from, I’m not entirely sure. But, you know, anyway, this is the current list.

Let me know. Let me know. So, that’s the one thing, right? We added in Perfmon counters. The second thing that I added was the ability to take a sampling of your server. So, you can run SP Pressure Detector for, I believe, a tiny-ent number of seconds.

I think it was a tiny-ent. The cap on that is 255 seconds. If you really need bigger than that, I don’t think you’re doing the kind of emergency troubleshooting that I do. I usually don’t need more than, like, maybe 30 seconds at the high end, maybe 60 seconds at the very high end.

So, I capped it at a pretty small number because I didn’t want someone to enter, like, 2 billion in there and just, like, have a wait for going for 2 billion seconds, however long that is. The aliens from Battlefield Earth will probably have killed us all by then.

So, I’m going to kick off a query over here just so there’s a little bit of activity. I’m going to drop clean buffers just to generate some reads and whatnot. And then I’m going to run this.

And roughly 5 seconds later, depending on exactly where in the wait for we hit. Sometimes it says 6 seconds. I think it’s just like I hit, like, a weird edge of things. But that’s no big deal.

It’s close enough to 5 for me. It’s not like anything in here takes a second to run. It’s all fast, right? So, what we see up here, let me just tuck that away to buy ourselves a little bit more screen real estate. What we have in here, oh, I didn’t mean to cover that up.

I’m just silly of you. I want everyone to see the glory of the sample seconds in here. It’s very important stuff. You’re going to see wait stats.

And I have reshuffled the column orders a little bit just to make things, like, right and consistent looking. And I’ll name the same thing. But right now, the only three result sets that get the sample comparison are the first three that you see here. So, wait stats, file stats, and perfmon counters.

Nothing else right now does that. I just don’t know what else I would really care to do that for. Like, the memory stuff, I mean, I guess it would be okay.

But, like, you know, really, like, I just don’t see a lot of value in it. If you see value in it, maybe we can, you know, we can, again, user feedback is always welcome. We can figure that out.

But, like, any section that has the final column sample seconds in it, like these do, those are the ones that have sample comparisons. The rest are just kind of a snapshot of what’s currently happening. So, like, the buffer pool memory is current.

The 10DB info is current. All the stuff that you see down in here is current. The thread usage stuff actually might be kind of cool to have a comparison for. But, you know, you know, V1, 4 in the morning.

I wasn’t willing to stay up until 5 in the morning to get this going. So, that’s what we have there. So, this will tell you, you know, what weight stats you had during that sample. What drive activity you had during that sample.

And this is still ordered by the total read stall here, which might look a little bit funny. Look at 10DB with negative 26. That’s cool.

Thanks. Thanks, 10DB. File 7 having fewer reads at the end than at the beginning, I guess. Or, sorry, less average read stall at the end than at the beginning really threw me off there. I might have to do a little bit of fixing on this to get all the numbers kind of in a nice positive space.

But even though these files had the most reads, those reads happened very, very quickly. All right. No messing around in my laptop.

So, a couple new cool, I think, new things added to SP Pressure Detector. Again, this is all free open source stuff. You can download it.

You can, if you’re feeling particularly masochistic, you can read the source code. You can put these on your servers. You can run them. You can find your SQL Server problems. And if you need help fixing your SQL Server problems, young, handsome consultants are standing by to help you.

Like me. I meant me, not anyone else. All the other consultants are old and ugly.

You want young and handsome consultants. Right? Cool. Anyway, thank you for watching. Hope you enjoyed yourselves. I hope you learned something. I hope you find this code useful.

I hope that you’ll go download it, try it out. And, of course, give the old darling data some feedback. Because that’s what we thrive on. People saying, you’re an idiot.

Or, thanks. Either one. Kind of cool. Anyway. Thank you for watching. And I will see you in another video some other time. Goodbye.

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.