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.