THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

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

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

For those of you who haven't been following along, here are some quick facts on Who is Active:

  • Who is Active is a DMV-based monitoring stored procedure that uses 15 different views to show a large amount of data about what's running on your server
  • Who is Active was designed to be extremely flexible, and includes options to not only get different types of data, but also to change the output column list and sort order
  • Who is Active was designed with performance in mind at every step; users report that under normal conditions response times are generally subsecond, with slightly longer response times on servers that are extremely taxed
  • Who is Active is compatible with all versions of SQL Server after SQL Server 2005 RTM. It does require that the host database (generally master) is not set for SQL Server 2000 compatibility mode
  • Who is Active is free for most users. Refer to the license at the top of the procedure for more information
  • Who is Active has been a work in progress for over 3 years. My source control system contains over 600 revisions, and version 10.00 is over 3900 lines long -- by far the biggest and most complex single stored procedure I have ever worked with.

 

Click Here to Download Who is Active version 10.00


A bit more information, for those who are interested:

Most of the changes since the last stable build (v9.57) have been bug fixes, and I would like to thank the many people who've sent me feedback over the past 10 months. As a result of fixing the various issues I am happy to say that this new version is quite solid, returning data even when the underlying DMVs aren't doing their jobs as advertised.

The most major enhancement is much better support for international character sets. When I first wrote the procedure I used VARCHAR for everything because, let's face it, I'm American and we know in our hearts that no one else in the world has any other language aside from that which we use. Well, luckily I was slapped into reality by a few users in far away lands such as Isreal and Sweden, and this version fully supports international character sets for everything except one feature where I couldn't get right-to-left encodings working properly. Stay tuned for a minor update to fix that discrepancy.

I also added a few features along the way:

  • The name of the actual latch is shown when displaying a latch wait, not just the name of the wait (LATCH_EX, etc)
  • The transaction_writes column shows both the number of writes and the actual amount of log space consumed, in kB
  • An option called @get_additional_info was added. This option lets you collect information about the running session or request including connection settings like ANSI_NULLS, DATEFIRST, etc.

I still need to fully document this thing, and it's a goal I hope to achieve before the end of the year. Bear with me, please. And in the meantime, feel free to ask me any questions either here or on Twitter.

Enjoy! And thanks again to everyone who has sent me feedback along the way. This would not be possible without you.

 

...

A few terms for the search engines:

sp_whoisactive whoisactive sp_who sp_who2 sp_who3 sp_who4 sp_who5

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

Comments

 

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!

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

~Kalen

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:

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!

November 6, 2010 3:55 PM
 

Adam Machanic said:

Dan,

Sounds like a personal problem to me.

Best,

Adam

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:

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

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?

thanks

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:

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

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:

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

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

Thanks

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:

DECLARE @schema VARCHAR(MAX)

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?

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.

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.

--Adam

January 7, 2011 10:30 AM
 

Ken Proctor said:

Adam,

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

Ken

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?

Virgil

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.

--Adam

March 10, 2011 10:12 AM
 

Adam Machanic said:

New version posted today:

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

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:

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.

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.

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

--Adam

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:

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

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:

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

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

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
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement