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.