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.

Less Data is More Data (A Month of Monitoring, Part 5 of 30)

This post is part 5 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.

You’ve downloaded Who is Active for the first time.

You’ve unzipped it.

You’ve taken a quick peek through the code to make sure I didn’t send you a rejuvenated version of Slammer.

You’ve installed it on a test server so that you can make sure it won’t take down your production environment. (Always a good idea.)

And now, the moment of truth arrives. You fire up SSMS and—hands ever-so-slightly shaking, breath held, body quaking with anticipation—you type the following:

EXEC sp_WhoIsActive

Your hand slowly approaches the F5 key. Your heart racing, you slowly depress the plastic square and...


Nothing at all. Where are the results? Is this thing broken? Um, hello?!


Unfortunately, this is apparently a common scenario for new Who is Active users. (The empty result set. The rest? Well, who knows.) I have received many e-mails and blog comments from people asking why this large, complex stored procedure they’ve just downloaded doesn’t seem to actually do anything when they run it.

The answer is simple: It didn’t show you any information because there was nothing interesting to show you. Unlike sp_who, sp_who2, and most of their brethren, Who is Active—by default—only shows you the sessions that you’re likely to want to see. If you installed it on a test server, or your laptop, or some other non-production machine, you’re probably not going to see any output in the default mode because there is nothing to show you. There are no active requests, and no one has started a transaction and left it sitting around waiting for Who is Active to drop by and monitor. The lack of output in this case is a good thing. It means that you don’t have to waste your time trying to find the meaningful information in a sea of worthless data. I explained this previously in the post on design philosophy.


Seeing More Data – The Basics

Sometimes you actually do want to see all of the data that Who is Active hides by default. One such time is when you’ve first installed Who is Active and want to get comfortable with it. No problem; if you’d like it to return one row for every session (and more than one row for some sessions, if they’re using Multiple Active Result Sets), you have to work with three options:

EXEC sp_WhoIsActive
    @show_sleeping_spids = 2,
    @show_system_spids = 1,
    @show_own_spid = 1

Once you’ve done that, the output will look much closer to what you’re probably used to seeing.


The three options shown above will be covered in detail in a future post, but in brief, here’s what we’ve done:

  • Changed the default mode from showing only active requests and/or sessions with open transactions to showing all connected sessions
  • Changed the default mode from hiding system sessions (even if they fall into the above category) to showing system sessions
  • Changed the default mode from hiding your own session—the one calling Who is Active—to showing it along with the rest of the output

Many people are uncomfortable, at first, with how little output they see when they run Who is Active. My recommendation: Try to let go of the need to see everything that’s connected to your SQL Server instances. There’s simply not enough time in the day to bother with that level of detail.



Yesterday’s task was probably a test of your web searching prowess more than anything else, but hopefully it got you thinking a bit about metadata permissions. I asked you to identify which DBCC commands you’ll have access to if you’re granted the VIEW SERVER STATE permission. The answer is: DBCC SQLPERF and DBCC INPUTBUFFER. Who is Active uses INPUTBUFFER—but that will be covered in another post.

Today we’ll take a break from the usual questions and answers. Your mission is to install Who is Active on whatever servers you feel comfortable with and start playing with the default mode and the three parameters listed above. If you feel like putting something in the comments below, it would be very interesting to hear about your production servers: How many rows are returned by the default Who is Active mode? And how many more rows do you see when you modify each of the three options?

Published Tuesday, April 05, 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



Ken Trock said:

Got a few SPIDs on one of our production servers under the default configuration.

So far I've noticed that you're able to get the whole sql_text for an active SPID. I know they cutoff routinely in Performance Dashboard. Maybe that's 2k or 4k characters. Will have to wait until we get a real massive query :) This is cool.

April 5, 2011 4:00 PM

Neil Hambly said:

Cool Series - will add to my daily reading list (need to move some stuff of that as it's getting very big)

BTW Did seen an odd thing with @show_system_spids = 1 I noticed the system one have a {start_time} as dates in the future I.E

only seems to be on these Builds: 10.xx

dd hh:mm:ss.mss session_id sql_text login_name

90 15:16:10.000 9         NULL sa

start_time      request_id collection_time

2011-04-16 06:13:45.000 0 2011-04-06 11:15:14.080

April 6, 2011 6:46 AM

Allan T said:

Great series.

A colleague told me about this script.

I have used it at a customer site to troubleshoot some issues.

I am looking forward to reading through this series as is progresses.

I have run into one inconvenient issue. It is difficult to export the results of sql_text to--say--Excel. It generates a new line after <?query-- and then again before ..?>, shifting all of the other columns.

As the series progresses, I am hopeful for discussion on how to customize this output.

April 7, 2011 12:44 AM

Adam Machanic said:

Hi Allan,

There will be a post on output customization. Whether or not it will fix your Excel issue is another question entirely. I have some other ideas for you, though. Stay tuned to the series and follow up later this month if you haven't figured it out yet.

April 7, 2011 9:22 AM

Adam Machanic said:

Hi Neil,

Dates in the future are of definite concern! This is not something I've ever seen before. What does sys.dm_os_sys_info report in the sqlserver_start_time column?

April 7, 2011 9:23 AM

Neil Hambly said:

Hi Adam

it seems that this is only showing dates in the future only for background spids, I have checked this is happening on both 2005 & 2008 systems, it maybe due to high values in sqlserver_start_time_ms_ticks perhaps

Example of one of the systems




spid 1

dd hh:mm:ss.mss session_id sql_text login_name wait_info tasks CPU tempdb_allocations tempdb_current blocking_session_id reads writes context_switches physical_io physical_reads used_memory status open_tran_count percent_complete host_name database_name program_name start_time request_id collection_time

35 15:16:10.000 1 NULL sa NULL                  1             840,299                      0                      0 NULL                         6                       1              147,901                         1                      1                  0 background                  0 NULL master 2011-04-18 13:45:22.000 0 2011-04-16 20:06:03.357

EXEC sp_WhoIsActive

   @get_task_info = 2

   ,@show_system_spids = 1

Select * from sys.dm_os_sys_info    

April 16, 2011 3:13 PM

Adam Machanic said:

Thanks to Neil running some more tests on his end, this issue has been fixed. A new version will be posted shortly.

April 17, 2011 10:44 PM

Adam Machanic said:

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

April 19, 2011 10:06 AM

Adam Machanic said:

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

April 20, 2011 10:40 AM

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

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