How To Tell Which Queries Are Actually Doing Work In SQL Server Using sp_WhoIsActive
Video Summary
In this video, I delve into the utility of using `sp_whoisactive` to monitor query performance and identify work being done by queries over time intervals. I demonstrate how the `delta_interval` parameter can be particularly helpful when dealing with a long list of queries or when you need to quickly assess which queries are actually doing work versus those that might just be sitting idle. By showing examples where blocking is involved, I highlight how this tool can assist in troubleshooting and tuning SQL Server queries effectively.
Full Transcript
Lo and behold, it is my, what is this, let’s see, one, two, three, this is my fifth video of the day which satisfies my requirement to schedule five days of blog posts so I don’t have to think about it for another five days. You’re welcome. It’s a grind, but, you know, I hear SEO is important if you want to stay in business. People have to be able to find you and all that stuff, right? I don’t know if, I don’t know, I don’t know. I’m not good at that stuff. I don’t have, I’m not a marketing whiz. Obviously. If I were, hmm, I don’t know. If I were, I don’t know. If I were, I’d be able to find out. Or I could probably just do live videos. So, this video, we are going to look at how you can tell if queries are doing work with SP who is active. Now, I’m going to grant you this. If we just run a query that does a bunch of work, and we, you know, keep hitting F5, we’ll see various metrics and counters like CPU go up every time we run this.
Oops. We got to re-highlight that. And now it’s done. So, let’s try that again. So, like, where’s CPU? There we go. There’s CPU. And if we run this again, we’ll see that, oh, you know, CPU went up a whole bunch. But sometimes, but sometimes I run who is active, and I just don’t, I don’t remember all the numbers. I don’t, I can’t keep track of everything that ever happened.
All right. I’m not looking at all this stuff. And that’s when it becomes useful to use the delta interval parameter, which apparently Atom Mechanic is not a marketing whiz either, because delta interval is about the worst name for a parameter that does this. You know, too late to change now, but let’s say that we execute this query, and we hit this for the whole thing this time.
What it’ll do is it’ll take an initial measurement of what’s going on on the server, wait for five seconds. That’s the delta interval. Thankfully not a jet blue interval.
And then we’ll get some additional columns back. So, it’ll tell you, actually, I think over here is probably where things get more interesting. So, we’ll get the CPU, right, like how much it used, and then how much it used over that five seconds.
So, in a five-second period of time, it used about 30 seconds of CPU time. All right. So, we can kind of measure what queries are doing work using this.
If we add something else into the mix, and let’s just use blocking, because blocking is pretty easy. All right. And we really quickly run this, run this, and then run this.
We’ll get back the, well, kind of, little, slightly weird order here. All right. This query up here, which was being blockade.
Let’s get over to the CPU stuff. The query up top that was being blocked used absolutely no CPU over that period of time. The query that did the blocking used about 10 seconds of CPU in that time.
Now, of course, if we keep running who is active, we’ll see that this blocked query just keeps getting stuck and doing nothing. And since this modification query is all done running, that took about six seconds. That it’s not going to show doing any more work either.
But this can also be valuable in different scenarios, particularly troubleshooting blocking. If we scroll over here a little bit, it’s fairly obvious that a sleeping query is not going to be doing any more work. It’s done, right?
So that’s one thing that might clue you into some sort of code or application error that this query has finished working and is just sitting around like, well, now what? No one has committed or rolled back or done anything else. And this query up here is still stuck behind it.
So some other useful information you can get out of who is active when you’re troubleshooting stuff like blocking. But for me, I kind of like this, particularly if, you know, when a lot of my consulting work is, you know, someone hands me a list of queries to tune. And sometimes when I get that list of queries to tune, like I don’t know what to expect from them.
Like I don’t know if they’re going to be super fast or super slow or, you know, like which part or like rather if it’s like a big long store procedure, I don’t know which parts are going to have problems. And so it’s helpful for me to not only run SP who is active to see where things are getting jammed up or stuck, but also to use the delta interval parameter so that I can figure out how much work queries are doing over, you know, two, three, four or five seconds. Because at least I get a sense of if the query is really pushing stuff or if it’s maybe stuck on something else.
Like, you know, if I ran this and found that a query did, you know, one second of CPU work in a five second interval, I would be curious what was preventing that query from doing CPU work. What was it stuck on? What were we waiting on reading pages from disk?
Did we get blocked? Like there’s all sorts of things that can happen that can make a query sort of feel artificially or variably slow that are very interesting to the avid query tuner. So the next time you are looking at a server using SP who is active and you’re curious if the queries that you’re running are doing any work, especially if there’s a long list of queries and it’s hard to keep track of what all the numbers are, it can be very, very useful to use the delta interval parameter, no matter how poorly it’s named, so that you have a sort of a running log of how much work each query did over the span of time you looked at the server.
Anyway, that’s going to do it for me today. That’s five. I don’t know what tomorrow might bring.
Might have to figure some other stuff out. I hope you liked this video a little bit. I hope you liked this video enough to like the video, the little thumbs up dude out at the bottom. I also hope you liked this video enough to subscribe to my channel so that I can become a YouTube billionaire and have a YouTube house.
That’d be cool. It’s a house full of cameras always watching me. And then you, constant watcher, could constantly watch me do a SQL Server.
Wait. No. All right. I’m going to park this one here. Thanks for watching. I will see you in some stuff tomorrow when hopefully these horribly hot lights are making me sweat a little bit less.
All right. Have a good one. All right.
No.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.