Who is Active? v9.57: Fast, Comprehensive DMV Collection - What's Really Happening on Your Server?

Adam Machanic — Thu, 03 Dec 2009 19:59:00 GMT

UPDATE, April 28 2011: Who is Active v9.57 is outdated. Please use v11.00 instead.

Happy December, SQLblog readers! My gift to you, just in time for the holidays: The newest "official" release of your favorite SQL Server activity monitoring stored procedure.

Click here to download Who is Active? v9.57

Since the last release--v8.82, from August of this year--I've made a number of modifications to the script, resulting in six interim "beta" releases. The uniting theme of all of these changes is more, better quality data, faster.

More data.

Two new core options were added at the behest of users: @show_sleeping_spids and @show_system_spids. These options cause the procedure to return information that wasn't previously available via Who is Active, about--you guessed it--sleeping and system sessions, respectively.
Another major change was adding a feature so that the script now shows blocking sessions whether or not they're included in the default filter criteria. This is not something you turn on or off--it just happens--and will ensure that if you're debugging a blocking scenario you'll automatically have all the information you need.
And for you query plan geeks out there, the full wait stats collection mode (see the "Faster data" section below) now returns node identifier information with CXPACKET waits--helping you to track progress of tasks as a plan is executed.
Finally, a small modification. The online help system (@help=1) now returns information about both the available input parameters as well as all of the available output columns.

Better data.

A few minor bugs were fixed, mostly having to do with the evils of MARS and the fact that the DMVs don't properly deal with MARS sessions in many cases.
Workarounds were also added for inconsistencies in how the DMVs report SQL handles, even without MARS.
Two features were added to help you get only the data you need when you need it, and not the data you don't:

Dynamic sort ordering, via a parameter called @sort_order, lets you pass in a list of columns and column directions by which to sort the output.
And "not" filters, implemented using parameters @not_filter and @not_filter_type, work exactly the opposite of regular filters. These are useful in those cases where you have a bunch of sessions that aren't of interest, and you don't want them cluttering your output.

Finally, I've changed the default output column order to something I think is a bit more useful. Don't like my selections? No problem--override me using the @output_column_list parameter.

Faster data.

This is the area in which I made the most modifications. A monitoring tool borders on useless when it takes a minute or more to return key metrics when your server is on fire, and alas, previous versions of Who is Active were doing just that for some users.
I've made major changes to the core queries in this version of Who is Active, bringing down query times from minutes to a few seconds in many cases.
In conjunction with these changes I added a new lightweight wait stats collection mode, which is the new default. This mode collects only the top non-CXPACKET wait, giving preference to blocked waits, so that you can see the worst problems without having to sort through a lot of output that may or may not apply.

If you miss the complete stats collection mode, it's still there--simply set @get_task_info=2 when calling the procedure.

I'm quite happy with this release, and I hope that it will help people quickly solve a number of tricky SQL Server problems. As always, your feedback is very much appreciated! Leave me a comment here, e-mail me (my address is in the script), or track me down at a conference. Most of the features in the past few versions are a direct result of requests I've gotten from users.

A huge thank you to those who tested and gave me feedback since the last version! Aaron Bertrand, Rajiv Jain, Michelle Ufford, Uri Dimant, and everyone else, I really am thankful for your efforts. To these people and all of my readers, I wish you a happy December and a prosperous 2010.

Until next time, enjoy!

Click here to download Who is Active? v9.57

Please ignore the text below. Putting in it for search purposes.

sp_whoisactive whoisactive sp_who sp_who2 sp_who3 sp_who4 sp_who5

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

Adam Machanic — Wed, 31 Dec 2008 14:49:00 GMT

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!

Search results matching tags 'Performance', 'monitoring', and 'Scripts'

Who is Active? v9.57: Fast, Comprehensive DMV Collection - What's Really Happening on Your Server?

UPDATE, April 28 2011: Who is Active v9.57 is outdated. Please use v11.00 instead.

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