First, this post needs to have the T-SQL Tuesday logo in it, so sayeth the rules. Here’s that.
This month’s topic challenges you to think back to the last time you saw code that made you feel a thing. Hopefully a positive thing.
I’m not a thesaurus, so the list isn’t exhaustive, but think along the lines of: surprise, awe, inspiration, excitement.
Or maybe it was just code that sort of sunk its teeth into you and made you want to learn a whole lot more.
This won’t be my submission, but I think a great example is a piece of query tuning magic by Paul White. It was one of the first articles I ever read as a young SQL Developer.
It was one of many lightbulb moments (and, crap, I should have said lightbulb moment up there when I was telling you what kind of code I want you to think about) that I’ve had in my career.
It should also probably be code that you won’t get sued for showing or talking about. Please be diligent in abiding by copyrights.
Other than that, it’s the normal pack of rabbits: submissions have to be posted next Tuesday, the 11th of July.
I’ll post a roundup after I’ve had time to gather all the links and write something sufficiently witty about each one of them.
If you feel the need to go negative, make sure it’s about something that no sane or rational person could disagree with, like comma placement, whitespace, indentation, capitalization, line endings, naming conventions, or aliases.
But especially if table aliases should be capitalized. We all know they shouldn’t.
Thanks for reading!
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_BlitzLock To Investigate SQL Server Deadlocks
Video Summary
In this video, I delve into the intricacies of SPBlitzLock, a powerful tool designed to help Database Administrators and developers navigate the often frustrating world of SQL Server deadlocks. Drawing parallels between my espresso machine’s quirky behavior and deadlock issues in SQL Server, I explain how SPBlitzLock can be a valuable asset for identifying and resolving these problematic situations. By walking through the output of system health extended events and detailing the various filters and parameters available within the procedure, I aim to equip viewers with the knowledge needed to effectively use SPBlitzLock in their own environments. Whether you’re dealing with query-on-query deadlocks or parallel deadlocks, this tool offers a comprehensive breakdown of deadlock information, making it easier to pinpoint the root causes and implement solutions.
Full Transcript
Erik Darling here with Darling Data. And in this video, we’re going to discuss probably the stored procedure that I’ve spent some of the most time working on of all the procedures in the first responder kit, probably only SPBlitzCache, maybe SPBlitzQueryStore, but I haven’t touched that one in a while. So, um, um, Blitz who at first, but, you know, uh, only so much you can do in there. But, uh, SPBlitzLock, uh, will help you with deadlocks, not locking deadlocks. And, uh, I’m going to start this off by talking about how, uh, my stupid espresso machine reminds me a lot of deadlocks in SQL Server. You see, my stupid espresso machine is a Philips 3200. And, uh, it’s a fully automated espresso machine, which is great for someone like me who is not terribly invested in the process of making espresso, but desperately needs the output of an espresso machine, which is, uh, a whole lot of caffeine packed into a small, small container. Now, when you, if you, if you have an espresso machine, you can get a little bit of caffeine.
If you have an espresso machine or use an espresso machine, uh, you might know that when you turn it on, there’s often a bunch of steps that you like the warmup stuff that it has to do before it’s ready to make you that perfect cup of espresso. My, my espresso machine, um, if I started up and, um, it, it realizes that there’s enough, not enough water in the basin or that the, the puck receptacle is full, it will actually stop the whole startup process and wait for you to, uh, come fix it. Either empty the, the puck receptacle or fill the water basin. Uh, when it does that, there’s no audible beep.
Right? There’s no, like, thing that, like, extra warns you about the fact that it stopped doing anything, which, if you’re sitting there staring at it, isn’t a big deal. But, you know, for someone like me, when I know something’s going to take a minute or two to warm up, I hit the button and then I go do something and figure I’ll come back and hit the, the double shot button and hit start. And then my espresso will come out.
So that’s frustrating because that’s sort of like what DBAs and developers deal with, with deadlocks. There’s often no really loud beep when there are, uh, when, when deadlocks are happening. A lot of applications sort of swallow, uh, error messages, right?
You like any user might not get an error, like a message that there was a, their, their transaction was involved in a deadlock. They just might get some error message like, um, try your, try your request again later. Do it again some other time.
Maybe you’ll get lucky. The other problem with the espresso machine is that, uh, the, the, the grinding area, which now that I say it sounds a little dirty, but the, uh, the grinding dish, let’s call it, let’s keep it a family friendly SQL Server video.
The grinding dish is this weird shallow thing. And like the, the, like the grind implement. God, it just keeps getting worse.
It’s in like the front left-hand corner, but like the angle of the dish, when like you put beans in, they don’t always like, like you rely on the shake of the, the, the espresso machine to like drive the beans towards it. But, uh, they don’t always, they can’t always make it there.
And there’s like a couple of weird things in there that beans can get stuck on so that like, you have to like go fish them out. It’s, it’s, it’s weird. And, uh, that reminds me a lot of deadlocks because getting deadlock information is a terribly frustrating thing.
Uh, it involves, uh, for most, I think for most folks these days, it’d be using extended events, which is, which leads to a whole lot of XML parsing and shredding. If you want to get your, get information about your deadlocks into sort of a readable format, that’s just not an XML document that you have to look all over the place for stuff for. Um, so there’s that.
So because of all that, back in 2017, while I was, I was drunk on an airplane with, uh, El Capitan himself, uh, flying out to, to, to, to pass summit for a pre-con from Chicago. Uh, uh, uh, I, I started writing SP Blitz lock. Uh, Brent was terribly uninterested because of the, the amount of X query involved.
Uh, it’s also probably why he doesn’t like Blitzcast very much either, but, um, yeah, it’s, uh, it, there were some weird design choices in it then mostly due to plain drunkenness. But, uh, I recently gave her a facelift and, uh, fixed a lot of the, the issues that I had with it over the years. So, um, hopefully now everyone can, well, I mean, I was drunk, but I wasn’t drunk on a plane when I fixed the, I was actually just drunk in a hotel room in Las Vegas.
So, uh, for most of it anyway. Uh, but yeah, um, now SP Blitzlock is in a pretty decent shape, I think. So, uh, by default, everyone, every, just about every SQL Server I look at these days has a system health extended event on it, which has, captures deadlock information.
So, uh, by default, if Blitzlock will go and look at that. If you have an event set up specifically to look at deadlocks, you can look at that by feeding it the name of the extended event session that you use to capture deadlocks. So, let’s just take a quick look at the output when we go to the default system health extended event session.
I put a pretty far back start date on this. Uh, by default, it’ll go back one week. Um, the system health extended event is sort of a best effort thing.
Data will be there if it can hold it. If not, it kind of rolls over, goes away, says bye-bye. Um, so, there’s a, you know, it’s good if you don’t have anything else, but if you, if you are okay with setting up an extended event session just to capture deadlocks, you’re, you’re probably better off doing that to have. So, but, uh, the, the output that it gives you, pretty, pretty simple.
Uh, you know, you get the server name, the type of deadlock. So, there’s two types of deadlocks that, uh, SP Blitzlock will, uh, help you analyze. There’s regular query-on-query deadlocks.
And then there’s also parallel deadlocks, which occur when, uh, you have a parallel execution plan. And the threads in that plan have some sort of order-preserving dependency in them that, uh, causes certain threads to not be able to make progress. And then eventually you may end up with a parallel deadlock.
You may end up with a bunch of exchange buffers spilling out to tempdb. It’s a, it’s a pretty nasty situation. I’m definitely worth looking into and trying to fix. Um, sometimes a max-dot one hint is your friend.
But you get, uh, the event date, the database it happened in, uh, the SPID, which I don’t usually find terribly useful. But, you know, I like to express as much of the data, uh, as, as gets stored in the deadlock report as I can. Uh, you get the deadlock group.
So, you know, which queries were involved in which, uh, set of deadlocks. Uh, you get the query that, uh, as much of it as possible. Sometimes the query text is truncated in here. Um, you get the, the, when possible, you get the object names, the, the table that were involved in the deadlock.
Uh, you get the isolation level, which is, uh, generally correct. Um, there are some circumstances when it may not be. Uh, you get the owner mode and the waiter mode of the deadlocks, which is good to have.
So you can figure out if you, um, have, uh, reader on writer deadlocks, which are most easily solvable by an optimistic isolation level. Read committed snapshot isolation or snapshot isolation. Uh, I picked up the client options because sometimes these get weird and you can, uh, see odd things in there.
Uh, some, uh, some basic information about where the queries came from, login, host name, client app, how long they waited before the deadlock detector woke up and gave someone the old kapow. Uh, the wait resource, which is not easy to decode.
Uh, so I don’t do that. Um, if you feel like figuring that out on your own, go for it. Um, if anyone’s setting deadlock priority, how much of the transaction log was used, some information about, uh, when things were happening in there, uh, the transaction name.
Uh, there’s some weird stuff to look out for in here. If you’re using the JDBC driver, you definitely want to look out for implicit transaction, the transaction name column that can be, uh, useful to have.
Uh, the status column is also pretty useful. Um, if you catch a lot of stuff in the status column that says sleeping, you, like, you might have an application bug where, like, queries aren’t closing commands out or something like that.
And, um, you, uh, you know, you need to fix that. You can’t really, can’t really do anything there. Uh, if you kill it, then they’ll roll back and you may not want that to happen.
Uh, if it’s a parallel deadlock, then this will get populated with details about what happened within the parallel deadlock. These are, of course, regular deadlocks, so there’s nothing too interesting in there. And then, of course, I also give you the full deadlock graph back, which SQL Server 2022 added a whole bunch of nonsense to, uh, just in case you want to copy and paste that into, uh, something that will visualize deadlock graphs for you.
Management Studio does it. Plan Explorer does it. So there’s stuff in there.
Uh, the bottom section will enumerate or roll up some of the information we have about deadlocks. Um, you know, give you some friendly advice, right? Like you might need RCSI, uh, which objects have been involved in the most deadlocks, which indexes, uh, it’ll also, you know, figure out if store procedures or ad hoc queries were involved in them.
Uh, it’ll give you a total, you know, deadlock wait time for stuff, uh, by database and object and, you know, in total. So there’s all sorts of useful information in there. Now, I’m not going to go over all that stuff again, but I am going to tell you that if you have a specific extended event session set up to, uh, look at deadlocks, then all you need to do is fill in the event session name parameter.
Um, again, I’m just going to use the start date here and accidentally hit the space bar. I’m just going to use the far back start date there. And this is one deadlock shy of the other one only because I didn’t have this set up when I was doing other deadlock stuff.
So, um, it looks a little different, but you can still probably, um, figure out enough from that there. Uh, there are a bunch of other filters in Blitzlock. You can filter down to database, object, um, user login, pretty much any, there are a lot of things you can filter to in there.
Um, you can set an end date. So if you want to look for a specific day or something, you can, you can filter to start date and end date. It’s pretty good.
Uh, there are a few others. Um, there’s, as always the, the help parameter is where you want to go to keep abreast of all of the latest developments in any of these store procedures. So, uh, I would, I would highly advise you do that.
So anyway, uh, that’s SP Blitzlock. You don’t have to do much there. Uh, pretty much just run it.
It spits out all the information and then it’s up to you to figure out, uh, what goes where and when. You know, so one thing that I do want to actually sort of tie into another video is, uh, we talked in the SP Blitzcache video about using only SQL handles. So if we go into the deadlock graph here and we scroll past all of whatever the heck this is, um, you’ll see that there are SQL handles for various, uh, queries in the XML deadlock report.
And this is what you would want to paste into the only SQL handles parameter in SP Blitzcache or SP Quickie Store to try to find execution plans for the queries that were involved in the deadlocks. A lot of the time, um, a lot of the time, uh, the query plan will tell you a whole lot about why the deadlock happened. That, uh, that might not be totally obvious from just, just the text of the query.
So anyway, that’s SP Blitzlock. It’s pretty awesome. I’m very proud of it.
Uh, if you like this sort of SQL Server content, don’t forget to do the old like and subscribe for me. I don’t know why I’m getting such interference over here. Everything was normal a second ago.
Green screens are temperamental. I guess, I guess like espresso machines. Uh, uh, what did I, I forget what I said last. Like and subscribe.
I’m forcing you to. Um, I have your family in a basement. If you don’t like and subscribe, um, I’m going to take them out to a nice dinner. They’re nicer than you could ever, nicer than you could ever bring them to. And then they’ll lose all respect for you.
Something like that. Anyway, uh, I hope you enjoyed yourselves. I hope you learned something. Thank you for watching. And I will see you in another video somewhat shortly. I’m going to have to run through these demos a little bit.
And then we’ll see, we’ll see what, see what we come up with. All right. Thanks 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_BlitzCache To Find Poor Performing SQL Server Queries To Tune
Video Summary
In this video, I delve into various ways to utilize SP Blitz Cache, a powerful stored procedure originally created by Jeremiah Peshka and maintained by many happy users over the years. I discuss how to navigate through different sort orders and parameters within SP Blitz Cache, such as average CPU, unused memory grant, and executions per minute (XPM), to identify queries that need optimization. Additionally, I highlight the importance of considering query plan instability in the Plank Cache, especially when compared to the more robust Query Store, and share practical tips for managing memory grants and identifying non-inlineable scalar UDFs or frequently executed code snippets.
Full Transcript
Erik Darling here again. Darling Data. No one from Microsoft has yet tried to acquire me based on the terms and conditions laid out in my last video, so I guess I’ll just soldier on here and keep pumping these things out. I hope that someone out there says, boy, that fellow looks useful. Let’s hire him. Let’s do some stuff to our SQL servers. Or let’s just watch all my videos and learn all my tricks. Who knows? Who knows what will happen? It’s a cliffhanger. So this video is different ways that I tend to use SP Blitz Cache, originally written by the lovely talented, hyper-ultra-cool Jeremiah Peshka, and of course maintained over these many years by thousands of happy people. Okay? Maybe like a dozen happy people. I don’t know. I guess depending on how much of the query plan XML they had to dig into would dictate if they are indeed truly happy people or not. Because I can tell you, when I was adding a lot of XML parse into this, it was some of the roughest days of my life.
So, I’m going to talk about, again, demo server hasn’t been a lot going on in there, so there’s not really going to be any big fireworks if I run this to get results. So I’m just going to talk through some of the different ways and sort of caveats and gotchas that I use SP Blitz Cache to figure out. Now, I’m just going to say it out loud and clear. The Plank Cache is a terribly unstable place. oftentimes when I go to look and see what’s in there, there’s a little warning that pops up in Blitz Cache about Plank Cache instability. This is one thing that I will show you.
It’s going to be a line like this here. So because my Plank Cache is apparently relatively stable, there’s not a lot going on in my server. You can kind of tell by this, like a lot of just the system queries that have been running on here. My kid put a Band-Aid on me. She pretended to give me a shot, actually two of them. So that’s why I have Band-Aids on me. I just forgot to take them off. And you know what? I rather like them, so I’m going to keep them on. So depending on how unstable your Plank Cache is, this might move up higher in the priorities.
But this line here will tell you, sorry, this line here will tell you what percentage were created within a few time frames. They’re not terribly scientific. It’s just last 24 hours, last 4 hour and last hour. And a lot of the times when I see this on servers that are like really active, you’ll see something like a, like, you know, some really high percentage of plans were created in the last, like, you know, one in four hours and like almost a hundred percent were created in the last 24 hours.
And it just doesn’t give you the type of real historical view of which queries ran on your server and caused problems at various points. It’s all sort of very, I don’t know, it seems very short term to me just for a lot of reasons. The Plank Cache clears out for, you know, memory pressure restarts, some settings changes, you know, people, you know, dorking around with stuff.
And particularly a lot of the servers that I look at, you know, tend to have pretty severe memory pressure issues. So we’re lucky if the Plank Cache even has like, you know, a decent amount of plans that are an hour or more old. So it’s a little, it’s a little tough to love the Plank Cache these days, you know, back when it was all we had made the best of it.
But now that, now that we have Query Store, I tend to spend more time in there than anything. Now, one of my go-to sort orders for Blitz Cache, and even for, you know, you looking at the Query Store with my store procedure, SP Quickie Store, is average CPU. And the reason for that is because when I find queries that have a high average CPU, I have pretty good confidence that those are queries that I can make meaningful adjustments to to fix.
The thing is, sometimes when you sort by average CPU, you find stuff that doesn’t matter a lot. It might only execute once a day, it might execute once a night, you know, it might be some background task that doesn’t really have any impact on end users, aside from the fact that, you know, it took up resources that the end users could have used while the query was running.
And that can, you know, that can be a little frustrating, you know, especially, you know, if you’re working with people who don’t know what, like, you know, sort of, like, can’t point you to which queries they want fixed. It’s up to you to go and find them. And if you find, like, if you know your initial search is a bunch of meaningless queries that don’t really do a whole lot to affect the sort of general user workload, then you haven’t really found queries worth tuning.
You know, you might, you might have it, like, people say they’re not doing anything bad, but they’re actually doing a lot of bad stuff. But, you know, again, you tend to want to focus on the things that, like, users are really feeling up front. So, average CPU is my favorite sort order, but sometimes you need to add some caveats to that.
Now, one good caveat to add to that is that some queries need to have some minimum number of executions in order to be considered, you know, part of the regular user workload and things that are worth tuning. Sort of generally, the more frequently something runs, the less chance there is of it having, like, a super high average CPU. Like, it very well could, but, you know, a lot of the times when I go and look for this stuff, you know, like, as I bump the minimum execution count up, the average CPU tends to trend down.
Like, there are going to be some outliers in there, but for the most part, that’s what happens. So, there is a, you do have to sort of strike a balance between, you know, what you, like, finding long-running queries and finding long-running queries that, you know, end users are the ones executing. So, the other thing to keep in mind, too, is something that I brought up in a number of videos is that, you know, not every query is going to be properly parameterized.
You might have, if you have unparameterized queries and SQL servers interpreting, or even if you have queries that are parameterized but not strongly typed, so things like strings end up getting, like, all different lengths when they get passed in. SQL Server is going to be interpreting those as brand new queries, and you’re going to get brand new query plans, almost all of them, unless you, you know, happen to match something exactly. You might see, like, a couple of you get reused, but, like, those would only have a, might only show a single execution, but there would be many sort of variants of that query that all got.
Like, like, the same or a similarly, or I’m not even going to call the execution plan bad. I’m just going to say that the query didn’t perform well. So, there might be, like, you know, what looks like one query that didn’t perform well, but it’s actually just one query that’s a variation of, like, 50 queries that all got, you know, all took a long time, but they just had, like, a couple few literal values different in them.
So, you know, sometimes you don’t want to set that minimum execution count too, too high because you’ll miss out on a lot of the stuff that, you know, could be painful to end users, but just may not appear to be because of the execution count that you see from the plan cache. I don’t know. Usually, when that’s the case, though, you’ll see, like, five or six queries that all, like, look like the same beginning of a select statement or something.
And then as you get further into the query text, you’ll find the literal values that caused the SQL Server to interpret them as different plans. But, you know, again, all things you learn over time. One particularly interesting sort order that SP BlitzCache has is unused memory grant.
So unused memory grant is good because the plan cache actually has something that QueryStore doesn’t in that the plan cache tracks how much memory a query was granted and how much memory a query used. The QueryStore only tracks how much a query used. It doesn’t track what the grant was.
I would imagine this has something to do with all the memory grant feedback stuff, maybe. I’m not sure. But I don’t know. It’s just, it seems weird to me for QueryStore not to have that.
It’s just, I don’t understand what led to that design choice. So, but these are good things to track down. You know, by default, SQL Server will give 25, well, 20 to 25% of your server’s max server memory setting to a single query.
Right? The more memory you have in a server, the higher your max server memory setting goes, the higher that memory grant can go. And a lot of the times just due to, you know, the bad estimates or, you know, lots of reasons why SQL Server might not come up with a great estimate.
You could overestimate the number of rows by quite a bit. And overestimating the number of rows will inflate your memory grant. So I’m actually working with a client now that has a couple terabytes of memory in their server.
And there are regularly queries that ask for an excess of 300 gigs for a memory grant, but only end up using like 200 or 300 megs of it. In their case, it’s a slightly easier thing to take care of in one swath because you can change resource governor to change that max query percent to a much lower number that’s more suitable to query, to servers with a lot of memory on them to reduce query memory grants down sort of globally. But if you’re on standard edition, you’re screwed and you have to use like the max grant percent hints if you can.
Query store hints will be really useful for that because, you know, we’ll have a little bit more control over stuff. I don’t quite trust the memory grant feedback mechanism to take care of things the way it should in all cases. So, you know, we have that to contend with.
Another good way to look at your plan cache. And this is a great way to find non-inlineable scalar UDFs, cursors, other things like that that have a really high frequency of execution. That sort order there is XPM.
That’s executions per minute. I don’t know why it’s XPM and not EPM. I could ask Jeremiah that someday. He’s the one who came up with that rambunctious little shortcut.
I’m not quite sure. You know what? I like it. XPM sounds cool. XPM sounds exciting. So that’s a good way to find really frequently run code.
You might also just find a lot of queries that like run hundreds of thousands of times for like a millisecond. Could be an application bug. Could be something that could be cached somewhere.
Who knows? Lots of weird little things like that you might run into. One thing that I do find myself using quite a bit though is using Blitzcache to only look for certain SQL handles in the plan cache. And this actually comes back to something that I covered with SP Quickie Store where you might be looking at the blocked process report or you might be looking at the XML deadlock report.
And, you know, when queries lock and deadlock, you know, it’s not always obvious from the query text in those reports what the plan would be and why they end up deadlocking and locking so much. So, but in those reports, they have SQL handles in them for the queries that, you know, SQL Server thinks were involved in the blocking and deadlocks. It can sometimes be misleading, but that’s a story for another day.
So it’s always, I find it useful to look in the plan cache to find by SQL handle queries that come out of there. It’s been my experience for some reason, you know, well, it’s been my experience that for some reason, like finding things by those SQL handles and query stores, then a lot of, a lot more misses than hits. I’m not sure why, it might, might, might be the auto capture mode or it might be some other, you know, internal thing that is keeping queries out of there.
But I find that I have much better luck finding an execution plan in the plan cache for a lot of the blocking and deadlock report queries that come up. So I end up using that quite a bit. And, and, and, you know, finally, of course, if you only care about like plans from a database, then, you know, you can filter zoom right into that one single database and have a long and fulfilling day of laughing at query plans.
That’s usually, usually the stuff that I do with it. There are some other kind of interesting parameters too. I guess if I had to pick some others that might, might get you all excited.
Skip analysis is a, is a good one, especially if servers are really overloaded or you have really big execution plans or, you know, you just want quick results. Skip analysis will skip all of the XML stuff. So you won’t, you won’t do all the XML shredding and parsing to find particular issues in there, which is sometimes fine, honestly, because, you know, sometimes you don’t need it.
Sometimes you just need to get results back and you can figure things out on your own. But that’s a good way to get results much faster is if you use the skip analysis parameter. So that’s generally the stuff that I end up doing with Blitzcache.
Blitzcache is a very good store procedure for, I think, a lot of the, the warnings it gives you about what’s going on in execution plans are really valuable. You know, especially for, you know, some of the stuff that is not quite as easily visible to, to people who aren’t used to, used to looking at execution plans. You know, all the warnings that show up in the warnings column are really good and sort of high level overview of stuff that ain’t good.
It’s also, it’s also a good set of stuff to, to have in there because there are, there are query anti-patterns that it, that it picks up on that people might know can be bad. And seeing it show up in the warnings, they might do a little bit of research and dig deeper into just why they’ve made a bad choice in the way they’ve written the query. Table variables, scalar UDFs, things like that.
All good things to know about. So, I just ate lunch before this one, so I’m a little sleepy. I’m going to have some, I’m going to have some espresso before I do this next video and then, I don’t know.
It’s also hot in here. These, these recording lights are, I feel like a rotisserie chicken. Sometimes I feel like I kind of look like one.
I get this pink hue. It’s unnatural pink, pinkish, orangish hue to me that, a little disturbing. I don’t know.
Anyway, I’m going to go make some espresso. I hope you enjoyed yourselves. I hope you learned a thing or two about SP Blitz Cash, about the, the, the plan cash, about, well, I don’t know. Love, life, dancing, caffeination.
I will, I will see you in another video. Don’t forget to do the old like and subscribe. I, I, I record as often as humanly possible.
All right. Cool. Thanks for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. 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_BlitzIndex To Find Indexing Problems In SQL Server Databases
Video Summary
In this video, I dive into the world of SP Blitz Index, a powerful tool written by Kendra Little that helps database administrators analyze and optimize indexes in SQL Server databases. I share my personal approach to using different modes of SP Blitz Index, focusing on how Mode 2 provides detailed information that can help identify which indexes should be kept, removed, or modified based on their usage patterns and performance metrics. I also discuss the importance of customizing the sorting of results to better align with specific database needs, highlighting examples like heavily read tables versus frequently written-to ones. Additionally, I touch on my skepticism regarding missing index suggestions from SQL Server, explaining why they might not always provide actionable insights and how to use them more effectively in certain scenarios.
Full Transcript
Erik Darling here with Darling Data. And at least for now anyway, right? Who knows what will happen over the course of the day. Maybe Microsoft will buy me for $10 million. In case anyone from Microsoft is watching, these are my terms. $10 million. I want a partner salary at level 69. And I want a matte black employee badge. Those are my terms and conditions. If you find them acceptable, you know where to reach me. Leave a comment here. We can figure out if those are legally binding or not later. So today, well, I guess today, there’s been a bunch of videos today. In this video, we’re going to talk about SP Blitz Index, written by the lovely talented, talented, hyper-ultra-cool Kendra Little, and just some of the parameters that I use to do analysis and get information when I’m working with clients so I can figure out which indexes should stay, should go, and occasionally which ones might need to be created.
So, just because there isn’t a lot of activity in my Stack Overflow database, the results of this are rather unimpressive. for some of the analysis portions. So, you know, there’s Mode 0, which gives you somewhat lighter detail than Mode 4, which really goes in and finds like every single problem that we can find. Mode 0 just tends to find some of the more critical ones that you should probably focus on first for some of your larger tables.
Mode 1 gives you aggregate information about all of the index and heap objects in your database. Mode 2 kind of scripts out all of your index definitions and gives you like a lot of information per index about what’s going on with them. Mode 1. Now, all of these are useful in different ways to figure out which tables to focus on.
One thing that I like to do, and this only works in Mode 2, is to sort the output a little bit differently than we might get it normally. So, in Mode 2, like I said, it scripts out, well, it doesn’t script out like it just spits out a script file of your index definitions. It definitely scripts out your index definitions, but it gives you a lot of other stuff too.
So, this is the one that I’m going to run just to show you the results real quick. And so, this is for the Stack Overflow 2013 database. It’ll tell you the schema, the object name, the index name, the index ID, details of the index.
You know, there’s a lot of stuff in here that is useful. It’ll get a little bit further over. It’ll tell you is where the stuff that I tend to focus on goes.
So, the last time an object was used, how many reads those objects have, how many writes those objects have, the size of the object is another good one, right? Rows, reserved MB, all that good stuff.
You know, this is a great column to look at if you’re trying to figure out, you know, maybe which tables to go after. And then, of course, way over here to the right are two columns that I use quite a bit. You have T-SQL to drop indexes and T-SQL to recreate indexes.
So, if you were to say recommend dropping an index on a table and then at a later date someone said, you know what, turns out there’s this query that runs every three months that really likes that index. You would have the create SQL for that index to get that index back.
So, that’s a useful thing to have. Of course, there’s also the option to disable the index, which you could just, you know, right click and rebuild from the GUI if you’re that kind of DBA. And that would make life a little bit easier.
But here in SP Blitz index land, we have drop T-SQL, not disable T-SQL. So, why do I do this? Why do I order results differently? Well, depending on the database, we might care about different things in here, right?
We might have different objectives for what we want to look at. But looking at indexes just sorted by the number of rows or the size of a table might not lead you to the tables that are most problematic in a database in all circumstances. I think a good example of that is, you know, let’s say you have, you know, a trillion row audit table in your database.
The only thing that ever happens to it is singleton inserts. It’s never a point of contention or a problem for end users. But here you come along looking at these results and you say, oh, my God, we have a trillion row heap.
It’s got to have a clustered index. Got to have a clustered index. Everything’s going to, the world’s going to burn if we don’t have a clustered index. And then you go and try to create that clustered index and fill up the transaction log and the server crashes and burns and no one likes you anymore.
I’ll tell you that sometimes you need to focus on different things. So looking at tables that are heavily read from might be a good place to start looking if you wanted to, you know, maybe figure out if there are any indexes that you should have on that table. Looking at tables that are heavily written to might be a good place to start if you want to figure out which tables you might want to clean up indexes on, right?
Get rid of unused and get rid of unused indexes, merge together overlapping indexes that have, you know, sort of matching key column patterns, things like that. Now merging together like like say you have two indexes that have the same key columns, but then slightly different includes you could you could narrow down like two, three, four or more indexes to one index. Just merging those things all together.
So there are a lot of things that you need to consider when you’re figuring out which tables and indexes you want to go after. There is another mode to this that I’m not including here, and that is missing indexes. And the reason that I’m not including that mode, it’s a number between one and four that is not on this screen, is because the more I work with the missing index feature in SQL Server, the less impressed I am with it.
It’s very where clause centric. So if you have columns that you are joining on or ordering by that do not also appear in the where clause, then SQL Server just sticks them in the includes and that may not be the best choice. It also doesn’t give a lot of thought to column order in the index. Sure, it separates columns into equality and inequality, but the only ordering you get, the only consideration for what order columns are in within those groups is their ordinal position in the table.
So like if you think about a column, you know, like table whatever, column A, B, C, D, E, column A is first ordinal position, column E is a fifth ordinal position, and everything else in between fills in the blanks. SQL Server doesn’t give any thought to selectivity or data distribution or anything like that when it gives you these missing index requests. So they are not always terribly insightful.
The other problem is that there is no deep thought put into the missing index requests. Missing index requests are quick, sort of like pre-optimization opportunities for where SQL Server estimates, you know, those cost percentages which are useless, where SQL Server estimates which operators will be the most costly, and sometimes SQL Server will say, oh, look at this clustered index scan.
It’s going to be 90% of the query cost, right? 90% of the cost within the query is a clustered index scan. Then you run the query and get the actual execution plan, and that clustered index scan takes like 200 milliseconds.
And you’re just not going to save the day very often by solving a 200 millisecond problem in a query that runs for 30, 40 seconds. You see? See what I mean there?
So, the missing indexes can be useful points of correlation. So if you find a query that runs a lot, or let’s say, sorry, let’s say you find a query that runs slowly, but maybe it gets a different execution plan every time it runs because it’s not properly parameterized, or, you know, someone, your app developers don’t strongly type their parameters, or, you know, you’re using the bad kind of dynamic SQL you can catenate things in.
So, you know, you could have a lot of different instances of a query, long-running query that, you know, would, like using a plan, but, you know, you wouldn’t have the sort of overall metrics. So you could use, like, if that long-running query had a missing index request in it, you could use that to sort of, that missing index request, the number of uses for that to kind of correlate, well, how many times would a query have used this? So, missing index requests are useful data points, but they are not like the actual, you know, hand to God, biblical truth of what indexes your database should have in them.
There’s also a lot of things that, a lot of the times when there, like, should be an index where SQL Server doesn’t suggest one. I think my favorite is when you have an eager index spool in a query plan, and that signals that SQL Server is just creating an index for you every time the query runs, but then it throws it away afterwards, and you don’t get a missing index request either in the query plan or in the missing index DMVs when you have an eager index spool.
So there’s a lot of sort of blind spots to the missing index request, so I don’t generally spend too much time with them until I have a set of long-running queries that I want to tackle, and if, by happenstance, there’s a missing index request in them, well, you know, then I might go see how much that missing index request pops up. So, anyway.
The last time I tried to record this video, I got to the very end, and you know what happened? There was, like, 50,000 sirens went by my house, and someone started trying to FaceTime me, and there was a phone ringing. So, I don’t know.
Maybe that was some higher power telling me that that was a bad recording of the video. So, now you have this one. I hope you liked it.
I hope you enjoyed yourselves. I hope you learned something. If you enjoy this sort of content, I would urge you to hit the like and subscribe buttons so you can get more of this content automatically from YouTube. You don’t have to, you know, just hit F5 on my page and refresh it like it’s SBHoo is active.
We’re looking for new videos every five minutes. Jamming it in there. So, yeah. I got, I think, two more.
Blitz Cash and Blitz Lock that I’m going to talk about. And then, yeah, we’re going to wrap up with those two. And then, I don’t know what I’m going to do next.
I haven’t quite decided yet. Still trying to find my inspiration. Maybe I’ll crochet something or something.
I don’t know. Anyway, thank you for watching. Can’t find my, can’t find my mouse cursor.
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.
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.
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
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.
More Updates to sp_QuickieStore and sp_PressureDetector
Video Summary
In this video, I discuss updates to two of my favorite stored procedures: SP_Quickie_Store and SP_Pressure_Detector. Starting with SP_Quickie_Store, I added a user-requested feature that allows filtering queries based on whether they come from modules or are ad hoc pieces of code. This is achieved by checking the object ID in query store; if it’s zero, the query is considered ad hoc, while non-zero IDs indicate stored procedures, triggers, or functions. SP_Pressure_Detector received more attention with several improvements, including the ability to skip query results and weights, as well as adding CPU time to the result sets for a better comparison with uptime. I also truncated some decimal places in memory-related data points to make them more readable without losing precision where it mattered most. These updates aim to provide clearer insights into server performance and help you troubleshoot issues more effectively.
Full Transcript
Erik Darling here with Darling Data. Recently made amends with nice folks at Bare Gut Magazine. They offered me a bit of an olive branch, a peace offering. They named Darling Data the sexiest SQL Server consulting firm in all of America, Europe, Asia, Africa, I think I missed one. Definitely Australia, certainly New Zealand. So, you know, feeling pretty good about that, feeling nice about myself. Got my glow up. That’s the, that’s the, is that the right thing to call that? Anyway, um, I have some additional improvements, updates to two of my, favorite store procedures in the entire world. Uh, SP underscore, quickie store and SP underscore pressure detector. Um, let’s start with quickie store because, uh, it’s what the script is telling me to do. Uh, so the thing that I added here was actually a user request. Don’t get too many of those. That’s cause I write such good scripts, but, um, uh, what they wanted to do was to be able to get, uh, uh, queries, uh, queries, uh, either only from store procedures or only not from store procedures. And I guess I should take that back a little bit only from modules or only not from modules. See, uh, in, in query store, uh, there’s, uh, this is object ID column in one of the, one of the DMVs. And if the object ID is zero, then it is an ad hoc query or it is dynamic SQL or it came from an application or something. It did not come from a query.
from a module inside of SQL Server. If it came from a procedure or a trigger or a function or something like that, then, uh, the, the object ID will not be zero. It’ll be greater than zero. So, uh, I’m not really taking any steps right now to differentiate between the different types of modules. Um, it seems like it would just be kind of annoying to do. Uh, honestly, that’s it. It sounds annoying to do. I don’t want to do it. So, uh, but you can use, um, cookie store to look for either ad hoc pieces of code like this, where everything will say ad hoc, or you can look for, uh, modules. Now I’m going to, I’m going to let you in on something. Uh, I don’t, don’t, don’t currently have any store procedure stuff, uh, from any like demo workloads in my query store. So that came back empty.
But, um, I’m going to let you in on something here. The only thing that this checks is if, uh, the query type starts with a, mostly because, uh, some people are going to put a space in ad hoc. Some people aren’t. You can do either one. Um, I don’t, it doesn’t matter here. Uh, but, uh, people are going to type something like procedure or function or whatever in here. Uh, and, uh, I’m, uh, I’m only really checking if this starts with a at the moment. I’m not taking any heroic steps to, uh, to, to, to get you more information there.
Uh, for that I do apologize, but, uh, mostly people just want stuff that came from something or that came from nothing. That’s generally how it works. Uh, and of course, uh, the ever helpful help parameter will get you information on, uh, all sorts of things like new additions to parameters. Cool. Hope you like that one. Uh, we also have updates to SP underscore pressure detector.
Now, this one, uh, got a little bit more attention this time around, mostly because, um, uh, the more stuff I add to it, the more stuff I realize I don’t always want to see. There is some stuff that is opportune to see, uh, if you’re just trying to get, like, a complete picture. But when you’re really trying to drill into certain things, you don’t always want, like, sort of semi-unrelated results back.
So, um, one thing, one thing that I added, uh, was the ability to skip, uh, getting query results. And by that I mean getting a list of running queries. Uh, I’ve talked about in previous videos about SP pressure detector, how you can, um, uh, look at, find queries that are contributing to CPU and memory pressure currently on your server, which can be very useful if you’re continuously troubleshooting stuff.
Um, I should probably add a query-only parameter, but, uh, I just use SP who is active for that, so… I don’t know. Maybe I won’t. Uh, so we can skip queries.
Uh, we can also decide if we want to skip weights. Maybe we don’t want to see weight stats every single time. Uh, that’s a, it’s a interesting thing to have there. Uh, some other stuff that got added.
To the result sets. Uh, a couple tweaks that I made here is, uh, I’m going back to one of the, uh, resource governor DMBs that, uh, tallies, uh, all the CPU time that workload groups have used. And, uh, I’m getting now the hours of CPU time.
And I’m getting that because sometimes when you look at the hours of uptime, and you look at the hours of weights, the weights can look really low compared to, uh, how many hours your server has been up.
And that’s, like, if a server is just, like, busy during the workday, like, 9 to 5, and then does nothing from, you know, 5 p.m. to 9 a.m. Like, there’s not really a whole lot of overnight activity on there.
Uh, then the weights aren’t going to be accumulating so much then. And, um, I wanted to add CPU time to the results to kind of give a better picture of, like, okay, the server’s been up for this long.
And that time, queries have used this many hours of CPU, right? And since we have that, we can kind of figure out, like, sort of generally how the weight stats compare to, like, how long queries, like, how much CPU has been used, like, how long queries have been running, stuff like that.
So, uh, I mean, I know it’s not perfect because, you know, parallelism and other stuff, but it just kind of gives you a, like, a sort of, like, different data point, like, a different angle on, like, okay, like, you know, maybe, you know, hitting, you know, X amount of hours of some weight and, you know, like, some much larger number of hours of uptime doesn’t look that bad.
But, like, compared to, you know, how much time you’ve spent, like, actually running queries, how high is that weight? So some servers are, like, off the rails and, like, have, like, you know, like, 80 times, like, parallelism weights compared to uptime, like, like 100 hours of uptime and, like, 8,000 hours of parallelism weights.
And you’re like, oh, well, we have some things to talk about. But I wanted to add this for the servers where that distinction isn’t as apparent. Another thing that I did was I realized after a lot of time of using this that having nine decimal places in some of these results was not terribly useful.
So all of the not terribly useful nine decimal place results have been truncated to two decimal places. This used to be a whole mess of stuff going across here. I shortened that down to two.
I’ve also done that for the memory stuff because, you know, having nine decimal places of a memory grant isn’t saving anyone’s day. The places where I didn’t change that are in any weights that are related to time. So if you get query weight stats, or sorry, if you get, like, when you run a query, when you run the store procedure to get queries that are currently active, like how long the queries have been running, how long the queries have been waiting on, like, a particular weight, that isn’t truncated at all.
Like, I want you to know exactly, as exact as possible how long those things have been happening so you have a better picture of just how fast or slow a query is. All right.
Cool. Some other stuff that I did specifically for the memory section. Just to sort of keep with the basic theme of the script, I moved the section of queries that are asking for memory down to the very bottom so it matches what the CPU pressure section looks like.
So you get all of the sort of, you know, above-board information up here, you know, how memory is currently being used, if you’ve had any low memory stuff, what the current, you know, semaphore query, like, query memory grant information stuff is, and then finally the queries that are asking for memory grants.
I also added to this section. This section is a real, like, sort of, I don’t know, it’s a potpourri of different data points. And I really like it because it gives me a better idea of, like, what’s asking for stuff where.
So, like, having the total database size compared to the total physical memory in the box, knowing what max server memory is set to compared to the total memory in the box, knowing if lock pages and memory is turned on.
All this stuff in here is all sort of very useful when we start looking at, you know, weights correlating, like, if there’s pressure on the server, like, what can we do? Like, how, like, where is it coming from, right?
So one thing that I, a column that I added to this result set is the total number of forced, of, sorry, of reduced memory grants that have occurred on the server. This should generally be sent startup unless someone alters their workload groups and clears that.
I don’t think that’s a very common thing. But, you know, that can happen. I haven’t really thought of a good way to alert people if that’s been done or not, just because I think it’s going to be such a rare event that I just don’t think I want to deal with it.
So this is good here. Because, like, all this information going to, like, sorry, like, there is sort of what’s going on currently. And I think it’s very useful to have this data point knowing how many times a query has been forced to run with a reduced memory grant because it sat there waiting to get memory, like, the amount of memory that it wanted and couldn’t.
So I think that’s a pretty good thing to have. And that’s a pretty good data point to have. And there is, like, historically, how much has memory pressure sucked on this server? Some stuff that I added to specifically the CPU section.
One thing I did to make the, well, two reasons that I did this. One is so that it better matches other places where there are XML clickable columns. I separated this back out.
This used to be down in this result set, but I moved it out to its own result set. And another thing, like, A, it matches the rest of the server procedure a little bit better, where, like, if you run this to get everything, let me just go give you an example of why.
So if we run this to get everything, like, the tempDB info is a separate thing, and the low memory clickable is a separate thing. So now the CPU details are separate things.
So the other reason that I did that is because I added some new columns to this section. I added total active request count, so how many, like, requests are currently active. This column is in the DMV.
It’s not documented terribly well what it does, but it looked interesting to me. So I added it. We’ll see if it survives the test of time or not. How many, the total blocked task count.
So if any queries are blocked, we can get a count of those to kind of figure out, like, okay, well, like, you know, compared to how many things are, like, how many requests are there, how many are blocked? It could be very interesting to see that on servers where, you know, you’re troubleshooting a blocking issue.
And I also grabbed the total active parallel thread count. I think this will make thread pool demos even more interesting. So those are all available over here.
It’s a total active request through total active parallel thread count. These four columns are brand new. I also added a change a little bit. Well, I mean, I didn’t change the way I get thread pool weights.
I used to just select from the DMV if there were thread pool weights. And if there weren’t on, then you got, like, a blank result set. So now I’ve changed it so that if there are no current thread pool weights, it tells you there are no current thread pool weights.
And if there are thread pool weights, then you will see information on the thread pool weights. It will be a list of queries with, like, you know, their null session IDs, how long they’ve been waiting, and the thread pool weight. So that’s a little bit more specific there.
As usual, for all of these things, SP Pressure Detector has a very helpful help section. If you have questions, comments, or concerns about the store procedure, you should always use the at help parameter. And if you run into issues with any of these store procedures, please, pretty, pretty, pretty, pretty, pretty, pretty, please, use GitHub to ask questions, report bugs, request features, all that stuff.
My GitHub repo is listed pretty much everywhere in the world, including, you know, usually where you have to go get these things from. It’s not like you can download them from my website. Go to GitHub, get the most recent version.
And then have a lot of fun. That’s it. All right. Cool. Well, those are the new updates, changes, additions, no subtractions to SP Quickie Store and SP Pressure Detector. I hope you like these store procedures.
I hope you find these store procedures to be of some utility to you. You know, I spend a lot of time working on them. And, you know, if I could only use them for me, well, I don’t know.
Maybe the world would be a less safe place for SQL Server. I don’t know. Anyway, thank you for watching.
And if you enjoyed this video or you enjoy my other videos but not this video or some mix and match there, remember to like and subscribe. You know, it’s the thumbs up and the bell thing so that you can be alerted to all of my miraculous, wonderful, now officially awarded sexiest SQL Server content on the planet. And I’m going to record some more stuff after this because it’s Saturday and I finally don’t have anything else to do.
Oh. All right. See you soon.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. 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.
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
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.
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
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.
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
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.
