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