THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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.

Who is Active? v8.82 - Harder, Better, Faster, Stronger

I've had a lot of great feedback since my last "official" Who is Active? release--v8.40, back in March--and since then I've made 42 distinct changes, bringing us all the way to v8.82. My versioning scheme is quite simple: one change, one increment, with occasional numbers skipped or major version incrementation if I do a really major change.

If you've been watching the SQLblog closely you may have noticed that I've spun up a "beta" program where I've been posting intermediate "builds" of the stored procedure. These builds represent the latest updates and are published pretty much immediately, which means I haven't tested them enough to declare them stable. Builds posted here on my main blog--such as this one--have been tested fairly heavily and I believe them to be in very good shape.

In the case of v8.82 I've made only one minor change in the past month and a half, and in the meantime I've been heavily using the code and have not encountered any issues. Nor have I received any change requests. So I am declaring v8.82 stable and ready to roll. You can get it from the following link, or at the bottom of this post.

 

Click here to download Who is Active? v8.82

 

So What Has Changed?

If you've been watching the beta builds you will have noticed the change log I've been keeping since v8.67. But that leaves around 20 changes unaccounted for. Between 8.40 and 8.67 I did a number of things: I implemented a number of minor bug fixes and several workarounds for strange ways that data is exposed by the various DMVs.

One change you'll quickly notice is that all of the numbers have been right-aligned. This is something that was originally requested by Aaron Bertrand, who later was unable to use the feature as-is because, as it turns out, his font choice in SSMS is not compatible with the way I made the feature work. I did a poll here on SQLblog and discovered that Aaron is fairly unique with regard to fonts, so I'm keeping the feature as-is. Personally, after the 10 minutes it took me to get used to seeing the data a new way, I found it to be a really great enhancement. I'm quite interested in hearing your feedback on how you like the change.

From 8.67 on I have a comprehensive change log, and next I'll go over some of the more important enhancements, in chronological order based on when I implemented them. There is a central theme here: each of these changes directly resulted from someone giving me feedback. But more on that below.

At the request of Joe Sack I added a parameter called @help which, when set to 1, returns information about all of the available options. Online help, in other words. This is much easier than pulling up the definition for the stored procedure and I've found it to be quite helpful on a number of occasions since making the change.

Jimmy May asked for an option to help find "block leaders", SPIDs that are at the front of the blocking chain. From this the @find_block_leaders parameter was born. It causes the stored procedure to add a new column, blocked_session_count, which is an integer representing the number of SPIDs blocked downstream. Sort by this descending (@sort_column='[blocked_session_count]', @sort_direction='DESC') and the block leader(s) will all sort to the top. I've already been able to use this one to debug a production blocking problem in a matter of around 30 seconds, which may have otherwise taken me minutes. Thanks, Jimmy!

After an e-mail thread with Michelle Ufford I decided to support simple wildcards in the @output_column_list parameter. Interested primarily in information about tempdb? No problem: @output_column_list='[temp%][%]'. Now everything about tempdb will show up in the leftmost columns, and every other column will show up afterward. No need to mess around and try to re-construct the complete column list.

Around this time I received a few requests for better filtering capabilities. I removed the @spid parameter and replaced it with @filter and @filter_type. The default filter type is "session" in order to support some form of backward-compatibility. Other valid filter types are "program", "database", "login", and "host". Wildcards are supported, so if you're interested in everyone logging in with hosts that start with the letter "C", I've got you covered: @filter='C%', @filter_type='host'.

An e-mail exchange including both Jimmy May and Joe Sack got me really interested in the Runnable Queue, and yet another enhancement was created. I added a wait type called "RUNNABLE" to the wait_info column. This wait type doesn't actually exist, but is rather an indication of how long tasks have been sitting on the queue. It's a way to quickly see when you're experiencing scheduler pressure.

 

Next Steps

At some point I'm going to fully document this thing. Really. But to be honest I'm much more interested in continuing to enhance it. If you've found it to be useful, please let me know. If you have feature requests, definitely let me know what they are. And if you find a bug, drop everything and e-mail me right away (well, within reason). My e-mail address is in the stored procedure on purpose; most of the enhancements to this stored procedure have come about thanks to users, and I really do want to hear from you. Don't be shy.

So without further ado, enjoy. And thank you to everyone--both those listed and not listed in this post--who helped make this version what it is. I really appreciate your help.

Published Thursday, August 20, 2009 12:03 PM by Adam Machanic

Attachment(s): who_is_active_v8_82.zip

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

Comments

 

Sankar Reddy said:

Thank You for updating it. This has been a valuable monitoring script in my environment and helped us many times without having to look at running multiple scripts.

August 20, 2009 12:41 PM
 

AaronBertrand said:

Just so people don't think I'm being overly pedantic, my only complaint about the right-justified columns is that they don't work well if you use a fixed-width font in grid output (I use Consolas).  For some reason it is right-padded by spaces equal to the number of digits in the value:

http://aaronbertrand.com/voodoo/who_is_active_output.png

I've been meaning to see if I could fix this, but have been distracted by way too many other things.

That all said, this is still a fantastic procedure and saves me a lot of manual work hunting for values in DMVs on my own.  Keep up the great work Adam.

August 20, 2009 2:17 PM
 

AaronBertrand said:

Sorry, not *equal* to the number of digits, but at least somewhat directly related.

August 20, 2009 2:29 PM
 

Phil said:

Adam, just having an issue creating the sp on one of my SQL 2008 servers.

SQL Server 2005 Express with Advanced Services - 9.00.4035.00 -- OK

SQL Server 2008 (SP1) - 10.0.2531.0 -- OK

SQL Server 2008 (RTM) - 10.0.1600.22 -- FAILED

Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 394

Incorrect syntax near '.'.

Seem SQL 2008 RTM doesn't like the CROSS APPLY syntax, but 2005 Express is ok with it.

August 20, 2009 7:35 PM
 

Adam Machanic said:

Hi Phil,

Interesting. It definitely works on 2008 RTM; are you by any chance trying to create the procedure in a DB set to a different compatibility level than 2005 or 2008? I get the exact same error and same line number if I set a database to SQL Server 2000 compatibility and try to create the proc in there.

August 20, 2009 11:38 PM
 

casm said:

Great way to retrieve the information about the parameters!!. Hope you don't mind if I use it in my own store procedures.

And, one more time, thanks for this superb script.

August 21, 2009 4:09 AM
 

Alvaro Mosquera said:

With version v8.82 the sp show several process with status "sleeping" runnig the query "exec sp_MSget_last_transaction @publisher_id = 0, @publisher_db = N'OracleSinfos', @for_truncate = 0x1". I think this process belong to the LogReader process of transactional replication  and are active since the SQL start. The version v.8.40 don´t show this process.

Is there anayway to filter this process?.

Thanks.

August 21, 2009 4:50 AM
 

Adam Machanic said:

Hi casm,

Enjoy!

August 21, 2009 9:45 AM
 

Adam Machanic said:

Hi Alvaro,

This is a change I forgot to mention in the post. In the previous version of the script, it showed all active requests by default, if you asked for transaction information you would see all SPIDs either with active requests or with at least one open transaction. Several people asked me to change that behavior and always show the SPIDs with an open transaction. I guess now we have a counter-case. I'll consider adding a new param that lets you control that.

August 21, 2009 9:45 AM
 

Linchi Shea said:

Despite all the DMVs, the sysprocesses table still offers a significant amount of information for identifying

August 21, 2009 6:12 PM
 

Michelle Ufford said:

Adam,

Thank YOU for all of your hard work on this excellent script.  I have now updated my servers to your latest version, and all is working well.  Your efforts are much appreciated! :)

Michelle

August 24, 2009 5:47 PM
 

Adam Machanic said:

Happy December, SQLblog readers! My gift to you, just in time for the holidays: The newest "official"

December 3, 2009 3:50 PM
 

David Wetherell said:

Adam,

This is cool, I love the built in help parameter ...

David

October 16, 2010 3:06 PM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement