Query Tuning Archives | Page 80 of 147

How I Use sp_BlitzFirst To Profile SQL Server

Posted on June 28, 2023January 22, 2026 by Erik Darling

How I Use sp_BlitzFirst To Profile SQL Server

Video Summary

In this video, I delve into the practical applications of SPBlitz scripts in my day-to-day consulting work, specifically focusing on SPBlitzFirst and SPPressureDetector. I share how these tools help me identify performance issues and gather valuable data points that guide further troubleshooting. I also discuss the nuances of running SPBlitzFirst using SendStartup or sampling for a period of time, highlighting why I prefer the sampled mode with seconds for its detailed insights. Additionally, I explain how SPBlitzFirst complements my work by providing a snapshot of what happened over a short timeframe, while SPBlitzHumanEvents helps dig deeper into the root causes of performance issues.

Full Transcript

Erik Darling here with Darling Data and continuing on with how I use the SPBlitz scripts in my day-to-day consulting life along with all the other store procedures that we’ve talked about in this series. Some mine, some others. It would be kind of, well, I don’t know. Can’t not talk about these ones. One thing that’s bothered me consistently over the last few videos is that I am always using my O-Stress login. He says SSMS Windows for some reason. I forget what I was testing, but now I’m just like permanently logged in as O-Stress.

Usually I’m SA, because I’m like the coolest person who uses my demo VM, so whatever, but okay. So, SPBlitzFirst is pretty cool. A lot of the times I use SPPressureDetector because it digs in a little bit further on some things that I usually end up having to troubleshoot.

It gives me some additional data points and, you know, interesting things to look at. But I still do end up using SPBlitzFirst for a number of different reasons. And also SPBlitzFirst in the sampled mode with the seconds is sort of what propelled me to write SP underscore human events because, you know, SPBlitzFirst tells you what happened over a period of time.

It doesn’t tell you how it happened, right? It doesn’t tell you what caused stuff for everything. So, I did, like, that’s, like, a lot of why I wrote SP human events is like, okay, well, if, like, I spot something just absolutely crazy in that 10 second window, like, then I need to dig deeper and I want to be able to do that quickly without, like, you know, like, like extended event gooeying things and being annoying and all that other, having to, like, parse XML on the spot and all that other glorious nonsense that you have to do to work with the profiler’s replacement.

So, I don’t know. So, I don’t know. First easiest way to run SPBlitzFirst is just to use SendStartup.

You’ll get information about, like, what weight stats you’ve accumulated, what your files have been up to from a read and write perspective. You’ll get a bunch of perfmon counters. You’ll get Holy Mother of God, so many perfmon counters.

I gotta be honest with you, I rarely look at this except for, like, the bottom, like, couple few lines. The stuff that I usually look at in here is batch requests, compilations, and recompilations. There’s just a lot of perfmon counters in here, and I just don’t always care about all of them enough unless something, like, really just jumps out and screams, like, hey, I’m real weird.

Fix me. But there’s a lot of stuff in here that I just tend to skip over because it’s just, it’s sort of information overload a lot of the time.

But the other way to run this is to sample your server for a period of time. And the expert mode parameter is really extra useful here because this is what gets you a whole lot of extra result set stuff that you don’t get if you just run it for a number of seconds. So, the first thing that it does is actually runs spblitzwho.

It’s actually the first thing, the last thing that it does. spblitzwho is sort of a slightly less configurable version of spwho is active. Adam Mechanic once told me that spblitzwho is mostly right, and I was pretty happy with that.

So, if there were anything running on my server, it would have appeared up here, and the results would have been mostly right. So, that’s pretty cool. The second section down is, I believe, what Mr. O calls the headline news section.

So, anything of particular note that spblitz first checks for would show up in here. So, kind of coming back to, again, what sort of got me to write sphumanevents was, you know, just again, I’m going to reuse the example. Let’s say we had 400 batch requests a second, and like 399 of them either compiled or recompiled.

Then I would use sphumanevents to look at compilations or recompilations and track down exactly which queries that was happening to so we could figure out why it happened and how to fix it. Right? So, if I think that might be something that I did with sphumanevents to dig in on that. You know, other stuff that, you know, would show up in here would be like blocking, long-running queries, things like that.

So, there’s all sorts of stuff that, like, again, like it tells you what happened but not like what caused it or why it happened. Right? So, like that’s when I would use sphumanevents to dig in on like blocking, compiles, recompiles, waitstats, or query performance. Speaking of waitstats, the next section down is where you get all the waits that happened during that 10-second period.

Again, my server isn’t terribly busy so there’s nothing really all that interesting in here. But, you know, we do have all that. If you’re, any of your, anything happened to any of your drives, whether reads or writes, that would show up here.

We would get all the perfmon counters that happened during that window. And it would also, if we, you know, decided we really wanted to like examine the plan cache, we would be able to do that. If I really wanted to look at the plan cache though, I would probably just use spblitzcache.

But also like I spend a lot of time these days avoiding the plan cache. I spend way more time in query store doing stuff because that’s where there’s just far more interesting things going on. Better historical data, stuff like that.

And then, of course, like I said before, the final thing that it would do is run spblitzwho. And if anything, we’re running here, it would show up in this section and again be mostly right. So that’s pretty much the way that I use spblitzfirst these days.

Nothing, nothing, that’s been like pretty much the way I’ve used it like forever and ever. Sometimes I might sample longer or shorter periods of time, kind of depending on exactly what I’m trying to catch happening. But, you know, this is a pretty good starting place for you to run spblitzfirst to start examining stuff on your servers.

So, yeah, there’s all that. Anyway, thank you for watching. I hope you vaguely, mildly enjoyed this video in some bizarre way.

I hope you enjoyed yourselves. I hope you’re wearing pants. Kinda.

Maybe. If you like this video, feel free to hit the like button. If you like this, and also if you like this video, you should hit the subscribe button so that when I make more videos that you will undoubtedly like, you will be alerted to them instantly.

That would be, that would be the wisest choice that you ever made in your life. Humbly. My humble opinion.

That would be the single smartest thing you could do. It wouldn’t be, you know, learning a trade or it wouldn’t be investing for retirement. It wouldn’t be taking care of yourself, dieting and exercising, living a healthy lifestyle.

It would be subscribing to my channel. Alright. So, I’m gonna do some more of these, I guess. I’m gonna start uploading this one.

No time like the present. So, yeah. I will see you in another video. At some point when Hyper-V Manager goes away. Thanks.

Ah, good. Alright. Before I stick this landing too hard, thank you 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.

How I Use sp_Blitz To Get The Results I Want

Posted on June 27, 2023January 22, 2026 by Erik Darling

How I Use sp_Blitz To Get The Results I Want

Video Summary

In this video, I continue my series on the store procedures I use in my day-to-day consulting work to help clients identify and resolve issues with their SQL servers. After covering a range of scripts like SP Pressure Detector, SP Quickie Store, SP Human Events, and SP Human Events Block Viewer, as well as diving into SP Who is Active, I now turn my attention to the SP Blitz scripts. Created during my time at Brenoza Unlimited, these scripts have evolved significantly since their initial development. While some of the code formatting remains a point of pride and shame for me, I’ve learned that clean, readable code is essential for effective troubleshooting. In this video, I focus on two key features: using the `check server info` parameter to gather comprehensive information about the SQL server environment, and leveraging the ability to skip checks for items that don’t pertain to my specific consulting needs. This approach helps me present a more focused set of results, making it easier for clients to prioritize their attention on critical issues.

Full Transcript

Erik Darling here with Darling Data. And I’m going to pick up kind of where I left off with my series of videos about how I use different store procedures in my day-to-day consulting work to help people figure out what’s wrong with their SQL servers and fix it in exchange for money. That’s my business model. Take it or leave it. So, we’ve covered SP Pressure Detector, SP Quickie Store, SP Human Events, and SP Human Events Block Viewer. We’ve covered SP Who is Active. A lot of SPs have been covered in these videos. So many SPs. It’s hard to believe how many SPs there are. Everyone thinks they’re special. Anyway, I’m going to spend the next first, I guess, five videos talking about how I use SP Blitz scripts. Of course, you know, I was an employee over at Brenoza Unlimited for some years. And while I was working there, I did a whole lot of work on the scripts, you know. Some I’m more proud of than others, I think. If I had to pick one thing to be particularly embarrassed about, it’s the way that a lot of the queries are formatted in there.

They’re real ugly, the way I wrote them. And, you know, it’s kind of a mark of shame for me. But you know what? Code formatting is one of those things that you evolve over time. And I am just in a more evolved place code format-wise now than I was then. And, you know, I think then I was just happy if it worked and worked relatively quickly. And now I’m very picky about all sorts of things like not having tabs and not having white space and, you know, having things sort of spread out so you can read them easier, not just have like a bunch of stuff crammed on one line.

You know, like, don’t tell me just put it in a CTEto make it more readable. Most definitely doesn’t work. But, yeah, anyway. My far and away favorite parameter, and one that I think I wrote at least a couple few of the checks in here to deal with, is check server info equals one.

And check server info equals one will get you all basically everything in this list. Everything in this list will show up if you use check server info equals one. You can read the list yourself, but, you know, just because I don’t want to have a 30-second video.

OS details, if you have lock pages and memory enabled, if you have instant file initialization enabled, which service accounts equals server and agent run under. I think browser too, but I forget. How many CPU cores you have, like kind of like what their layout is, NUMA-wise.

You know, like, you know, how many, like if you have two core, two sockets and 10,000 cores per socket, you owe Microsoft your life. How much memory is in the server, kind of how that memory is assigned to CPU cores. The patch level of your server, if it’s part of an AGR cluster.

The last time it was restarted. How much drive space you have and how much is available. The last time your server was restarted. I think, wait, did I already say that? I think I already said that.

No, okay. Cut, cut, cut, cut that. What did they say? Anyway. If your server is virtualized, we’ll try to give you some information about that. If you have any CPUs and or memory that are offline that are not available to SQL Server because of you installed the wrong version of SQL Server and you have, like, you’re limited to 20 cores or someone set up affinity masking or something else goofy, then that’ll be that. That can be particularly helpful for folks on standard edition who have done something asinine with their VM, like, give it 16 cores, but give it one core per socket for 16 cores.

And then SQL Server is like, well, I can only see four of them. You only have four cores now. The other 12, who knows?

They’re just sitting there twiddling thumbs or whatever CPU cores have for twiddling. What power plan your CPUs are operating under, that’s obviously probably far more important for, like, a physical server than a VM or for anything in the cloud. But, you know, I am a bit superstitious when it comes to the balanced power mode thing.

So I always like to make sure that any server that I touch runs in high-performance power mode. You know, you can go green with your tablets and personal laptops and, you know, you can compost something to make up for it. But when CPUs cost $7,000 per core, I want them spinning at full speed.

Sorry about that. Mother Gaia. I don’t know. There’s probably worse offenders out there in the world.

It’ll tell you if you have multiple SQL Server instances stacked on one server, if you have any of the SS, AS, IS, or RS components installed. And then if you have any significant weights, it’ll give you, like, the top three or something, or top five or something like that. I forget where the top is in there.

No one would care if they were the bottom weights. So check server info. Pretty awesome. Now, one thing that I end up doing with servers after I’ve gotten to know them a bit is using a really often overlooked feature where you can skip checks for things you don’t care about.

So you need to create a table and a database. And you need to have three columns in that table. Server name, database name, and check ID.

And you can tell SP Blitz where that table lives. And it will skip for whatever server, database, and check ID you put in there. Whatever you configure it to do, basically.

So for this, I wanted to check everything in every database except these things, right? So, like, I’m going to leave server name and database name null because I want these checks to apply no matter where we’re doing them. And, like, there are things that, you know, really just don’t, like, affect my consulting.

Right? Like, this list of things in here is never, like, if I start pointing, like, any of this stuff out to someone and they’re like, yeah, but my server is on fire and, like, there’s 30,000 block processes. Can we focus on that?

I’ll be like, oh, yeah, it’s probably a good idea. So this is just stuff that is not for me. It might be important to you if you need to audit certain things, if you care about who owns a database and privileges and all that other stuff. And this is for you.

This is not for me. So I’ve already run SP Blitz, and we’re going to look at the results with all that stuff skipped. And I know that you’re probably going to be pretty horrified by the things you see in here. You’re going to say, Eric, you are a terrible database administrator.

You have not taken backups. You have not run dbcc checkdb. You have dropped clean buffers. Your tempdb is on the C drive. And I will say, yes, it’s a VM. And it doesn’t matter much for me what happens to it.

But I have this stuff everywhere I go. So, yeah, there’s just stuff that is just not going to matter to me. And I like to skip that stuff.

And I like to keep a nice tidy set of results there that are generally focused on things that would make a difference to people. Or at least for what I do anyway. I don’t know.

Let’s see what else we have in here. I don’t know. So, all right. So, you know, to be fair to me here, there’s some stuff that I leave. Some stuff that I leave is in the incorrect state so that when I run this, I know that checks are working and things like that.

So, you know, on the occasion that I do work some with SP Blitz, I like to make sure that the checks that I write will fire correctly. So, I tend to sometimes leave things in not as good a state as they could be. So, I don’t know.

There’s not a whole lot of interesting stuff in here, is there? PBS pre-allocate. Ooh, la, la. Yeah, look at that. Look at that. I got all the smart settings. I got instant file initialization. I got log pages and memory.

Yeah, I’m doing well. I’m rocking and rolling here. Anyway, those are the two things that I like to do with SP Blitz. I have to use the check server info parameter.

And once I’ve gotten to know a server and I know what doesn’t matter to me, I like to use the ability to skip checks. So, I don’t get overwhelmed with 10,000 lines of things that don’t concern me. So, yeah, that’s that.

Anyway, I’ll cover some of the other Blitz scripts that I use. I think, I forget exactly which ones I’m going to be covering at this point, but there’s going to be four more of them. Coincidentally, there will be four more of these to fill five working days of blog posts.

And you can make of that what you will. I don’t want people watching SQL Server, getting SQL Server emails on the weekends. It’s rude.

Rude. Anyway, thank you for watching. If you like this video or you think you would like other videos better, which you might, it’s possible, you can subscribe to my channel. If you like this video, you can smash that like button.

That was not my hardest punch. And I’m going to record a few more of these. So, I will see you over there.

Thank you for watching. Hope you enjoyed yourselves. I hope you learned something. And I will see you soon in another video. It’s been a while. Thank you. Thank you.

Going Further

More Updates to sp_QuickieStore and sp_PressureDetector

Posted on June 26, 2023January 22, 2026 by Erik Darling

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

sp_WhoIsActive: Advanced Logging To Tables

Posted on June 23, 2023January 22, 2026 by Erik Darling

sp_WhoIsActive: Advanced Logging To Tables

Video Summary

In this video, I delve into a set of custom scripts designed to help you monitor and manage your SQL Server instances more effectively. These scripts include a stored procedure that dynamically creates views based on the `whoisactive` pattern, allowing for easy retention management and comprehensive query analysis. By leveraging these views, you can easily identify and troubleshoot performance issues, especially those related to blocking and resource contention. Additionally, I walk through another stored procedure responsible for logging detailed information from the `sp_whoisactive` command into a table, ensuring that even fleeting events are captured. The third script handles retention by deleting old log tables and updating views accordingly, maintaining data integrity without overwhelming your database with historical records. Finally, I provide an agent job setup to automate the logging process, making it as seamless as possible for you. All this code is freely available on my GitHub repository, so feel free to check it out and adapt it to fit your needs.

Full Transcript

Erik Darling here, Darling Data, Enterprise Solution Architects. Just kidding, what a goofy, goofy thing to call yourself. In this video, we are going to talk about the set of code that I use to log the wonderful talented, beautiful, vocally gifted, SBHoo is active to tables. The views that I create to help you figure out what’s going on on your server, how I manage retention, and the agent job that I provide to get you up and running. Now, I created all this stuff because this is stuff that I have to do regularly in my client work if they don’t have a monitoring tool or to catch things that other stuff might be a bit overbearing to use, like extended events or something like that. Folks don’t have a monitoring tool. Crazily enough, some people who are still not on a version of SQL Server where Query Store is available, some of this stuff is very, very, very, very, very, very useful.

So, excuse me. Foggy in here today. So, the first store procedure in the bunch is one that will create views for you to use behind the scenes. Now, this store procedure gets, there’s a reason why this is a store procedure, and there’s a reason why this thing gets called every time SBHoo is active runs, and we’ll sort of talk a little bit about that. So, the two views that I create are, so, I should take a step back. The way that I do my logging is I don’t just keep dumping everything into one giant table because those tables can get out of hand really quickly. It also becomes really hard to search through them unless you add indexes to them to, like, figure out, like, you know, when something happened, right? Like, search through for, like, dates or whatever.

So, what I do in this procedure is look in sys.tables for any new table that the store procedure, again, it creates one table per day, which also makes managing retention a lot easier. So, it’ll look through sys.tables for any tables with a name that matches the whoisactive pattern that I create, and it will essentially create one outer view called dbo.whoisactive with all of those tables sort of union all together, right? So, you have one view that encapsulates all of the daily tables. The daily tables are called, like, whoisactive with, like, the year, day, and month.

And then you have the whoisactive view, which union all those tables together so you don’t have to search through, like, which tables are there and whatever. You can still, obviously, select from individual tables if you are interested in something that happened on a particular day at a particular time. That’s fine. But, again, that’s a lot easier to do for a table that only has been logged to for one day rather than for a table that’s been logged to for, like, a week.

The other view that I create is one that specifically does a recursive CTEquery to enumerate blocking on a server. So, the blocking query, I mean, it’s a long, complicated thing, but you’ll see the results in a minute, and I think you’ll be fairly enamored with what comes back. But this is the whole recursive CTEthing, and then I select everything out of that recursive CTEto give you a bunch of good information about the blocking queries on your server.

Even this can be really useful. Even if you have the block process report set up, the minimum threshold for blocking to get logged in it is five seconds. So, if you have blocking that goes on for less than five seconds or fewer than five seconds, depending on how you think about these things, then this can be useful to catch that stuff as well. I don’t suggest you log SP who is active to a table every second. That would give you a different problem.

But sometimes the agent job that I give you goes every minute. You are free to adjust that to your needs. All right. So, this is the stored procedure that creates the views. And again, the reason this is important is because as tables get created daily or as tables leave their attention period, we’re not going to have them available.

We’re going to either not have them available or we’re going to have new tables available that need to be part of those views. So, the outer view here really helps to, you know, encapsulate all those daily tables. And then the blocking thing just works off the other view.

And I know nested views, bad, horrible, but, you know, I’m a professional, so you can’t argue with me. The second store procedure is the one that does the actual logging. And this one pretty much does the, follows the instructions in the SP who is active documentation to set up a table, set up the destination table that we’re going to log SP who is active to.

All right. So, it does a bunch of this stuff. It, you know, concatenates things together nicely.

And then it executes who is active and we tell the store procedure we want to log you to a table. So, we give you, so we get that stuff and so we get all the parameters right in there. And then this is what does the actual logging to a table.

Right. This is what does the actual logging. That sets up the table and gets all the stuff that we need set up for it. This does the actual logging.

And then this store procedure runs to manage retention. So, by default, I keep 10 days worth of SP who is active tables on there. You are free to change that to your whims and fancies.

All right. So, first we delete any tables that are outside of our retention period. And then if we have created any new tables, again, because they are daily tables, then this, the who is active logging procedure to create views gets run to recreate the view definition so that we make sure that we’re only looking at the correct set of tables when we select from our views so that we don’t get weird errors.

Or we don’t get weird errors when the view tries to get created either. That would be terrible, wouldn’t it? I wouldn’t like that.

The third store procedure is the one that manages retention. So, this one builds up a string, essentially drop table for any tables that match the who is active pattern that I create that fall outside the retention period. All right.

So, good stuff there. Okay. This one’s pretty short and sweet and to the point, so I’m not going to spend too much time on it. You’ll just have to trust me that it works if you don’t feel like reading all the code. And all of this code is available freely in my GitHub repo.

So, if you decide to, you want to check it out, there’s a link in the video description for you to go do that. And if you find any issues with it, if you want to make any improvements to it, you can’t because I’m a professional and you can’t. You can’t reason with me.

Just kidding. I’m very happy to get GitHub issues, either to answer questions, get performance, report bugs, things like that. The final piece of code that I give you is to set up the agent job to run spwho is active.

Now, again, this will get you every minute, right? This will execute every single minute. Right?

So, it’s just pretty stock agent job creation stuff, agent schedule creation stuff. There’s nothing all that new, brave, or interesting in here. This is just a quick and easy way for me to hit F5 and get all the right stuff in place.

So, when I need to do this for clients, I don’t have to sit there and remember to tick the right boxes and set things at the right interval and all that. So, four neat pieces of code built on a very neat piece of code called spwho is active. And the results are pretty useful.

So, this is the main who is active view right here. And if we select star from this and we just say order by collection time. Collection time is a column way over here that isn’t part of like the stock and standard who is active results.

This collection time is one that specifically gets used so you know when these things got logged to a table. But if we order that by collection time and then say CPU descending because, you know, let’s say we care the most about high CPU queries on a server. We will get all of the standard spwho is active results back except logged to a table.

All right. So, we get all this fun stuff in there from, you know, again, this is just from a couple runs that I did of the agent job while I had some stress on the server. I didn’t want to do it live because, you know, it’s kind of, you know, again, the labor pains and the baby.

Here’s the baby. Okay. Cool. Now, what we can do, oh, my goodness, I forgot to alias that.

Someone fire a SQL prompt. All right. So, if we look at the spwho is active blocking, granted, I did not do anything too ambitious here because I really just wanted to get this recorded and available for you to consume and digest and, well, excrete in some way. Hopefully some useful way that makes your job and life easier and, I don’t know, helps you track down some problems.

But what this gives you back, and this is, again, what that whole big recursive view query that I showed you earlier in the video does, is gives you how long things were going on for. It tells you the speed and the date that things happened, which is useful information. If we come over here, we can see the weights that were in place.

So, the query getting blocked was waiting on LCKMIS. This was waiting on sleep, bpool, bpool, steal, well, and some other stuff too. I like to get as much information into this stuff as possible because I’m not doing it live and I don’t know what I’m going to need to troubleshoot.

So, I’d like to get as much stuff in there as I can so that I don’t have to say, crap, I missed it. Because nothing breaks confidence in a consultant like them saying, crap, I missed it. So, we get all this stuff back with, like, you know, the normal blocking stuff, that lovely new implicit trend column, you know, open transaction count, collection time, all that other stuff that we sort of talked about before.

Again, we’ve talked about this in a previous SPHUAS active video, but this is the get additional info column where you can see exactly which object we were waiting on locks to be released from so that we could do our read query. But, yeah, this is what the blocking view gives you. If there were more blocking in here, we would have, you know, additional things showing us other lead blockers or other queries that were blocked.

So, again, nothing too ambitious here, just enough to sort of give you an idea of what to expect from the results. So, with all that being said, thank you for watching. I hope you learned something.

I hope you’ll try these scripts out. I’ve had a lot of very good luck with them helping clients find weird issues with SQL Server. So, hopefully, you can, you know, with your clients or your employer, you can do the same thing.

If you enjoy this sort of stuff, if you enjoy my videos or me or, I don’t know, if you just need something to fall asleep to, like and subscribe. I’m here for you. I can be your lullaby.

Okay. And, let’s see, we covered thank you, covered like and subscribe, covered all the code, covered. I don’t think there’s anything else, is there?

No. All right. It is eight minutes until five o’clock. I think that’s close enough for me to start having glasses of wine, so maybe I’ll just go do that and save other videos for tomorrow. All right.

Cool. I’m out of here. Thanks for watching.

Going Further

sp_WhoIsActive: Filtering Results To See What You Want

Posted on June 22, 2023January 22, 2026 by Erik Darling

sp_WhoIsActive: Filtering Results To See What You Want

Video Summary

In this video, I dive into the versatile `sp_whoisactive` stored procedure and explore how to filter its output based on specific criteria such as session ID, program name, database, login, and host. I share practical examples of when these filters are particularly useful, especially in scenarios where you need to focus on certain databases or programs while ignoring others. For instance, I demonstrate filtering by database to monitor only critical applications or by program to track the performance of specific tools like SQL Query Stress. Additionally, I highlight how using wildcards can help exclude irrelevant results, such as service broker sessions that clutter your view during routine monitoring tasks.

Full Transcript

Guess who? Erik Darling with Darling Data. No surprises there, I don’t think. And before I record each video, what I do is mess my hair up. But also, I do a test video where I say things, I enunciate things into the microphone to make sure that the audio is synced up because there have been a number of times and I have recorded whole entire video videos and then watched them later and found that somehow magically between recordings the video and audio sync is off. So, sometimes I sing songs and sometimes I get those songs stuck in my head. If you can guess which song is currently stuck in my head, I will give you all of my money. So, that brings us to this video about SP who is active where we are going to talk about filtering the results. Now, this can be so in the last last video, I showed you how I troubleshoot specific scenarios and who is active. In this video, I’m going to talk about how I filter to specific things that I might be interested in.

Or not, or filter things out that I might not be interested in. So, starting with this first query here. Now, the things that you can filter by are session, program, database, login, and host. Right now, those are the four filterable elements of SP who is active. And I find that those generally get me where I need to go. So, if we run SP who is active just the normal way now, all we’re going to get back is this one line, this one session ID, 58, which is CDC doing some stuff in the background. But we don’t necessarily want to see that. So, since there is no active session ID 66 currently on my server, nothing shows up. But if I go run a query in session ID 66, that will be the one thing that shows up in the results. Right? So, kind of cool if like, you know, you’re running a query in another window, and you just want to watch it and see what happens with either the plan or the weights or whatever. It’s a pretty good way of doing that.

So, you can also filter to something that I use quite a bit because I tend to work, not tend to, but I sometimes end up working on servers that have a lot of different databases on them. And I don’t necessarily care about what’s going on in databases outside of like one critical one or something like that. And so, you can also use SP who is active to filter query results to a specific database, which if I go in this query from, I will get just this query back because the database that it is executing in is Stack Overflow 2013.

There are also situations where I’ll have to work on servers where I’m only interested in what a specific program is doing. You know, login sometimes, but you know, that’s kind of dull. And then host, you know, it’s, I don’t know, maybe sometimes. The one time that host came in handy actually was there were two web servers and requests from one web server were really slow and requests from another web server were really fast.

And the slow request web server had really high async network IO weights. And it turned out that the CPU on the bad web server was in balanced power mode. And flipping that to high performance power mode brought them both to equal speed.

So, host did come in handy once, believe it or not. Consulting is weird. But also if we want to filter to a specific program, we can kick off a fairly familiar workload with SQL query stress and run this.

And the only queries that I will get details back about are ones from the program SQL query stress. Now, I have a percent sign here in the string. Not because I need it.

It’s SQL query stress, plain string, no weird stuff from anything else. But there are times when you might have like a program name with some additional information in there, like maybe which web server it came from. Or, you know, like there might be like, you know, different spawns of the program.

Where this came in more handy is actually as a not filter. And you can also use you can use the filter parameter and filter type or not filter and filter type. So, where the wild card came in really handy for me is working on a server that had, I don’t know, like 100 databases.

Like a multi, no, like tenant per database type thing. And they all had service broker enabled. So, there were like 100 service broker sessions.

Every time you ran SP who is active. And, of course, service broker for each database had a slightly different name. Which was weird.

So, yeah. I used the not filter to get rid of all the service broker results. And all was well.

So, that’s kind of cool. And to a certain extent, the session ID filter was a little bit of what inspired me with SP human events. Because, you know, you use SP who is active.

And you watch a query, single query run. And you’re sitting there like you have to like query plan. Oh, open it. Oh, new query plan. Open it. Query plan. Open it. Where, you know, with SP human events, you can also watch a single session execute and filter. You know, set a threshold for like, you know, query execution time that you care about.

And you can only capture query plans for queries above that duration. But there’s a video about that too. If you watch it.

The SP human events videos on my channel. So, but, yeah, that inspired it a bit just because, you know, I just wanted all the query plans in one place where I could stare at them. Not open up a million SSMS windows and figure things out later.

All right. Cool. So, SP who is active. Very useful. Very, very quite useful.

You can filter in or out rows for session ID, program, database, login, and host. Very useful stuff there. If you need to focus or unfocus on certain things.

These are definitely parameters that I’ve found useful on a whole bunch of occasions. Because I do weird things. I do weird work.

Weird situations. People do terrible things to databases. And expect them to just function. And it is occasionally disappointing. When they don’t.

Anyway. Thanks for watching. I hope you learned something. I hope you take this invaluable knowledge with you. In your data professional career. Troubleshooting query performance.

And I hope that you like this video. And subscribe to the channel. So that you can see more hot, fresh, steaming SQL Server content. Anyway.

Yeah. Thanks for watching. Appreciate you.

Going Further

sp_WhoIsActive: Troubleshooting tempdb Contention And Blocking

Posted on June 21, 2023January 22, 2026 by Erik Darling

sp_WhoIsActive: Troubleshooting tempdb Contention And Blocking

Video Summary

In this video, I delve into using SP who is active to focus on specific issues and gather more detailed information about them. I demonstrate two scenarios: one involving TempDB contention and the other highlighting blocking queries. By limiting the output column list to relevant columns and utilizing wildcards for a broader search, I show how to efficiently capture screenshots and analyze server activity without unnecessary scrolling or complexity. Additionally, I explain how sorting by different parameters can help identify the most impactful queries in various scenarios, making troubleshooting more effective and streamlined.

Full Transcript

Erik Darling here with Darling Data. And, you know, according to another video about SP, who is active? One of my favorites. I’m a proud maintainer on the repo, even though there’s not a ton of action there. I do enjoy laying down the law over there. Just kidding. I don’t do much laying down the law. There’s a sheriff for that. I’m just a deputy. Pew, pew, pew. Anyway, in this video, I’m going to talk about a couple ways to use SP who is active to focus on specific issues that you might see and get more information about them. Now, you’ll notice that for many of the demos that I use in who is active, I limit the output column list just to where I use it. What I want to focus on. I find this to be a very useful tactic. Because by default, you kind of get a lot of columns back. And especially if you need to capture screenshots or something about what’s going on on a server, it’s a lot easier to limit the columns and kind of get everything focused in without us without having to scroll and get multiple screenshots or something.

I know that there are all sorts of ways that you can use screenshot software to scroll within a window. But for me, the less I have to do, the better. Code first, as they say. So I’m going to show you two scenarios that I use SP who is active for quite a bit.

And the parameters that I use to get more information and the list of columns that I tend to focus on. All right. Video’s over. Bye. Just kidding. Stick around. It’ll be fun.

All right. So the first one is TempDB. TempDB. So of course running this without anything going on, you know, there’s really not anything using TempDB here. TempDB allocations in current are both zero for my CDC job doing things.

Now, one thing that I want to point out is that for both of these, I am implementing the sort order parameter and using that to find things doing the most of something. All right. So this sort order parameter is really useful. By default, SP who is active just shows you kind of what’s been running the longest.

But there are all sorts of scenarios where you might want to find things by some other most. Right. Because sometimes SQL servers mosting is leasing or something. That made sense, right?

Min maxing, whatever they call it. Greatest in leasing. All right. Anyway. Let’s kick off a workload that is going to do specifically just a bunch of crap in TempDB. I forgot a semicolon there. Shame on me.

So this is just going to select the top 10,000 rows from badges into a temp table. It’s going to do that a whole bunch. There’s a helicopter going by. If you can hear it, sorry.

I do not control all the helicopter activity in New York City. So that’s my fault. Anyway, let’s kick this off and let’s beat up TempDB a little bit.

Let’s run this and let’s see what happens. All right. So we got this and I don’t think we really need this to keep running. It’s not really going to do anything useful for us.

I don’t know why I can’t click on this now. That’s interesting. SQL query stress. Where are you? Okay. Well, apparently that’s just going to keep going. So let’s kill that the old-fashioned way.

Maybe. No? All right. Cool. Whatever. Well, you really let me down there, SQL query stress. Really let me down.

Maybe if we just hover over here, we can see how much progress it’s made. Well, it’s still going, isn’t it? Well, anyway, as they say, SQL query stress giveth and SQL query stress taketh away.

So I guess we can give this another run and see what’s happening here. But scroll over a little bit. And none of these are going to use a particularly large amount of TempDB.

We’re only dumping like 10,000 rows into a temp table. But we can see a decent amount of stress on TempDB. Now, you can accuse me of shenanigans here.

But, you know, I do have eight TempDB data files to match the eight cores that are assigned to this virtual machine. And I do have the in-memory TempDB feature enabled here. So I’m not just messing with SQL Server.

We still are hitting TempDB contention, but just not a ton of it. All right. There’s a 9, 10 milliseconds. It’s not like, you know, not like in the past when you had misconfigured TempDB.

And, you know, before 2016 when trace flags 1117 and 1118 became the default behavior. And, you know, you didn’t have the in-memory TempDB system tables where this could drag on for many milliseconds. But now we just have, you know, pretty minimal TempDB contention.

No. So, you know, thanks for, um, thanks for finally showing up. Thanks for finally finishing.

Groovy. Groove is in the heart. Cool. So this is the setup that I use if I’m troubleshooting contention in TempDB.

Uh, I use the sort order, uh, TempDB current descending. Uh, and for the output column list, I use, uh, well, I get sort of like the normal stuff. And then one thing that I think is really cool about SP who is active is that, uh, you can put the list of columns that you want.

But if you put, um, wild cards on, you know, we can put the one on the beginning, the end, both sides. Uh, then it will get back all of the columns that, uh, that are related to, uh, TempDB. So that’s, that’s one thing that I find really useful here is, uh, the wild card.

So I don’t have to remember the name of every single column that touches TempDB. I can just say Temp, uh, percent for, for like the, the, you know, the wild card search and get all of them back. So pretty cool there.

The next one that I want to show you, uh, that’s why I have these columns on a new line, because my big head gets in the way of some of these over here. Uh, I’m going to do something sort of different, but sort of the same.

You know, that’s why these things kind of group together. One of these things is a bit like the other. So for this, I’m going to find block leaders. And when I use, when you use find block leaders, that gives you a column called block session count.

And I’m going to order by block session count descending so I can find which query or queries are at the top of a blocking chain. Uh, I’m also going to get a little bit of a, of additional information here so that the additional information column gets populated with all of the most useful things. Now, uh, there is a parameter for SP who is active called get underscore locks.

And that can be okay sometimes. But what get locks does is it populates an XML column, like an XML clickable column with all sorts of lock information. And on servers where there are a lot of locks going on, that can be really, really, really slow.

And that’s kind of why the additional information column is useful because you don’t need to enumerate all those locks in the same way that the get locks parameter does. So that’s why I have that instead of get, that’s why I have this setup instead of using get locks because I just don’t want to deal with many, many locks. So if I run this now, uh, the only thing going on is again, my, my pesky CDC job.

And, uh, uh, I don’t think, uh, we just don’t need to get into that, do we? I don’t think so. Probably, probably not too interesting to get into that.

But, uh, what I’m going to do is come over to this window. I’m going to begin a transaction to update the comments table and then I’ll, I’ll roll it back later. But really, I just need the transaction open like this so that when I go and I run this query, which is going to try and query the comments table, uh, all these queries will get stuck.

So, uh, give this a couple runs and that’s probably good enough. And, uh, yeah, so we can cancel this. That, amazingly, that worked.

And I’m going to, uh, roll this back and I’ll deal with the, the rebuild over there, uh, in a moment. Uh, the reason for the rebuild is because I am updating an Envarkar column where, uh, to something where, you know, like it just shouldn’t be happening. I don’t want to, it can just cause, it just causes weird stuff with other demos.

So I have that out there. But the important stuff that we get back, the, uh, the SQL text, the query plan, which can be really, really useful for troubleshooting, blocking problems. Uh, over in the wait info column, uh, we have, uh, information about the queries that are blocked waiting on lock weights.

Now, there’s no wait info for that top query, right? If we look over here, this top query has no wait info associated with it. And that’s because, remember, queries doing the blocking do not emit lock weights unless they’re being blocked.

But in this case, this query is just free to go off and do whatever it wants. And it is, and it is causing the blocking. So other queries are wait, waiting to get locking weights to do their thing.

Now, I am under the default isolation level for SQL Server, which is read committed, which is garbage. And I don’t recommend it, but it is good for the purpose of this demo. So thanks for being good for one thing, read committed.

Now, uh, you can see the blocked session count column. Uh, this top query here has the most blocked session. Remember, we’re ordered by this descending.

And so it’s going to look a little bit funny to see the CDC column show up in there. But it’s not, remember, this is just an await for. And since this has, these bottom, uh, bottom four queries are not blocking anyone, the sort by zero just kind of puts them in whatever order, right? Like, like non-deterministic sort.

But we have the blocked session count. Uh, we have the queries that are doing, well, this top query is blocked right here. And then, uh, it says this one here has three queries behind it, which, uh, is, is technically true, I guess. But, uh, but, but looks a little bit funny since it’s, it’s a select, right?

But really it’s just because this one is blocked by this one. So, uh, blocked session ID 66 or blocking session ID 66. That’s this top one.

65, that’s the, that’s the second one. And so this one, this, this top query is blocking all four of these, but this bottom query is blocking three of the other selects, technically speaking. Now I told, I made a big stink about that additional info column.

So we might as well click on that. Now for the query doing the blocking, it’s not going to look all that interesting here. Uh, the command type, because it’s a modification that finished and was waiting to roll back or commit, the command type just says awaiting command.

But if this were in flight, it would say update. Uh, we get some useful stuff back like the SQL handle, uh, which is good if you want to go track down an execution plan for this at some later point. Uh, you get the, uh, the ANSI settings for the query.

So if, uh, you’re using some weird setting, you might be able to fix that, uh, in the application or whatever over here. Um, you know, there are all sorts, there are the, uh, ANSI, the required ANSI settings in SQL Server to do all sorts of stuff like, uh, match index views, filtered indexes, computed columns, which break if you’re not using the right settings here. Uh, and, uh, you also get the isolation level, which in this case is the steaming pile of garbage known as read committed.

Uh, where things get a little bit more interesting are for the queries that are blocked, uh, because that will give you this separate section of XML, uh, called block info. So in block info, we’ll tell you the lock type that, uh, is, is we’re, we’re waiting to be released, uh, the database, uh, object ID, object schema, object name of the thing that we are trying to get access to. And, uh, that’s good because then we can figure out, um, you know, if it wasn’t obvious from, uh, uh, from either the, the query doing the blocking or the query that is being blocked, we can get, uh, information about exactly where this thing is jammed up.

Now, again, you can get a lot more detailed information using the get locks parameter, but when there’s a lot of blocking going on, that can be really, really terribly slow. But, uh, the additional info column for all of the other three blocked queries will show just about the same thing. Uh, we’ll see, you know, the information about the database here.

Uh, I don’t know. That’s about, that’s about it. Anyway, uh, so this is a couple ways that I use SP who is active to troubleshoot specific scenarios on servers. There are all sorts of other ways you can do this.

There are all sorts of things that you can do to, uh, get more customized, uh, output from who is active. We’re going to talk about filtering, uh, results in the next video. But this is a pretty good, uh, illustration of how I use SP who is active to, um, to filter out to certain things or to, uh, troubleshoot specific scenarios.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

Uh, and I do hope that you will watch future videos of mine and that you will like and subscribe to my channel. That you are feeling sleepy, very sleepy. You will find yourself clicking the like and subscribe button.

And, um, yeah. That’s, that’s, that’s that for me. That’s it.

I’m out of here. I’m done. Tired. So tired. So ever 11 tired. Anyway, thanks for watching.

Going Further

sp_WhoIsActive: Tracking Down Implicit Transactions

Posted on June 20, 2023January 22, 2026 by Erik Darling

sp_WhoIsActive: Tracking Down Implicit Transactions

Video Summary

In this video, I delve into the intricacies of implicit transactions in SQL Server using SP who is active as a tool to track down these pesky issues. As usual, I begin by explaining why Microsoft hasn’t provided a straightforward way to monitor implicit transactions and how they can cause unexpected behavior, especially with drivers like JDBC and Python. I then demonstrate how SP who is active can be enhanced with specific parameters to reveal more detailed information about queries within implicit transactions. However, even with these enhancements, the tool still falls short in fully capturing the complexity of such transactions. Throughout the video, I illustrate this through practical examples, showing that while SP who is active is a valuable resource, it isn’t sufficient on its own for comprehensive transaction tracking. The takeaway? Implicit transactions are best avoided unless you have a deep understanding of their implications and can manage them carefully.

Full Transcript

Erik Darling here with Darling Data, as usual. Forever, forever and so forth. So be it as it may. And in this video, I’m going to show you how you can tell with using SP who is active. Still waiting for Microsoft to write SP whom is active or something. To track down if queries are using implicit transactions. Now I have another video fairly recently about just how bad implicit transactions can be. Should probably watch that. Promise it’s a barn burner, as they say. But you see them a lot from the JDBC driver, you see them a lot from the Python driver, and they can come and cause some real weirdness in a lot of ways. Now, SP who is active is no shortcut around that weirdness. And I’ll show you why and how wow that is true. So if I run this now, again, we’re going to have my noisy CDC job in the background because I am working on CDC stuff for a client locally. Figure out some ins and outs there.

Some specific use cases, as it were. So I’m going to use get transaction info. And if you have not updated SP who is active in the last year, year and a half, you’re going to need to do that in order to see this information. So that’s what that’s what this is the secret sauce, as they say. But you get a new column called implicit underscore tran. And that that’s what’s going to tell you if something’s going to be an implicit transaction or not. This is a null. In this case, not every transaction is going to have a on or off here. I don’t know why. Something’s just weird in the place that this is tracked. Maybe an is null or a coalesce would help with that. Let’s say is null. No. So what I’m going to do is over in this window, I’m going to set implicit transactions on and I’m going to show you why things are weird. And then I’m going to run this count query. I’m going to get a result and I’m going to run this query and I’m going to get a result, but I’m not going to hit commit. I would have to use commit to close this transaction out.

So if I run SP who is active now, I get this back. And in the SQL text, it just says select count big from badges, which, you know, is not really the full story because we had that other select from the users table in there. Now, if I use another parameter for SP who is active called get full inner text and I set that to one, you might think that you would get the full inner text of the set implicit transactions on the select count from users and then this, but you don’t. You still only get the full command. You still only get the select count big. Notice up here in the SQL text, we do get the full store procedure instead of just the wait for command.

Just to refresh your memory, if I quote that out and I run this, all we see is wait for delay, whatever. Now, there’s another parameter you can use with SP who is active called get outer command. In order to see that, we need to change this a little bit to say SQL command.

And if we run this, we’re still not going to get the full story of what else happened in there. All right. So like notice for SQL text, we get the procedure that’s running and we even get we get the name of the procedure, the execution of it over here. But over in this column, we still only get the select count.

So even SP who is active can show you somewhat misleading information about about multiple queries in an implicit transaction. Of course, if I come over here and I hit commit, then everything goes away. I’m running. Now I can’t I can’t double commit.

But just because I have implicit transactions on, I have to commit this here. And now if I run this whole thing, all I see is the information about the CDC job. So what else is there to say?

I’m going to say implicit transactions are a real pain in the butt. They’re a real nuisance. I really don’t suggest that you use them without a full understanding of exactly what they’re doing.

The full the full understanding of everything that you’re doing in a single batch that might hold locks for a really long time. In the implicit transactions specific video that I have, I talk about how that how that can happen. So, yeah, most of the time I just tell people don’t use them because most people just don’t have a good handle on what they’re doing with them.

And there’s no global way to say not allowed because any user session can say, well, I feel like using them. I’m going to override you. I’m not listening to you, mom and dad.

So, yeah, that’s that. Implicit transactions, bad for the body. Bad for the body SQL.

Avoid them. They’re confusing. Even SP who is active has a tough time seeing through their dismal charade. And so do not recommend.

Thumbs down. Thumbs down. Anyway, thank you for watching. Hope you learned something. I hope. Well, I would say that I hope you enjoyed yourselves, except this video was rather depressing, wasn’t it? It was a bit dour.

We didn’t. We didn’t. We only learned how bad something was. We only learned how tough it is to deal with this thing. It is so bad that it even makes SP who is active look foolish, which is a tough gig. Anyway, please find your way to the like and subscribe buttons.

If only so you can see my life and say, God, everything’s kind of all right for me. Anyway, I’m going to record something hopefully less depressing after this one. So thanks for watching.

Going Further

sp_WhoIsActive: Getting Memory Grant Details

Posted on June 19, 2023January 22, 2026 by Erik Darling

sp_WhoIsActive: Getting Memory Grant Details

Video Summary

In this video, I delve into using SPWhoIsActive to gain deeper insights into query memory grants on SQL Server. Specifically, I demonstrate how to leverage the new `@GetMemoryInfo` parameter to retrieve detailed information about memory grants, including requested and used memory. However, I also point out some quirks in the output format that might be a bit confusing for users, such as the way memory is reported in pages rather than more intuitive units like megabytes or gigabytes. Despite these minor inconveniences, the tool proves to be quite useful for troubleshooting scenarios where query memory grants are problematic. I encourage viewers to update SPWhoIsActive if they haven’t done so recently and explore this feature further to enhance their diagnostic capabilities.

Full Transcript

Erik Darling here with Darling Data. If you can hear anything that sounds like I’m at a dentist office on this recording, I do apologize. Someone started sawing or drilling or something outside and I don’t know, there’s just not a lot I can do about other people at this point. Someday, someday. Anyway, this video is going to be a little bit more. So, I’m going to talk about how you can use SPWhoIsActive to get additional interesting information about query memory grants. And also how the output for this is a little weird for my tastes and why. So, I’m going to use this demo, which I used in a prior memory grant video. And I’m just going to run this and open up and run SPWhoIsActive. And then I’m just going to kill this because we don’t really need anything more from this at the moment. You can ignore this top line. In case you can’t read up here, this tab is titled CDC crap. And I was trying to figure out some stuff for a client about Change Data Capture. And so, I have this Change Data Capture job constantly running on my system now. There’s a way to run this.

So, I’m going to wait for it. And it goes and does some stuff. But anyway, newer versions of SP. Now, the last version of SPWhoIsActive was released about a year, year and a half ago. So, if you haven’t updated SPWhoIsActive in the last year to year and a half, now would be a good time to do it so that you’re able to use this fabulous new parameter called at Get Memory Info. So, that’s this lovely parameter right here. Now, I’m using the output column list just to get back the columns that I care about. You don’t have to do this to see the memory grant stuff. This is just to make the results a little bit more digestible for the video that I’m recording. No other reason for that. But it is kind of a cool example of how you can use the output column list to get just the columns that you care about back for certain things. But we’re going to talk more about that in another video.

So, just like in other memory grant demos that I have, this set of queries when run in a foursome will have one query that gets stuck waiting for memory grant. So, you can see that because it is requested memory but granted memory is null. Now, where things get weird with these results is, and this is not the fault of the gentleman who added this code in, a smart fellow named Michael Fuller who’s been indispensably helpful working on who is active over the past, I don’t know, I guess, well, I guess past couple years.

There hasn’t been a lot of active development just so recently. But, so when, for all of, so these three columns are what got added to the output list. This used memory column has been in there forever.

And the logic for the used memory column is to report this in pages, KB. So, used memory is divided by eight. I don’t necessarily agree with this choice because you don’t really measure query memory grants in eight KB pages.

You measure them in megs or gigs or something that is a little bit more in tune with how you think about memory grants generally. You generally don’t think about eight KB pages when you think about query memory grants in SQL Server. So, these numbers here are always going to look a little bit weird.

Of course, we have this query down here which has requested memory but has not been granted memory and has been waiting two and a half hours. And, we’ve talked about that in other videos which were highly successful. Smash hits, they’re the reason I make billions of dollars on YouTube and no longer need to work a real job.

I’m kidding. If only, if only. But anyway, so you get used memory, max used, requested, and granted memory back in the output list here, like top-level columns.

But then you also get another top-level column called memory info. And memory info is an XML clicky column that will report this information. But, like, this stuff gets reported sort of like from the raw DMVs.

There’s no adjustment made to it. So, you get the request time, grant time, like you get requested memory, used memory, all that stuff in KB. So, like, you do have to do a little bit of KB to GB math if you want to figure this stuff out.

Generally, I don’t care about memory grants that are under the one gig mark. Generally, I don’t care about query memory grants until they hit, like, you know, several gigs or more. That’s just me, though.

You know, especially on larger systems, you know, smaller memory grants just don’t, just do not titillate the mind. But this one is interesting, sort of. But the one down here for the query that’s waiting is a little bit more interesting because there’s a little bit more information to take in.

Such as, and this is stuff that we’ve talked about in other resource semaphore videos. Go find those if you want to, you know, learn more about these columns. But these are, because this query asked for a memory grant and didn’t immediately get it, it’s sitting in the queue.

It’s the next candidate. So, the wait order for it is zero. And, you know, you can kind of get some information about the workload group that it’s in, what the max memory grant that a query can ask for is. And this 25 is based on max server memory.

And then you can get some information about that, about the workload group, like total memory, how much is available, how much has been granted out, how much has been used from that. So, there is good information in here that is usable. You know, XML isn’t always the most consumable thing, but it is an easy way to present a whole bunch of information like this all in one go.

So, like, it doesn’t help if you want to stick it into an Excel file or something, but it is a reasonable way, I think, to present like grouped information in this way. Cool. So, get a new version of SP who is active if you haven’t updated it in a long time.

And check out the get memory info column. It’s a useful doodad for if you are troubleshooting, you know, a SQL Server where query memory grants are sort of out of control. Again, other videos about memory grants, resource semaphore and stuff where you can learn more about all of that information.

I highly suggest you peruse every video on my channel so that I can get enough views to have YouTube say, you should consider monetizing your channel. And I can say, no, I’m a man of the people. I do this.

I do this for them. I don’t do this for money. I mean, I do do this for money. Just like not whatever. Anyway, you get the point. Thanks for watching.

Hope you learned something. Hope you enjoyed yourselves. And if you would be so kind as to like this video and subscribe to my channel, I would be ever so grateful. But since I don’t get paid for this, I can’t give you any kickbacks.

But just my love and affection. Maybe a kidney if you really need it. But I kind of don’t think you want my internal organs at this point.

I’ve been working with SQL Server too long. I’ve just been tremendously abused. Anyway, see you in the next video.

See you in the next video.

Going Further

Updates to sp_QuickieStore and sp_PressureDetector!

Posted on June 16, 2023January 22, 2026 by Erik Darling

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

sp_PressureDetector: Which Queries Are Causing CPU and Memory Pressure?

Posted on June 15, 2023January 22, 2026 by Erik Darling

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.