THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Deciding What (Not) To See (A Month of Activity Monitoring, Part 9 of 30)

This post is part 9 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

A few days ago I explained that in the world of monitoring, Less Data is More Data.

The goal, again, is to avoid being distracted by a bunch of information that doesn’t matter. Your job is to find and fix the real issues, as quickly as possible.

It’s only natural that during the finding phase you might wish to focus in on some particular class of user, or application, or whatever. And that means filters.

By default, Who is Active filters out any session that’s not active or that doesn’t have an active request. It also filters out information about the session running Who is Active—i.e., you. The idea is that you’re probably not interested in what Who is Active is up to unless you either have way too much time on your hands, your name is Adam Machanic, or both.

If you would like to change any of the default filtered behavior, you can do so using a few options that I introduced in the Less Data is More Data post:

  • @show_sleeping_spids defaults to a value of 1, which means that the only sleeping sessions that will be shown are those that have an open transaction. If you would like to see all sleeping sessions, set the parameter to a value of 2. And if you wouldn’t like to see any, regardless of whether there is an open transaction, set it to 0.
  • @show_system_spids can be set to 1 to capture information about background and system processes. It’s generally not very interesting to look at these, with the exception of seeing activity being done by Service Broker activation procedures—and those sessions are already shown by default in newer versions of by Who is Active.
  • @show_own_spid can be set to 1 to capture information about the session you’re running Who is Active from. You’ll see a small chunk of the code that’s running, and it won’t be very insightful, but I’ve included the option for completeness.

Beyond the default filters, Who is Active supports the ability to do text filtering on various columns. To see how Who is Active deals with these text filters, let’s take a look at a sample set that might be returned by Who is Active:


This blat guy sure is running a lot of queries! I’m much more interested in what Adam is up to (go figure). To reduce the set to see only Adam’s activity, I can employ a filter:

EXEC sp_WhoIsActive
    @filter_type = 'login',
    @filter = 'Adam03\Adam'


This filter gives me an exact match on “Adam03\Adam” in the [login_name] column. And that’s great, but a lot more can be done.

Who is Active supports five filter types:

  • “session” filters on the [session_id] column
  • “program” filters on the [program_name] column
  • “database” filters on the [database_name] column
  • “login” filters on the [login_name] column
  • “host” filters on the [host_name] column

Session is special because of the five filter types it is the only one that is numeric. “Filtering” using the “session” filter on a value of “0” returns all of the sessions to be shown based on whatever other filter options you’re working with. A non-zero value will return information only about the session you’ve specified—again, subject to other filter options. This means that if you’re looking for information on session_id 96, but that session is sleeping, and you have @show_sleeping_spids set to 0, you’re not going to see any information. Always remember that the filters are additive.

The other four filter types are quite a bit more flexible, because they are pure text filters. And text filters support wildcards. The same kinds of wildcards that you can use for LIKE or PATINDEX. Meaning that you can write the above query in a number of ways, depending on how flexible you would like to be with which sessions are returned:

EXEC sp_WhoIsActive
    @filter_type = 'login',
    @filter = 'A%';

EXEC sp_WhoIsActive
    @filter_type = 'login',
    @filter = '%dam';

EXEC sp_WhoIsActive
    @filter_type = 'login',
    @filter = 'A[de]am03\[^B]dam';

Another option is to ask Who is Active to not show certain sessions. This is referred to as a “not-filter,” and works the same way. If you want to see everything except what blat is up to, you can do:

EXEC sp_WhoIsActive
    @not_filter_type = 'login',
    @not_filter = 'blat';

The not-filter option was added after some great feedback from Michelle Ufford, who was trying to use Who is Active on a system that was always busy running a huge number of automated jobs and processes. Since all of these had been running for quite some time and were rather uninteresting, Michelle wanted to see only the other, non-automated activity on the system. Not-filters support the same filter types as normal text filters, and also support wildcards. And of course, both filter types can be combined:

EXEC sp_WhoIsActive
    @filter_type = 'login',
    @filter = 'A%',
    @not_filter_type = 'login',
    @not_filter = 'b%';

The current set of Who is Active filters are, in my opinion, quite flexible, but I don’t feel completely satisfied. My goal is to eventually convert the entire filter mechanism to an expression language that supports arbitrarily complex filters. This would, of course, also have to be 100% injection safe, and perform extremely well, so it’s a bit of a dream at the moment. But it’s an interesting problem, so I’ll continue to work on it and perhaps I’ll crack it in a future version of Who is Active.



For today’s task I would like you to think a little bit more about the sysprocesses legacy view. Can you describe the purposes of both the [waittype] and [lastwaittype] columns?

Published Saturday, April 09, 2011 11:00 AM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Tony said:

Perhaps you could define complex filters as an XML parameter? I've done similar things in the past.

April 9, 2011 12:48 PM

Uri Dimant said:

Hi Adam

Thank you for that great blog series, I am waiting for more info from you :-) on  BLOCKING/PARALLEL query identifying.

April 10, 2011 3:40 AM

Adam Machanic said:

This post is part 10 of a 30-part series about the Who is Active stored procedure. A new post will run

April 10, 2011 3:57 PM

Adam Machanic said:

This post is part 28 of a 30-part series about the Who is Active stored procedure. A new post will run

April 28, 2011 9:03 PM

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM

Yaniv said:

how about filtering by duration?

September 7, 2015 12:24 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement