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? 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.


 


 

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

Published Thursday, December 03, 2009 3:59 PM 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

Comments

 

Jimmy May, Aspiring Geek said:

Adam, I'm quite excited about unloading your latest-&-greatest against the delinquent perpetrators who persist in trying to confound me.

Best wishes to you for a very Merry Christmas & a happy, healthy, & prosperous new year.

December 3, 2009 4:15 PM
 

Warner Chaves said:

Will be testing the new version out, thanks for the updates Adam!

December 3, 2009 5:34 PM
 

merrillaldrich said:

You are the man. Again

December 3, 2009 6:31 PM
 

csm said:

Thanks again for your script, it's great!!

December 4, 2009 4:19 AM
 

Thomas Pullen said:

Very handy script, I've been meaning to check this out since you first posted it, and I'm glad I've finally got round to it.

December 4, 2009 8:20 AM
 

BradK said:

Great job with this Adam. Would love it if you would spend a couple of blog posts explaining some of the internals. I really like how you implemented the help text.

December 4, 2009 1:23 PM
 

TommyB said:

Thanks Adam!

December 4, 2009 5:19 PM
 

Adam Machanic said:

NOTE: v8.40 is outdated at this time. Please try v9.57, which you can find here . It has been only a

December 11, 2009 3:23 PM
 

Aaron Bertrand said:

In my last post in this series , I talked about the common habit of creating an IDENTITY column on every

February 8, 2010 10:02 PM
 

Steve said:

Adam,

 When running sp_WhoIsActive (w/no parameters) on my server, i find it reports close to 90 sleeping spids (all from one DB) each with 1 trans open.  I immediately run dbcc opentran and find none.

What am I missing here?

Thanks!

March 8, 2010 9:21 AM
 

Adam Machanic said:

Hi Steve,

DBCC OPENTRAN reports only read-write transactions--i.e. those that have modified some data (or will, in the case of a transaction started for an INSERT or similar).

Who is Active, thanks to the DMVs used, reports any open transaction, including read-only transactions. Your 90 SPIDs must have all started explicit transactions before queries were run, but didn't write anything. Personally, I think that reporting on both read-only transactions and read-write transactions is preferable to only showing the latter, as read-only transactions can hold locks, etc. But if you'd rather not see these SPIDs, simply run Who is Active with @show_sleeping_spids = 0 and they won't be included unless they're blocking some other SPID.

March 8, 2010 9:31 AM
 

Ewan said:

Hi Adam

Brilliant Proc - thanks for sharing. I've been trying to change the defaults so that blocking chains are always bubbled to the top of the resultset, by setting @find_block_leaders to 1 and then sorting by @sort_order VARCHAR(500) = '[blocked_session_count][dd hh:mm:ss.mss]'. If no blockers are present, this should default back to the task age sort order. But it only sorts on the blocked_session_count - the age column is ignored. Is this expected?

Thanks

Ewan

May 10, 2010 7:33 AM
 

Ewan said:

Of course I could have just used start_time... Sorry for being sloooow.

Ewan

May 10, 2010 7:40 AM
 

Adam Machanic said:

Hi Ewan,

I assume it's working for you now? Apologies for this case not being the most straightforward thing in the world. [dd hh:mm:ss.mss] is not a valid sort column, for a few reasons: A) I figured no one would want to type it in, and B) It isn't generated when you ask for "unformatted" output from the proc, and C) It sorts opposite the way you expect dates to. So in this case you've found the answer: [start_time] is the correct one to use.

Enjoy, and let me know if I can help you with anything else with regard to the proc.

May 10, 2010 10:58 AM
 

Ewan said:

Hi Adam. Sorry for the delay in responding. Yes, it's all working perfectly now. I've been playing around with the output for a while and I think I finally have it, goldilocks-like, just right.

Ewan

May 14, 2010 7:04 AM
 

Michael said:

Is there any way to display the user name instead of the long S number for users that have been created with the "WITHOUT LOGIN" option?

For example the login name shows "S-1-9-3-2913542764-1330446797-317931661-1541866692" while the user sid is "0x0105000000000009030000006C22A9ADCD014D4F8D40F312C404E75B". I looked but I couldn't find any way to get from one to the other to look the name up in sys.database_principals or sys.sysusers.

August 27, 2010 12:10 PM
 

Michael said:

OK, it looks like I found something, it just doesn't help me much.. It looks like you have to call a WindowsAPI (ConvertStringSidToSid) to convert the S-1-9-3 into the SID. Maybe that would make a good CLR function? ;)

August 27, 2010 12:19 PM
 

Amit Singh said:

I m not able to get the output when i m running query;

exec sp_whoisactive @GET_TRANSACTION_INFO=1 ,

@output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][query_plan][locks][%]'

Error generarted:-->

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.dm_exec_query_memory_grants'.

please help

September 22, 2010 8:23 AM
 

Adam Machanic said:

Today I am happy to release the newest official build of my Who is Active procedure: v10.00 . For those

October 21, 2010 5:02 PM
 

Dugi said:

Well done! When it will be available for download!?

October 22, 2010 2:22 AM
 

DBA Expert said:

Your script is very great. I think, i can use it. thanks.

December 19, 2010 4:21 AM
 

Jay said:

WOW!!! That's a very useful script.Thanks Adam

Jay

March 21, 2011 10:33 PM
 

Roman said:

Really really useful script !!

Great !!

Thank you Adam  !!!

April 13, 2011 3:02 PM
 

Philip Orleans said:

The script cannot be found. Can somebody post it again?

October 29, 2013 1:04 AM

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