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

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

Kevin Boles — Thu, 21 Oct 2010 22:47:47 GMT

Thanks again Adam!!

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

Patrick Flynn — Thu, 21 Oct 2010 23:56:57 GMT

Thanks Adam. Your work is highly appreciated.

I have deployed this across 100+ Servers and been getting all DBA's in shop to use this to replace sp_who2

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

Aaron Bertrand — Fri, 22 Oct 2010 00:40:24 GMT

Hey Adam, just FYI I get this on a brand new Denali install:

Msg 8622, Level 16, State 1, Procedure sp_WhoIsActive, Line 1195

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

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

Adam Machanic — Fri, 22 Oct 2010 00:43:18 GMT

Kevin and Patrick, glad to help!

Thanks for the interesting comment, Aaron. Not worried about Denali quite yet. :-)

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

AaronBertrand — Fri, 22 Oct 2010 00:52:11 GMT

FWIW there was an easy workaround: comment out the OPTION (HASH GROUP) around line ~1559 or so.

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

Adam Machanic — Fri, 22 Oct 2010 01:03:00 GMT

Hi Aaron,

Very interesting! The locks DMV seems to have changed in some way... I'm definitely looking forward to checking that out. Hopefully it's a positive change :-)

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

Dugi — Fri, 22 Oct 2010 08:00:55 GMT

Thanks for sharing and update!

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

Michael Barrett — Fri, 22 Oct 2010 12:22:47 GMT

Hi Adam

Just wanted to show my support for this. It's a really great procedure and I use it in all the enviroments I work with - if I can get away with deploying it. :-)

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

Kevin Devine — Fri, 22 Oct 2010 17:13:58 GMT

Is there a way to get it to show the job name instead of the job_id?

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

JagT — Fri, 22 Oct 2010 17:18:52 GMT

Thank for this. I've been using it much success!

Simple would be a big help

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

Jimmy May — Sat, 23 Oct 2010 01:49:34 GMT

Adam, I'd love to have you host a webinar of tips-&-tricks using your beloved beastie. It's a powerful tool, & I bet you could help us leverage it more fully with a primer.

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

Michael Codanti — Sat, 23 Oct 2010 05:59:15 GMT

Thanks for the update, Who is Active? has been very helpful.

I have sent you a version I modified to fix what appeared to be a bug around the new additional information feature, and to add a couple things that I have found essential. I hope you will consider incorporating them into the official code base.

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

Jesse — Sat, 23 Oct 2010 15:45:50 GMT

this proc is great. I have it on all my servers. MS should bake it in!

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

Michael Codanti — Sat, 23 Oct 2010 16:47:45 GMT

I agree that Microsoft should improve their offering, but I think they would have to license Who Is Active? to include it. (Not that it would be bad for Adam to get some money for his work.)

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

Kalen Delaney — Sat, 23 Oct 2010 20:40:53 GMT

Hi Adam!

Congratulations! Your post was selected as Blog Post of the Day on the SQL Server Internals FaceBook page!

http://www.facebook.com/home.php?ref=home#!/SQLInternals

~Kalen

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

Adam Machanic — Mon, 25 Oct 2010 03:04:26 GMT

Dugi, Michael B, JagT: My pleasure

Kevin: You're the second person to ask me for that. Stay tuned.

Jimmy: Great idea!

Michael C: I will consider it.

Jesse / Michael C: Agreed that Microsoft should bake it in, and yes, they would have to license it. I've tried to get the attention of various people on that end to set the wheels in motion and to give feedback on performance monitoring in general, but so far it seems that the SQL Server team is uninterested in improving the DMVs or the built-in monitoring procs. Too bad for all of us :(

Kalen: Thanks!

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

Dan — Sat, 06 Nov 2010 19:55:32 GMT

Adam,

Here are my 2 cents:

You did a great work here but if you do not split the proc in multiple procs as layout , logic, output formatting etc.. a lot of dbas will just take parts of it... I cannot run a procedure, that I do not understand 100%, against my servers.

Good luck!

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

Adam Machanic — Sun, 07 Nov 2010 00:38:43 GMT

Dan,

Sounds like a personal problem to me.

Best,

Adam

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

niikola — Thu, 25 Nov 2010 09:22:02 GMT

Would be nice if you add switch to include/exclude processes from system and distribution databases. I did it here, but I have to modify code for every new release :(

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

Adam Machanic — Fri, 26 Nov 2010 03:43:53 GMT

niikola,

That's the first time anyone has requested that. You should be able to use the not_filter to get rid of at least one database, or a pattern of databases, at a time. Or if all of the requests you don't want to see are from the same login, you can use the not_filter to get rid of that. Not a perfect approach, but it's the best I can offer today. If I can figure out how to make the filters more flexible without introducing major performance overhead the story here will improve.

Best,

Adam

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

Ron Sorrell — Tue, 30 Nov 2010 13:57:46 GMT

I get an error running the script

Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 494

Incorrect syntax near '.'.

on my system, that line is the following so some word wrap maybe involved

The target system is SQL 2005 SP2 32bit but attempting to run the query via SQL 2008 64bit delveoper Studio Manager

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

Adam Machanic — Tue, 30 Nov 2010 14:53:06 GMT

Hi Ron,

The issue is that the database you've installed the proc in is using SQL Server 2000 compatibility mode. This is a common issue on systems that have been upgraded. I personally see no issue in flipping the database over to SQL Server 2005 compatibility mode -- I find the 2000 mode to be useless -- but it's something you'll have to evaluate. If you don't feel comfortable doing that, simply create a new database on the server and install the proc there.

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

Ron Sorrell — Tue, 30 Nov 2010 15:51:12 GMT

good catch, our master was still at 2000 mode. We do hhave some situations with our particular app we support that requires 2000 mode for a database (at least until we upgrade some views and procedures), but never the master file

thanks again

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

Ron Sorrell — Tue, 30 Nov 2010 15:55:41 GMT

after creating the stored procedure, do you just run dbo.sp_WhoIsActive

if so, what would be the expected outcome as I got no rows returned?

do you only get data if there are potential issues?

thanks

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

Adam Machanic — Tue, 30 Nov 2010 17:11:41 GMT

Hi Ron,

The default mode returns only rows for "interesting" sessions, where "interesting" is defined as:

- Has an active request

- Or has an open transaction

So if you're getting no rows back, it means that all is quiet on your server. If you'd like to see some rows, try:

EXEC sp_whoisactive @show_sleeping_spids = 2

... and this will return one row for every sleeping (non-system) session.

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

Gary Rumble — Tue, 30 Nov 2010 23:50:51 GMT

I get the following on 2005 SP2.

Warning: The join order has been enforced because a local join hint is used.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'original_login_name'.

Did I miss something?

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

Adam Machanic — Wed, 01 Dec 2010 16:27:29 GMT

Hi Gary,

Are you sure you're actually running SP2 and not an earlier build? Unfortunately, after releasing v10.00 I realized that original_login_name was put into SQL Server in 2005 SP2, so the proc is not actually compatible with 2005 RTM or SP1. However, the most recent beta prior to v10.00 -- v9.90 -- should work fine and has pretty much the same set of features. You can find it here:

http://sqlblog.com/files/folders/beta/entry27502.aspx

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

Gary Rumble — Wed, 01 Dec 2010 22:47:56 GMT

select @@version reports:

Thanks, I'll try 9.90

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

Adam Machanic — Thu, 02 Dec 2010 15:13:45 GMT

Hi Gary,

9.00.1406 is a patched version of RTM. The "Service Pack 2" at the end refers to the NT build. You might want to upgrade; you're way behind and there are a lot of security and performance fixes you're missing.

Here's an article on how to find the version you're running:

http://support.microsoft.com/kb/321185

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

Jeet — Thu, 02 Dec 2010 21:10:06 GMT

Is someone using this to collect information in a table, for sort of historical monitoring to go back and check what happened last night kind of scenario? If yes, how frequently you are collecting the info. Any performance impact?

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

Jeet — Thu, 02 Dec 2010 21:29:13 GMT

How to get the table schema? I set this but with no luck.

@return_schema BIT = 1

Thanks

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

Adam Machanic — Fri, 03 Dec 2010 05:27:18 GMT

Jeet: I occasionally set it up for tight collection, every 15 seconds, and let it run for 5 or 10 minutes during a peak period. In default mode the performance impact is minimal.

To get the schema:

DECLARE @schema VARCHAR(MAX)

EXEC sp_whoisactive @return_schema = 1, @schema = @schema OUTPUT

SELECT REPLACE(@schema, '<table_name>', 'yourTable')

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

Ken Proctor — Thu, 06 Jan 2011 18:18:11 GMT

I've been using Who Is Active for a couple of months now, and its very useful, thanks.

When investigating an issue today I noticed that a status of KILLED/ROLLBACK wasn't reported. Can this info be retrieved?

Details:

"sp_who 254" returns column 'cmd' with a status of 'KILLED/ROLLBACK'

"EXEC sp_WhoIsActive @filter_type='session',@filter=254" returns lots of interesting information but 'KILLED/ROLLBACK' isn't present.

Looking through sp_WhoIsActive @help=1 I can't see an option for this although the source code suggests 'cmd' is queried from sysprocesses.

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

Adam Machanic — Fri, 07 Jan 2011 15:30:15 GMT

Hi Ken,

The "cmd" column is used internally, but not displayed. The [status] column should read killed/rollback if that's what your request is doing. Can you verify that it's not working properly? If you could provide a repro it would be great, so that I can fix it. Feel free to post it here or e-mail me using the address in the header of the proc.

--Adam

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

Ken Proctor — Tue, 11 Jan 2011 09:37:57 GMT

Adam,

I've emailed you a repro. Thanks for your help.

Ken

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

Randy — Thu, 13 Jan 2011 18:45:20 GMT

I'm running SQL Enterprise 64-bit SP3 and trying to use WhoIsActiveUI. I get various errors about original_login_name or get_additional_info depending on if the stored procedure is 9.9 or 10.0

Sql Version:

9.00.4262.00 SP3 Enterprise Edition (64-bit)

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

Virgil Rucsandescu — Fri, 21 Jan 2011 14:34:11 GMT

Thank you for this wonderful tool, Adam!

I had an interesting situation today with sp_whoisactive on the production machine:

When I ran it in production, I got a line with a process ran by somebody from a development server(from the management console) having defined the production server as a linked server - all the information in the line of that process was perfect with one exception : sql_text was just an empty string.

(I am sure it must have been stored procedure - if that matters).

Is this a normal behaviour or maybe there is a parameter to be used for situations like this one?

Virgil

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

Adam Machanic — Sat, 22 Jan 2011 17:50:57 GMT

Randy: Try running 9.9 without the @get_additional_info parameter and you should be all set. Apologies for the original_login_name issue; I'll fix that in the next version (by removing it from the proc -- it's not compatible with all versions of SQL Server, as I previously believed).

Virgil: Unfortunately, there are a number of cases where the DMVs don't give the SQL text. You can sometimes get a clue from the @get_outer_command option.

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

Ranga Narasimhan — Thu, 27 Jan 2011 20:47:24 GMT

Very useful utility. It will be helpful if the "Text" column is parsed and shows the StoredProc name or Trigger name etc, which will be helpful to know from where the sq_text comes from.

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

Fernando Zavala — Mon, 07 Feb 2011 16:10:05 GMT

Great Script. It's nice to see all of this information right away when you're having issues.

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

Neil Hambly — Mon, 21 Feb 2011 17:28:43 GMT

Not sure if this is me interpreting it incorrectly

but have the following - whihc i think is incorrect

spid 66

spid 64 wait_info @ 476946ms (running for 07:56:796) LCK_MS_S 66

spid 60 wait_info @ 476953ms (running for 14:02:316) LCK_MS_S 64

why it is waiting is not the issue - each query has a final Select query which is blocked {simply info from sys.partitions )

spid 66 is inserting - so it is expected to be block the others

Issue is wait_info is the same 64 & 60 (within a few ms) but both have been running for quite different durations...~ 6 mminute diff ..

infact one has wait duration for longer than it's own duration

{this is incorrect No ?)

Version V10.00(2010-10-21)

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

Adam Machanic — Mon, 21 Feb 2011 22:21:47 GMT

Hi Neil,

Those numbers make perfect sense to me. Why couldn't these two queries have fallen through to the SELECT and gotten blocked at nearly the same time? Also, the wait times are collected just after the run times so it's possible they can be a handful of milliseconds off, which in this case falls into the range (476953ms is 07:56:946 -- just a couple of hundred milliseconds longer than the reported run time).

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

surya — Thu, 10 Mar 2011 11:14:17 GMT

once i checked ur site i happy, but i execute this in my testing server using sa login its throwing erro

plz find below error

Warning: The join order has been enforced because a local join hint is used.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'original_login_name'.

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

Adam Machanic — Thu, 10 Mar 2011 15:12:23 GMT

Hi Surya,

This is due to a part of the code where I referenced a column that did not exist in SQL Server 2005 prior to SP2. Since I claim that the procedure should be compatible with all versions of SQL Server 2005 it's a bug on my end -- and will be fixed in the next version. But at the same time it's an issue on your end, since you're running a very outdated version of SQL Server.

The new beta version will be out early next week. In the meantime I highly suggest that you upgrade that sever to a more recent build.

--Adam

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

Adam Machanic — Mon, 14 Mar 2011 21:54:36 GMT

New version posted today:

http://sqlblog.com/files/folders/beta/entry34124.aspx

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

surya — Wed, 16 Mar 2011 09:38:29 GMT

Hello Adam........

i verified ur new script its working for me .....................

if u don't mine i know , whats the previous script and new scripts difference .. And one more thing is how u over write our existing script error ?

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

Elizabeth Block — Fri, 18 Mar 2011 23:05:18 GMT

Do you have documentation on how to use this besides the documentation inside the sp? Thanks!

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

Adam Machanic — Sat, 19 Mar 2011 16:15:55 GMT

Surya: The differences are listed on the download page. I eliminated the error by removing the original_login_name column.

Elizabeth: Not yet. But stay tuned. Documentation is coming very soon, I promise :-)

Top Tools I Didn’t Know I Needed as a DBA

Merrill Aldrich — Thu, 07 Apr 2011 23:18:08 GMT

Today’s post is an unabashedly subjective plug for a bunch of unrelated tools I have come to rely on

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

subash mool — Fri, 10 Jun 2011 17:32:06 GMT

Adam,

Thank you for sharing sp_whoisactive. While executing i get the following error:

Warning: The join order has been enforced because a local join hint is used.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'original_login_name'.

Any help will be appreciated.

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

Adam Machanic — Sat, 11 Jun 2011 23:20:16 GMT

Hi Subash,

Please upgrade to v11.

http://sqlblog.com/files/folders/release/entry35240.aspx

--Adam

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

DavidB — Fri, 05 Aug 2011 14:30:55 GMT

What options need to be set to list all connections that sp_who2 shows?

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

MikeL — Mon, 15 Aug 2011 16:30:48 GMT

Hey Adam, do you have a document describing all the parameters in this stored proc or do I need to just read through all the comments in the stored proc itself?

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

Adam Machanic — Mon, 15 Aug 2011 17:48:28 GMT

DavidB: @show_sleeping_spids = 2, @show_system_spids = 1

MikeL: Start here: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/30/twenty-nine-days-of-activity-monitoring-a-month-of-activity-monitoring-part-30-of-30.aspx

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

kevswag — Thu, 08 Dec 2011 13:13:39 GMT

looks great and thank you. I have sql 2005 sp3 installed, but my production database is 2000 compatibility mode. The master is in 2005 mode. Will I be able to use this on production database?

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

Passos — Wed, 11 Apr 2012 19:45:59 GMT

Adam! Are u there yet?!

Congratulations for this proc!

I'd like to use CPU filter to organize it ( desc ).

How can I do this?!

Thanks and Regards!

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

Adam Machanic — Wed, 11 Apr 2012 22:49:13 GMT

Passos:

Am I where?

To sort by CPU, you can do:

EXEC sp_whoisactive @sort_order = '[cpu] desc'

... and for best results make sure you're on the most recent version:

http://sqlblog.com/files/folders/beta/entry42453.aspx

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

Ravi Raj Chelluri — Mon, 16 Jul 2012 04:43:31 GMT

I would like you know CPU information is in Milliseconds or Micrseconds?

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

Daksh — Fri, 31 Aug 2012 11:46:19 GMT

I want to check whether sql connection in my application is on or not, how can i use whoisactive for this.

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

Adam Machanic — Fri, 31 Aug 2012 15:55:51 GMT

@Ravi: Milliseconds

@Daksh: Apologies, but I don't understand your question. What do you mean by "is on?"