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

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

Jimmy May, Aspiring Geek — Thu, 03 Dec 2009 21:15:36 GMT

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.

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

Warner Chaves — Thu, 03 Dec 2009 22:34:18 GMT

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

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

merrillaldrich — Thu, 03 Dec 2009 23:31:46 GMT

You are the man. Again

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

csm — Fri, 04 Dec 2009 09:19:43 GMT

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

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

Thomas Pullen — Fri, 04 Dec 2009 13:20:08 GMT

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.

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

BradK — Fri, 04 Dec 2009 18:23:02 GMT

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.

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

TommyB — Fri, 04 Dec 2009 22:19:54 GMT

Thanks Adam!

Who is Active? v8.40 - Now With Delta Power!

Adam Machanic — Fri, 11 Dec 2009 20:23:17 GMT

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

Bad habits to kick : relying on undocumented behavior

Aaron Bertrand — Tue, 09 Feb 2010 03:02:08 GMT

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

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

Steve — Mon, 08 Mar 2010 14:21:52 GMT

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!

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

Adam Machanic — Mon, 08 Mar 2010 14:31:45 GMT

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.

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

Ewan — Mon, 10 May 2010 11:33:04 GMT

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

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

Ewan — Mon, 10 May 2010 11:40:11 GMT

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

Ewan

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

Adam Machanic — Mon, 10 May 2010 14:58:03 GMT

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.

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

Ewan — Fri, 14 May 2010 11:04:09 GMT

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

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

Michael — Fri, 27 Aug 2010 16:10:43 GMT

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.

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

Michael — Fri, 27 Aug 2010 16:19:54 GMT

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? ;)

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

Amit Singh — Wed, 22 Sep 2010 12:23:16 GMT

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

Who is Active? v10.00: DMV Monitoring Made Easy

Adam Machanic — Thu, 21 Oct 2010 21:02:24 GMT

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

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

Dugi — Fri, 22 Oct 2010 06:22:06 GMT

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

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

DBA Expert — Sun, 19 Dec 2010 09:21:51 GMT

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

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

Jay — Tue, 22 Mar 2011 02:33:26 GMT

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

Jay

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

Roman — Wed, 13 Apr 2011 19:02:56 GMT

Really really useful script !!

Great !!

Thank you Adam !!!