SQL Server Community Tools: Filtering sp_WhoIsActive Results

Oh Baby You

---

On really busy SQL Servers, or on SQL Servers where some ninny activated Service Broker on hundreds of databases so you have hundreds of sessions constantly sitting there waiting for conversations, being able to filter out stuff you don’t want to look at is an absolute killer feature.

And boy can sp_WhoIsActive do that really well.

You can filter in or out:

• session id
• program name
• database name
• login name
• host name

Most of the uses I have for these filters is to watch activity for a specific session id or for a specific database.

In a moderate bit of fairness to Service Broker, I’ve only had to screen out hundreds of sessions from it a few dozen times.

Innies

---

To only include certain sessions in the output, do something like this:

EXEC sp_WhoIsActive
    @filter = '138',               
    @filter_type = 'session' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @filter = 'StackOverflow',               
    @filter_type = 'database' /*session, program, database, login, host*/;

What’s really cool is that you can use wildcards in your filters (except session) to do something like:

EXEC sp_WhoIsActive
    @filter = 'CrappyApplication%',               
    @filter_type = 'program' /*session, program, database, login, host*/;

And of course, you can filter stuff out, too.

Outies

---

To do that, you just need to use the not filters:

EXEC sp_WhoIsActive
    @not_filter = '138',               
    @not_filter_type = 'session' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @not_filter = 'StackOverflow',               
    @not_filter_type = 'database' /*session, program, database, login, host*/;

EXEC sp_WhoIsActive
    @not_filter = 'CrappyApplication%',               
    @not_filter_type = 'program' /*session, program, database, login, host*/;

Which, now that I’ve copy and pasted it in, I can’t think of too many reasons to ignore a single session id, but whatever.

This is a great way to filter out hundreds of Service Broker sessions that pollute the results because some ninny activated it in hundreds of databases, though.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% 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.