THE SQL Server Blog Spot on the Web

Welcome to - 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? v10.00: DMV Monitoring Made Easy

Visit for updates on this stored proc.

Published Thursday, October 21, 2010 3:33 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



Kevin Boles said:

Thanks again Adam!!

October 21, 2010 6:47 PM

Patrick Flynn said:

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

October 21, 2010 7:56 PM

Aaron Bertrand said:

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.

October 21, 2010 8:40 PM

Adam Machanic said:

Kevin and Patrick, glad to help!

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

October 21, 2010 8:43 PM

AaronBertrand said:

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

October 21, 2010 8:52 PM

Adam Machanic said:

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 :-)

October 21, 2010 9:03 PM

Dugi said:

Thanks for sharing and update!

October 22, 2010 4:00 AM

Michael Barrett said:

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. :-)

October 22, 2010 8:22 AM

Kevin Devine said:

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

October 22, 2010 1:13 PM

JagT said:

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

Simple would be a big help

October 22, 2010 1:18 PM

Jimmy May said:

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.

October 22, 2010 9:49 PM

Michael Codanti said:

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.

October 23, 2010 1:59 AM

Jesse said:

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

October 23, 2010 11:45 AM

Michael Codanti said:

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

October 23, 2010 12:47 PM

Kalen Delaney said:

Hi Adam!

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


October 23, 2010 4:40 PM

Adam Machanic said:

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!

October 24, 2010 11:04 PM

Dan said:


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!

November 6, 2010 3:55 PM

Adam Machanic said:


Sounds like a personal problem to me.



November 6, 2010 8:38 PM

niikola said:

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 :(

November 25, 2010 4:22 AM

Adam Machanic said:


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.



November 25, 2010 10:43 PM

Ron Sorrell said:

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

November 30, 2010 8:57 AM

Adam Machanic said:

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.

November 30, 2010 9:53 AM

Ron Sorrell said:

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

November 30, 2010 10:51 AM

Ron Sorrell said:

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?


November 30, 2010 10:55 AM

Adam Machanic said:

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.

November 30, 2010 12:11 PM

Gary Rumble said:

I get the following on 2005 SP2.

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

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?

November 30, 2010 6:50 PM

Adam Machanic said:

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:

December 1, 2010 11:27 AM

Gary Rumble said:

select @@version reports:

Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86)   Mar  3 2007 18:40:02   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks, I'll try 9.90

December 1, 2010 5:47 PM

Adam Machanic said:

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:

December 2, 2010 10:13 AM

Jeet said:

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?

December 2, 2010 4:10 PM

Jeet said:

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

@return_schema BIT = 1


December 2, 2010 4:29 PM

Adam Machanic said:

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:


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

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

December 3, 2010 12:27 AM

Ken Proctor said:

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?


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

January 6, 2011 1:18 PM

Adam Machanic said:

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.


January 7, 2011 10:30 AM

Ken Proctor said:


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


January 11, 2011 4:37 AM

Randy said:

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)

January 13, 2011 1:45 PM

Virgil Rucsandescu said:

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?


January 21, 2011 9:34 AM

Adam Machanic said:

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.

January 22, 2011 12:50 PM

Ranga Narasimhan said:

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.

January 27, 2011 3:47 PM

Fernando Zavala said:

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

February 7, 2011 11:10 AM

Neil Hambly said:

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)

February 21, 2011 12:28 PM

Adam Machanic said:

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

February 21, 2011 5:21 PM

surya said:

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

March 10, 2011 6:14 AM

Adam Machanic said:

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.


March 10, 2011 10:12 AM

Adam Machanic said:

March 14, 2011 5:54 PM

surya said:

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 ?

March 16, 2011 5:38 AM

Elizabeth Block said:

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

March 18, 2011 7:05 PM

Adam Machanic said:

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 :-)

March 19, 2011 12:15 PM

Merrill Aldrich said:

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

April 7, 2011 7:18 PM

subash mool said:


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.

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.

June 10, 2011 1:32 PM

Adam Machanic said:

Hi Subash,

Please upgrade to v11.


June 11, 2011 7:20 PM

DavidB said:

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

August 5, 2011 10:30 AM

MikeL said:

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?

August 15, 2011 12:30 PM

Adam Machanic said:

August 15, 2011 1:48 PM

kevswag said:

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?

December 8, 2011 8:13 AM

Passos said:

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!

April 11, 2012 3:45 PM

Adam Machanic said:


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:

April 11, 2012 6:49 PM

Ravi Raj Chelluri said:

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

July 16, 2012 12:43 AM

Daksh said:

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

August 31, 2012 7:46 AM

Adam Machanic said:

@Ravi: Milliseconds

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

August 31, 2012 11:55 AM

realmerl said:

Hi Adam

just wanted to say, have been using you're sp from time to time for a couple of years now, very handy, and a fantastic helper.

does lots that I wont ever find out!

The create table output is a new one on me!

Good Job Sir!



October 24, 2013 6:27 AM

Adam Machanic said:

@realmerl: Really glad it's helping you. Make sure you're on the newest version (11.11) for the best experience.


October 24, 2013 9:40 AM

Alex said:

Is version (11.11) compitable with sql server 2014?

getting below error

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

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

November 20, 2014 11:01 AM

Adam Machanic said:


Yes, it absolutely is compatible; lots of people are using it.

A level 20 error indicates a much deeper problem -- you are either hitting a bug in SQL Server or something is messed up with your SQL Server instance. You might try restarting it when you get a chance; if you can't, maybe try to isolate which DMV is throwing the exception. I'd be more than mildly concerned, personally.


November 22, 2014 12:02 PM

Patricio said:

I created a Job to execute sp_WhoIsActive each 10 seconds and save results in a table. Could this frequent activity congest my Sql Server?

Sql Server 2005

HP ProLiant DL385p



May 5, 2015 10:37 AM

Adam Machanic said:


I doubt it.


May 5, 2015 3:47 PM

SimoneDBA said:


Is it script works well in SQLServer version  2012, 2014 and 2016 ?



September 6, 2016 1:36 PM

Adam Machanic said:

September 19, 2016 10:14 AM

Leave a Comment


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


Privacy Statement