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.

A Gift of Script for 2009: Who is Active, Redux

Last year on December 31 I posted part of a larger monitoring script that I had been working on for a few months. I received lots of great feedback on the little script in both the comments and from people I was working with, and over the course of the last year I estimate that I have invested at least a couple of hundred hours in the script. The result of all of this time is attached, and I hope that this script is as useful for others as it has been for me; I've been using it pretty much nonstop while modifying it and it has become an indispensable part of my toolkit.

Alas, while I had planned to write a comprehensive blog post on the script for publication today, I simply don't have time to do it correctly at the moment--but I wanted to get the script out on the 31st in order to create a kind of tradition. So instead of telling you everything it can do, I will ask that you instead take a look inside the script, where I've done a fairly good job documenting both the parameters and the outputs.  I'll do a followup post soon (I hope) and do a better job of telling you about the various options.  In the meantime, I'll leave you with a list of the script's major features:

  • Wait stats collection, if you enable the GET_WAITS switch. This mode aggregates the various resources that the tasks associated with the session are waiting on, and lets you know how long the tasks have been waiting.
  • Collection of lock information, if you enable the GET_LOCKS switch. A lot of effort went into this particular feature--all of the locks for each session are aggregated in an XML format, and work is done to map the various identifiers associated with each lock to actual object names so that you don't have to do the footwork yourself.
  • Information about sleeping SPIDs holding open transactions, if you enable the GET_TRANSACTION_INFO switch. If you're being blocked by a SPID that is not active, you should be able to find out about it. This option lets you do it.  As a bonus, it also pulls information about transaction log writes in any database in which the transaction has done a write.
  • Collection of the "outer" command/batch, if you enable the GET_OUTER_COMMAND switch. One of the comments on the script I posted last year asked why, if you issue SQL like "ALTER TABLE dbo.xx ALTER COLUMN yyy FLOAT NULL", you'll end up with statement text that looks like "UPDATE [zzz].[dbo].[xxx] SET [yyy] = [yyy]". This is an internal representation of what's actually happening, but in many cases is not esepecially useful for end-users. So if you want to see what was actually run, now you can.
  • Every expensive or potentially expensive option is now parameterized. I spent a lot of time tweaking performance and with no options running the thing is pretty fast. Enable some options--especially lock collection--and it will slow down considerably, so use these advanced features with care.

Enjoy, and please keep sending feedback!  Let me know what else you would like to see, if you find bugs, etc.  I'll post updated versions as major changes are made.

To finish I would like to thank a few of the people who had an impact on the evolution of this script over the past 12 months: Michael Condon, Erland Sommarskog, Aaron Bertrand, Louis Davidson, and Jonathan Kehayias.

Have a great 2009, everyone!

Published Wednesday, December 31, 2008 10:49 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



Denis Gobo said:

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

December 31, 2008 10:37 AM

Moham Mawla said:

Great job Adam , happy new year for you

January 3, 2009 4:00 AM

Adam Machanic said:

Happy New Year to you too, Mohammed!  I hope all is well in Egypt.

January 4, 2009 10:06 AM

Bill said:

Msg 8114, Level 16, State 1, Line 1

Error converting data type bigint to int.

I ran your script as is and i got above error..

January 25, 2009 4:09 PM

Adam Machanic said:

Hi Bill,

Hard to say what's causing that, but it has to be one of a couple of places that use dynamic SQL.  I just checked and it looks like I did get a couple of datatypes wrong, but it's unlikely you would hit them (I hope) -- the main ones are reads/writes.  If you select from sys.dm_exec_sessions do you see any sessions with more than two billion reads or writes?  That seems like a lot to me, but I know some systems keep sessions connected for a long, long time, so I guess it could build up...

Also, did you use any parameters?

I'm working on a fix for the datatypes I know I got wrong, and also am adding some new functionality; I hope to have a new version out in a day or two.  Thanks for the feedback!

January 26, 2009 12:34 PM

Bill said:

Thx Adam

No, I ran exec_sessions and nothing with more than 2 billion reads or writes.

I ran it without specifying any parameters. Thx Bill

January 28, 2009 3:18 AM

Adam Machanic said:


Can you drop me a line at amachanic [at] gmail [dot] com ?  I would like to send you a modified version and see if it fixes things.

January 28, 2009 10:59 AM

aspiringgeek said:

Adam, I want to let you know I used an older version along with my SQL Deltoids with great success at a recent engagement.  I'll look forward to trying the new! improved! version at my next opportunity.  Among other clever features, the XML column in the result set is wonderful.

February 4, 2009 9:56 PM

Adam Machanic said:

Who is Active? is a comprehensive DMV-based monitoring script, designed to tell you at a glance what

February 18, 2009 11:01 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