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.

Released: Who is Active v11.11

It's been several months since the last Who is Active fix, so I thought I'd call this one out specifically via a blog post.

v11.11 contains a few minor fixes and enhancements, which you can read about on the download page.

This will (I believe) be the last release that is compatible with SQL Server 2005 and 2008. v11.xx has been quite a stable release in general, with very few bugs found in the 11 months since I've released it--I do not expect to need to release any more fixes.

In the meantime, I have started work on a new version that will take advantage of several SQL Server 2012 features, as well as some SQL Server 2008 features that I was unable to previously leverage due to my efforts to keep the procedure backward-compatible with SQL Server 2005. The new version will be released in a few months, once I've had time to both implement and thoroughly test the new functionality.

As always, I appreciate any comments or feedback.

Enjoy!

Published Thursday, March 22, 2012 6:20 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

 

Tony said:

Hi Adam,

might it be worth adding SET ANSI_NULLS, QUOTED_IDENTIFIER ON to the top of your sp_whoisactive script (given that these settings are saved with the object definition at creation time)? If the proc is created with them off, it errors when it uses the xml datatype methods (e.g. the @get_transaction_info option).

March 22, 2012 6:00 PM
 

Don Halloran said:

Hi Adam, great query, this answers at least half of my day to day questions on perfomrance, blocking etc all by itself.

One question: What are the units on the various columns? For instance, used memory, reads, writes: pages, kb ...?

March 22, 2012 9:18 PM
 

Adam Machanic said:

Tony: Maybe in the next version :-)

Don: Units are 8KB data pages unless otherwise specified.

March 22, 2012 9:35 PM
 

John Sansom said:

Brilliant! I cannot thank you enough. This utility procedure has proved incredibly valuable to myself and the many others I have introduced it to. It's become a vital tool for triaging sql servers. Thanks for all your efforts and sharing it with us.

March 23, 2012 4:00 AM
 

spe109 said:

Thanks for your hard work Adam. Great utility that is so very useful.

March 23, 2012 4:40 AM
 

Craig M. said:

Thank you.

I will update my machines with v11.11.

> I appreciate any comments or feedback.

"After April [2011] all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation."

I periodically check back for this document, but it does not seem to be available yet.

Could you please comment on the availability?

Can I expect this document sometime this year?

If not, could you indicate the target year?

Thank you.

March 24, 2012 10:03 AM
 

dmmaxwell said:

This proc is part of my default administrative install for every sql server in the environment, along with a job that uses it to keep a log of activity on the server. Thanks again for your work.

March 30, 2012 9:29 AM
 

Max said:

Hi Adam,

WOW what a great tool.  I have no words to describe how awesome this dose the job.  I have helped some developer pin point what is going with your tool(but only in test environment).  But got few comments...

I am a professional oracle DBA and a seasonal SQL Server DBA.  Our company has most of there apps running on oracle and very few apps running SQL SERVER (2005 and 2008).  Due to that i only get call about sql server issue one in a blue moon....our SQL Server apps are pretty stable but they do mis-behave every once in a while... now to the actual comment part..

I do see that we have to create a SP in the master db, but is there a way to run this without creating a SP.  The reason is due to CHANGE MANAGEMENT POLICY..we have a very strict policy about creating new stuff in production and it will not be approved....There is a great tool just like yours called SNAPPER in oracle world written by Tanel Poder ( http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper )...dose the same thing in oracle but without creating any SP in the DB. Its a anonymous block.  

If your tool could do something similar, that would be awesome for people who have a strict change management policy.  As its hard to explain why i want to create this(besides performance reason) to change management.  Is this actually even doable ?

Again THANKS for your awesome work on this.    

April 18, 2012 4:30 PM
 

Scott said:

Max, you can easily run it without making a proc. You just have to make some small changes:

Remove the word "OUTPUT" from line 142

Remove the closing parentheses from line 147

Remove the last two lines (the END and GO)

Remove lines 1-25, and replace them with the word "DECLARE"

Now it is just one big sql script that you can run without creating any permanent objects.

April 18, 2012 6:02 PM
 

max said:

Thanks scott, I did extactly what you said...but it dose not seem to work...i get all the below errors...

Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Line 359

Must declare the scalar variable "@filter".

Msg 137, Level 15, State 2, Line 387

Must declare the scalar variable "@filter_type".

Msg 137, Level 15, State 2, Line 393

Must declare the scalar variable "@filter_type".

Msg 137, Level 15, State 2, Line 399

Must declare the scalar variable "@not_filter_type".

Msg 137, Level 15, State 2, Line 405

Must declare the scalar variable "@not_filter_type".

Msg 137, Level 15, State 2, Line 411

Must declare the scalar variable "@show_sleeping_spids".

Msg 137, Level 15, State 2, Line 417

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 423

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 429

Must declare the scalar variable "@format_output".

Msg 137, Level 15, State 2, Line 435

Must declare the scalar variable "@help".

Msg 137, Level 15, State 2, Line 799

Must declare the scalar variable "@output_column_list".

Msg 102, Level 15, State 1, Line 803

Incorrect syntax near ')'.

Msg 137, Level 15, State 2, Line 814

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 848

Must declare the scalar variable "@format_output".

Msg 137, Level 15, State 2, Line 852

Must declare the scalar variable "@format_output".

Msg 137, Level 15, State 2, Line 857

Must declare the scalar variable "@format_output".

Msg 137, Level 15, State 2, Line 862

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 878

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 884

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 889

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 893

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 897

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 901

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 905

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 909

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 913

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 918

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 922

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 928

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 933

Must declare the scalar variable "@get_locks".

Msg 137, Level 15, State 2, Line 937

Must declare the scalar variable "@get_transaction_info".

Msg 137, Level 15, State 2, Line 941

Must declare the scalar variable "@get_transaction_info".

Msg 137, Level 15, State 2, Line 947

Must declare the scalar variable "@get_outer_command".

Msg 137, Level 15, State 2, Line 953

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 957

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 962

Must declare the scalar variable "@find_block_leaders".

Msg 137, Level 15, State 2, Line 976

Must declare the scalar variable "@get_additional_info".

Msg 102, Level 15, State 1, Line 985

Incorrect syntax near ')'.

Msg 137, Level 15, State 1, Line 989

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 1008

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 1014

Must declare the scalar variable "@destination_table".

Msg 137, Level 15, State 2, Line 1018

Must declare the scalar variable "@destination_table".

Msg 137, Level 15, State 2, Line 1024

Must declare the scalar variable "@destination_table".

Msg 137, Level 15, State 2, Line 1044

Must declare the scalar variable "@sort_order".

Msg 102, Level 15, State 1, Line 1048

Incorrect syntax near ')'.

Msg 137, Level 15, State 2, Line 1059

Must declare the scalar variable "@sort_order".

Msg 137, Level 15, State 1, Line 1157

Must declare the scalar variable "@sort_order".

Msg 156, Level 15, State 1, Line 1178

Incorrect syntax near the keyword 'AS'.

Msg 137, Level 15, State 2, Line 1228

Must declare the scalar variable "@return_schema".

Msg 137, Level 15, State 2, Line 1250

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 1262

Must declare the scalar variable "@get_locks".

Msg 137, Level 15, State 2, Line 1331

Must declare the scalar variable "@filter".

Msg 156, Level 15, State 1, Line 1640

Incorrect syntax near the keyword 'AS'.

Msg 102, Level 15, State 1, Line 1682

Incorrect syntax near ';'.

Msg 137, Level 15, State 2, Line 1715

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 3399

Must declare the scalar variable "@filter".

Msg 137, Level 15, State 2, Line 3406

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 3594

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 3639

Must declare the scalar variable "@get_full_inner_text".

Msg 156, Level 15, State 1, Line 3700

Incorrect syntax near the keyword 'ORDER'.

Msg 137, Level 15, State 2, Line 3742

Must declare the scalar variable "@get_outer_command".

Msg 137, Level 15, State 2, Line 3879

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 3923

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 3961

Must declare the scalar variable "@get_plans".

Msg 137, Level 15, State 2, Line 4020

Must declare the scalar variable "@get_locks".

Msg 137, Level 15, State 2, Line 4293

Must declare the scalar variable "@find_block_leaders".

Msg 137, Level 15, State 2, Line 4336

Must declare the scalar variable "@get_task_info".

Msg 137, Level 15, State 2, Line 4545

Must declare the scalar variable "@output_column_list".

Msg 137, Level 15, State 2, Line 4673

Must declare the scalar variable "@delta_interval".

Msg 137, Level 15, State 2, Line 4679

Must declare the scalar variable "@delta_interval".

Msg 102, Level 15, State 1, Line 4684

Incorrect syntax near ';'.

Msg 137, Level 15, State 2, Line 4693

Must declare the scalar variable "@destination_table".

Msg 137, Level 15, State 2, Line 5142

Must declare the scalar variable "@schema".

April 19, 2012 8:27 AM
 

Adam Machanic said:

Max: why don't you just create it in tempdb? Or even better, make it into a temporary stored procedure (just put a # before the procedure name). You're allowed to create temp tables, right? This is effectively the same thing. Much easier than editing.

April 19, 2012 10:24 AM
 

Chris said:

If possible, can you add another filter type? I would like to able to filter by session duration (or time). For example, we have a lot of replication agents hitting our servers and they run for a long time. If we're having a performance problem it is usually something that is only a few minutes old. So it would be handy to get a sp_whoisactive result set that only shows the last n number of minutes of sessions. Thanks.

April 27, 2012 10:55 AM
 

Adam Machanic said:

Hi Chris,

Yes, it's on the list. Ideally I'd like to create a simple, XML-based custom filter "dialect" that will let you filter by any number of things -- I've had numerous one-off requests for new filter types and I don't want to add them all individually. Stay tuned.

--Adam

April 27, 2012 2:00 PM
 

Phil Carter said:

Hey Adam

Another stellar job.

One thing though, I note in the v11.11 notes

"Added host_process_id to additional_info collection"

Umm, how do I get that output?

We have an awesome vendor application that users run via Citrix. It holds locks when an error message appears on the users workstation. This causes a ever increasing blocking chain in the DB.

We use the Host_name and Host_process_id columns to track down users that have walked away from their workstation and not responded to the error message, thus they hold up database processing for everything.

This is what I'm running

EXEC sp_WhoIsActive

   @find_block_leaders = 1

   , @get_task_info = 1

   , @get_additional_info = 0

   , @output_column_list = '[session_id][block%][login_name][host%][host_process_id][program%][sql_text][wait_info]'

   , @sort_order = '[blocked_session_count] DESC'

Also, would be great to be able to list out only blocked/blocking sessions ;)

Cheers

Phil

June 6, 2012 3:35 AM
 

Adam Machanic said:

Hi Phil,

You want @get_additional_info = 1, not 0.

As for *only* seeing blocked and blocking sessions -- why? That doesn't seem especially interesting to me, to be honest. Ideally you'd deal with the blocking and then not need that option anymore :-)

June 6, 2012 10:15 AM
 

RIzwan Hassan said:

If I like to filter by DB name... what is the best way to do it. (I am new to SQL DBA role)

Thanks!

June 21, 2012 9:34 PM
 

Adam Machanic said:

Rizwan:

EXEC sp_whoisactive @filter_type = 'database', @filter = 'your DB name'

June 26, 2012 12:01 PM
 

Rizwan Hassan said:

Thank you and great work!

July 2, 2012 9:07 AM
 

Sergio Pacheco said:

Adam,

I think you mentioned documentation in an earlier post.  Where can I find it?  Also, i missed your "No more Guessing" Troubleshooting talk this last April.  When/Where do you think you may have it again?  Is the recording available for purchase?

July 25, 2012 10:20 AM
 

Adam Machanic said:

Sergio:

Sorry, the docs aren't finished yet. I've had a very busy year.

As for "No More Guessing," are you referring to the delivery at SQLbits? That unfortunately wasn't recorded, but keep your eyes open early next year as I'm sure I'll be delivering it at various places. (The rest of this year, not much will be going on.)

--Adam

August 7, 2012 9:54 AM
 

Rob Kraft said:

I just discovered sp_whoIsActive and am eager to try it after seeing all the feedback.  I am trying to run it right now, but I get no results.  I have not read the 50 blog posts, though I have read a scattered few in the attempt to make this stored proc show results.  Are there any usage examples?

I tried no parameters, and I get no records returned no matter which database I run it in.  There are plenty of active sessions by users per sp_who.

I also tried running it with this example but still got no results.

EXEC sp_whoisactive @filter_type = 'database', @filter = 'your DB name'

My dbms in SQL2005, my tooling is SQL 2012.

Should I expect output when ran with no parameters?  Could I be missing a step in setting it up to work?

Thanks for your time and devotion.

August 13, 2012 9:50 AM
 

Adam Machanic said:

Hi Rob,

Should you expect output when run with no parameters? Only if there's something interesting to show you.

This post explains it: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/05/less-data-is-more-data-a-month-of-monitoring-part-5-of-30.aspx

--Adam

August 13, 2012 10:33 AM
 

DanOrc said:

Rob, the procedure must be run on master database and you will see transactions that are currently running, not the finished ones.

August 13, 2012 10:35 AM
 

Adam Machanic said:

Hi DanOrc,

Actually, the proc does not need to be deployed, nor run, in master. It can be put in any database you like. I personally like to put it in master because then it's available everywhere else without using a three-part name. But the decision is yours.

--Adam

August 13, 2012 10:42 AM
 

Rob Kraft said:

Thanks Adam!  That makes sense and

EXEC sp_WhoIsActive @show_sleeping_spids = 2

is just what I needed to start getting oriented with the proc!

August 13, 2012 1:35 PM
 

Max said:

Hi Adam,

I have been using who is active tool for couple of months now and it has already saved us so many time.  But i had one question for you.  The other day someone requested me to capture the output of my query to see what exactly the SQL statements the session's are running and other metrics that who is active collects.  So i thought okay, that is simple.  Run your procedure and once i get the output in SSMS, right click on it and do "save results as".  I exported that in quite a few different format(csv, excel, text file) but seems like the output is not readable in any of those file format.  

I am in no means an expert, but if i were to guess that is happening due to the XML query SQL Text?

Do you have any suggestions as to how we can export this into a nice format(excel or csv)?  

August 20, 2012 10:55 AM
 

max said:

I know you already have a post on capturing output using create table and such...but i wanted to see if there is anything besides that ?

August 20, 2012 1:13 PM
 

Adam Machanic said:

Hi Max,

Did you try turning off formatting and exporting only the non-XML columns that are left? (That would mean no query plan, for example.)

Aside from that I have no idea. Never tried exporting to Excel.

--Adam

August 20, 2012 1:56 PM
 

max said:

no i did not turn off formatting, how would i do that?

although i tried just export columns that are non-XML, but wanted to see if there was a way to export the XML column as well( sql text column)..

August 20, 2012 2:30 PM
 

Adam Machanic said:

If you turn off formatting SQL Text will no longer be XML.

EXEC sp_whoisactive @format_output = 0

August 20, 2012 2:33 PM
 

max said:

Thanks Adam, I tried that but no luck.  Still the format seems to be out of order if i export it to CSV format.  

August 20, 2012 6:31 PM
 

TheSeventhDawn said:

Adam,

First off love the tool. Is there a way to kick off this proc execution when spids are suspended? Blocked process threshold and blocked report are excellent but I would like to get the flavor of details that comes with this proc

thanks

September 17, 2012 2:44 PM
 

Chris at DEA not that DEA said:

Mike, I just found out about this sp at a local SQL users group meeting. This is fantastic stuff. Thank you so much for sharing this with us.

December 7, 2012 2:49 PM
 

Gianluca Sartori said:

Thanks Adam,

oustanding job as usual! I've been using WhoIsActive for years and it's part of my standard setup for every instance I install.

It would be great if we could display and filter context_info as well. Sometimes I find it useful to add troubleshooting info in that binary string and I had to tweak your code to use it.

Thanks again!

December 12, 2012 5:46 AM
 

Adam Machanic said:

Hi Gianluca,

I'll add it to the additional_info collection in the next version. (If  you don't see it in the first beta, drop me a note and remind me.) Not sure I'll put a filter in for  it, though. Probably not a very common request.

Best,

Adam

December 12, 2012 9:41 AM
 

max said:

Hi Adam,

I have been using your tool for quite a while.  It works great.  But the other day i wanted to try this using sqlcmd.  And seems likes its not giving me output that it dose in SSMS....am i doing anything wrong.  i tried the regular exec sp_whoisactive also tried disable the out format and stuff...here is what i get...

C:\oracle\sql>sqlcmd -S servername/instance_name -E

1> EXEC sp_whoisactive @format_output=0

2> go

Warning: Null value is eliminated by an aggregate or other SET operation.

1>

2> EXEC sp_WhoIsActive

3> go

Warning: Null value is eliminated by an aggregate or other SET operation.

dd hh:mm:ss.mss

----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------

00 00:08:35.220

Select Id From DWE_Workflows Where Id=-1

--?>

00 00:08:35.220

Select Id From DWE_Workflows Where Id=-1

--?>

00 00:08:35.216

Select Id From DWE_Workflows Where Id=-1

--?>

00 00:08:35.216

Select Id From DWE_Workflows Where Id=-1

--?>

00 00:07:28.373

Select Id From DWE_Workflows Where Id=-1

--?>

Warning: Null value is eliminated by an aggregate or other SET operation.

1>

December 17, 2012 1:00 PM
 

Adam Machanic said:

Hi Max,

It seems to work fine on this end, although the output looks like absolute garbage; the proc's output is really optimized for SSMS. The option to turn off the formatting is there for data collection, and it's not going to help with SQLCMD output, since it doesn't decrease the large number of output columns, re-format XML, or anything else. What you'd need to do -- I think -- is collect into a table and then dump the output in a compact textual format. You'd also want to eliminate a number of the columns, such as the procedure text, which are going to add line breaks and ruin the output. But I'm not sure that's worth the effort. Any reason you can't fire up SSMS?

--Adam

December 18, 2012 9:34 AM
 

Max said:

We can fire up SSMS and make this run.  

The other day we had an issue were we would only login thru SQLCMD, SSMS was taking very long time(like 15 mins to load)...were as SQLCMD was about 1-2 mins to load...

once in SQLCMD, we wanted to find out what is going on, who is taking the CPU, memory etc etc (which sp_whoisactive provided)...but when i ran spwhoisactive in sqlcmd, the output like you said was Unreadable....so we had to reboot the machine to get everything under control...

so i have been trying to figure out how to make sp_whoisactive work with SQLCMD....incase we have the issue again....Hope it makes sense...

December 18, 2012 1:16 PM
 

Max said:

Do you happen to have a quick output as to how it looks like ?? when you can collect into a table and then dump the output in a compact textual format. You'd also want to eliminate a number of the columns, such as the procedure text, which are going to add line breaks and ruin the output

December 18, 2012 1:39 PM
 

Adam Machanic said:

Hi Max,

I'm not sure what the output would look like as I've never done it :-)

I think I would do something like this, one block per session:

session_id: 123  host_name: xyz     login: xyzzy

CPU: 123         Reads: 456         Writes: 789       Tempdb: 1234

Waits: (1x) CXPACKET (5x) PAGEIOLATCH_XX

[etc...]

---

December 18, 2012 2:44 PM
 

HIMANSHU said:

Hi Adam

I am getting this error

Warning: Null value is eliminated by an aggregate or other SET operation.

January 10, 2013 7:51 PM
 

Adam Machanic said:

Himanshu:

Ignore it. Maybe I'll make it go away in a future version, but it has no impact on anything.

--Adam

January 11, 2013 11:10 AM
 

HIMANSHU said:

Thanks Adam , I little modified it and running as script , its a awesome script .I have one question , under SQLtext column does it only show Tsql command (select,update etc) or it also shows exec procedure . Mostly we run storeprocs in any environmental ,if it shows store procs

it will be easy to find problematic sps .

It is human nature to always want more :-) .

Thanks again.

January 11, 2013 2:15 PM
 

Adam Machanic said:

January 11, 2013 2:39 PM
 

dbiDBA said:

I love to use your WhoIsActive functionality. Thanks for all your work. But I am having an issue. When the instance is busy and I need results the most, it frequently gives this error:

"The statement terminated. The maximum recursion 32767 has been exhausted before statement completion."

Originally, the maxrecursion was the default 100. I modified the code to increase the maxrecursion limit to the maximum and still frequent hit the limit. Is this something you have seen before? Is there a fix or workaround? Is there a runtime option I can avoid to prevent the error?

WhoIsActive 11.11 on SQL Server 2008 SP3

January 14, 2013 2:26 PM
 

Adam Machanic said:

Hi dbiDBA:

I am aware of the issue, and will fix it in the next version. The problem is that under load, there can be conditions where the blocking chain is nonsensical and seems to include cycles.

In the meantime, as a workaround, don't use @find_block_leaders mode when you're seeing that error. I realize that's not a fantastic workaround but it's all I can do for you today.

--Adam

January 14, 2013 3:18 PM
 

Shiju Samuel said:

Hi Adam, Is there a way to find out how long a victim of blocking is clogged? -Shiju

January 28, 2013 8:49 AM
 

Adam Machanic said:

Shiju: Yes; a blocked session will have a LCK_M_* wait in the wait_info column. The wait will include a wait time in milliseconds.

--Adam

January 28, 2013 9:36 AM
 

Dustin Mueller said:

I have to say thank you for all of your hard work that you put into this, because I have found myself using it so often!

February 6, 2013 12:26 PM
 

Chris said:

Adam, is there a help file for using your tool? Or is all of that available in comments inside the proc?

February 28, 2013 8:42 AM
 

Adam Machanic said:

@Chris: EXEC sp_whoisactive @help = 1

February 28, 2013 9:41 AM
 

yazalpizar said:

Hi Adam, thanks a lot for this great tool, I'm just scratching the surface and it's already really helpful troubleshooting our databases. One question though: I'm going through all your 30 blog posts on april'11 about monitoring and you mentioned the possibility of having all them on one single downlodable file. Does that file exists or you haven't had the time yet to compile all the information? I will continue reading online, but would be great to have it on pdf or any other format that could be used on ebook reader. Thanks again!

March 14, 2013 8:07 AM
 

NewKidInTown said:

I tried to run the script to create the sp and I get the following error:

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

Incorrect syntax near '.'.

Msg 156, Level 15, State 1, Procedure sp_WhoIsActive, Line 3941

Incorrect syntax near the keyword 'CASE'.

Msg 156, Level 15, State 1, Procedure sp_WhoIsActive, Line 3979

Incorrect syntax near the keyword 'CASE'.

Can someone please help?

April 18, 2013 10:10 AM
 

Adam Machanic said:

@NewKidInTown: Your database compatibility level is set incorrectly. Change it to at least 90 (SQL Server 2005). This is a common issue for instances that were upgraded from 2000; databases, including master, are not automatically migrated to higher compatibility levels.

April 18, 2013 11:56 PM
 

Mike Stuart said:

Adam, thanks for the awesome work on this.  

Suggestion / question: we have a LOT of instances in our environment; how about a @version parameter?  For example: 'exec sp_WhoIsActive @version = 1' would return something like 'Who Is Active? v11.11 (2012-03-22)', instead of needing to run sp_helptext, scripting out the proc, etc. on every instance in our environment?

April 23, 2013 11:10 AM
 

Adam Machanic said:

Hi Mike,

EXEC sp_whoisactive @help = 1

:-)

April 23, 2013 11:34 AM
 

Mike Stuart said:

... and there it is!  Excellent turnaround time, and I didn't even have to download it!  You're truly amazing... or I'm an idiot :P   Thanks.

April 23, 2013 11:49 AM
 

Jordon (HeavenCore) said:

Hi there,

Is there a script to drop sp_WhoIsActive and all its dependencies?

April 29, 2013 10:05 AM
 

Cary Davis said:

This thing is cool.  

Now I've got the power to control the world from right here at my desk.  

This is great stuff, Thanks Adam.  I wanna be you when I grow up.  DO NOT ever lose those sunglasses man.

April 29, 2013 11:32 AM
 

Adam Machanic said:

@Jordan: DROP PROC sp_WhoIsActive; (But why you'd want to is beyond me :-))

@Cary: Cheers!

April 29, 2013 12:11 PM
 

Steelie said:

Adam, thanks for the great tool, we use it everyday.  Quick question, we just deployed our first 2012 server, and granted ViewServerState to a group of developers, but they get an access denied error when running the stored proc.  They can run any other SPs inside master, just not whoisactive.  The users themselves are NOT sysadmins on the server, just regular logins with the ViewServerState granted.  This works fine on 2008, 2005, etc... is this some weirdness with 2012?  Thanks again  

May 23, 2013 1:33 PM
 

Ed said:

Hi Adam. I'm currently being blocked by something on instance start-up that is affecting the recovery time significantly. Do you know if there are any internal processes that can't be identified/tracked through your procedure?

May 28, 2013 4:19 AM
 

Adam Machanic said:

@Steelie: You need to actually grant EXEC on the proc:

GRANT EXEC ON dbo.sp_WhoIsActive TO [public];

(Well, I grant it to [public]. YMMV :-))

@Ed: The proc can't detect anything prior to instance start up. I'm not sure if that's what you mean, or if you mean that the problem occurs only at instance start up because that's when the databases need to recover? I haven't ever tried monitoring database recovery, but you'd certainly need to ask for system SPIDs. You'll probably also want to look at transaction information:

EXEC sp_WhoIsActive @show_system_spids = 1, @get_transaction_info = 1

... Whether or not this will help you track down the issue is a whole other conversation. There are a number of sources of data for log processes that Who is Active does not use.

--Adam

May 28, 2013 10:13 AM
 

Ed said:

Thanks Adam. On failover all the databases are recovering except our main production one, which is taking more than 5 minutes to become available. We've got as far as tracking the issue down to the tail end of the recovery process and that there is a significant amount of time spent on schema modification locks during this ~5 minute window. It's environment specific as moving the DB to our test server results in a sub 1 minute recovery time.

I'm making use of your procedures ability to log to a table so that we can capture output every couple of seconds for the duration of the recovery.

June 6, 2013 4:22 AM
 

Alexander said:

is it work in sql server 2008 r2 express?

June 10, 2013 3:37 PM
 

Adam Machanic said:

@Alexander: I think so.

June 10, 2013 11:38 PM
 

Alexander said:

I asked this because I was running in a sql server with sql express 2008 r2 and as soon as I execute the procedure sp_whoisactive it stop it and show nothing... let me tell u also that i run the same in a sql server 2008 enterprise edition and it show me info...

is it necesary to have whoisactive in master database? because i have it in another database... thanks a lot.

June 11, 2013 12:29 PM
 

Adam Machanic said:

June 11, 2013 12:45 PM
 

Alexander said:

thanks Adam :) , thanks a lot!!!!

June 11, 2013 1:04 PM
 

Shadab Shah said:

Hi Adam,

just wanted to say thanks for such a very good piece of article

July 2, 2013 1:18 PM
 

Anna said:

Hi Adam: I just created tested sp_whoIsActive

When I run it, I see only the xml format.  How can I see the graphic for the execution plan instead of the xml code?

Thanks in a dvance

October 9, 2013 1:51 PM
 

Adam Machanic said:

@Anna: Click on the XML.

If it's not bringing up a graphical version of the plan you might be hitting a known bug in Management Studio; you should upgrade to the most recent Service Pack.

October 9, 2013 4:27 PM
 

Boomer said:

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

Incorrect syntax near ','.

Where the following;

68 --number of active tasks, current wait stats, physical I/O, context switches, and blocker information

69 @get_task_info TINYINT = 1,

70

71 --Gets associated locks for each request, aggregated in an XML format

72 @get_locks BIT = 0,

All Compatibility Levels at 100 (2008)

Any advice?

October 24, 2013 7:28 PM
 

Boomer said:

Nevermind. I resolved my own issue. It was the difference between running the sql query text copied and pasted it into a blank new query window vs. populating the query window by running .sql file locally. Once the text was populated in the query window from the .sql file, the sproc created without issue.

October 24, 2013 8:51 PM
 

Eric Stephani said:

Running on SQL 2012 SP1. I get this message when I include include @get_taskinfo=2 for example:

sp_WhoIsActive @show_sleeping_spids = 0

   , @get_task_info = 2

   , @get_plans=1

go

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 1934, Level 16, State 1, Line 327

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

This is happening regardless of what I have QUOTED_IDENTIFIER set to.

October 29, 2013 9:50 AM
 

Adam Machanic said:

Hi Eric,

SET QUOTED_IDENTIFIER ON, and then re-create the proc. For some reason SQL Server keeps whatever setting is already set, and even though the proc itself tries to override it, the setting that was active when it was created "wins" in most cases.

--Adam

October 29, 2013 10:17 AM
 

ross said:

Hi Adam,

Thanks for the tool.  I only just found this blog series and haven't had a chance to digest it all.

I was wondering if you have any suggestions for my scenario.  I only want to run this proc when there is something interesting happening, like someone is being blocked or a deadlock.  Then I want to run it and dump the results to a table.  The reason being that I am not manually monitoring the server when an issue happens, and my only hope is that a user might phone me if they notice an issue and I can run it by hand.

I can think of what seems to be a crude way to do this, where I write another proc that is fired fairly frequently from a scheduled task and it dumps the results to a table.  But I'm wondering if you can suggest something better?

November 13, 2013 1:32 AM
 

Sam said:

tell me how to see the IP address of the user

December 2, 2013 4:35 AM
 

Andrea said:

I'm trying on my SQL server 2008 sp3 but every time it show me only one row:

EXEC dbo.sp_WhoIsActive

@get_plans = 1,

@get_task_info = 2

<?query --

WAITFOR(RECEIVE conversation_handle, service_contract_name, ..

where are all other transactions??

December 5, 2013 11:22 AM
 

Adam Machanic said:

@Andrea

What makes you think there is other activity?

How many rows does this return?

SELECT *

FROM sys.dm_exec_requests AS r

INNER JOIN sys.dm_exec_sessions AS s ON

s.session_id = r.session_id

WHERE

s.is_user_process = 1

AND s.session_id <> @@SPID

December 6, 2013 9:28 AM
 

Israr Haq said:

When is the next version coming? The latest and greatest?

December 14, 2013 1:27 AM
 

Adam Machanic said:

@Israr

Tough to say, but I'm slowly working on it. Is there something in particular you're after that's not in 11.11?

--Adam

December 16, 2013 1:27 PM
 

Sam said:

@Adam Machanic

Help please

"how to see the IP address of the user"

now i use this sql

http://pastebin.com/ULfsrta7

December 16, 2013 11:51 PM
 

Adam Machanic said:

@Sam

It is not currently returned by sp_whoisactive.

Why is the hostname not enough for you?

December 17, 2013 3:49 PM
 

Sam said:

>>Why is the hostname not enough for you?

because we use MS Access 2003 (mde)

and in it the host name is the name of the application

as a result, the host name does not determine user

December 18, 2013 2:09 AM
 

Sampath said:

Thanks Adam Machanic for developing this wonderful tool. Now its the key tool for me

December 26, 2013 7:37 AM
 

Toby H said:

Hi Adam

Is there a reason why you didn't include a primary (clustered) key in the definition when executing this with the @return_schema = 1

Reviewing my results I suspect that the collection_time and session_id are good candidates, but any comments would be appreciated.

Toby

January 1, 2014 9:00 PM
 

Adam Machanic said:

@Toby H

The only key that would make sense given the context of a call to sp_whoisactive would be (session_id, request_id). But naturally there are a huge number of potential use cases -- such as collection to the same table multiple times -- so any key I create won't be the correct key for some or many users. Therefore I create no key at all, and leave that as an exercise for people who need one. It's better for everyone that way :-)

--Adam

January 2, 2014 2:02 PM
 

Toby H said:

Hey Adam

Request_id doesn't really qualify as its a nullable column, but thanks for the suggestion.

As for our use case, we create a table per day and perform collections approximately every 10-15 minutes during the working day, retaining the results for 30 days. This has been an invaluable tool in troubleshooting some of our more tricky performance issues, so thanks for all your efforts.

January 2, 2014 6:16 PM
 

Adam Machanic said:

@Toby H

Regarding request_id, easily solved: Just use a clustered UNIQUE index or constraint rather than a PK. For all intents and purposes it's the same thing. Add the collection_time and you'll be all set. You may not need request_id in there at all, but it's good to include it just in case.

--Adam

January 3, 2014 3:16 PM
 

Sandra said:

Adam,

I just ran sp_whoisactive 11 to create a stored procedure and the execution completed successfully. I tried executing the procedure without any parameters but I am getting an error message: Warning: Null value is eliminated by an aggregate or other SET operation.

dd hh:mm:ss.mss. Is there something I need to include in the code?

January 21, 2014 11:55 AM
 

Adam Machanic said:

@Sandra

That is not an error. Just ignore it. You won't see it anyway, unless you look in the messages pane - make sure your have SSMS set to Grid mode. That's what Who is Active is designed for.

--Adam

January 21, 2014 5:39 PM
 

Iyer said:

Hi Adam,

I'm a great fan of this SP. I've been using this since last few years. I can't wait to see the updated one for SQL 2012.

February 13, 2014 2:14 PM
 

Carmine said:

This stored proc saved my butt today. Saved me a ton of time. And helped me win an argument too. Awesome!

March 17, 2014 10:47 AM
 

Adam Machanic said:

@Carmine

Excellent! That's exactly why I published it :-)

March 17, 2014 5:25 PM
 

Iain Elder said:

This month I used sp_WhoIsActive to prove the major source of latency in a logging database. Thanks from Edinburgh, Adam!

Where is the best place to ask questions about or request features for sp_WhoIsActive?

Any objections to hosting the code somewhere like Bitbucket, Github, or Codeplex?

March 17, 2014 8:35 PM
 

Adam Machanic said:

@Iain

You can ask right here.

Any objections to hosting the code somewhere? Yes. Don't post my code anywhere. You may download it to your own server and beyond that I absolutely do NOT give you permission to post or distribute it in any form.

March 18, 2014 12:33 AM
 

Iain Elder said:

Okay, thanks for the clarification.

Could you update the link at the top of the recap page to point to the latest version?

I installed v11.00 because that's what the download link at the top points to. I didn't realize there was a later version.

Would just make the discovery process a little easier :-)

March 18, 2014 8:53 AM
 

Iain Elder said:

I use an agent job to log output periodically into a table like this:

EXECUTE sp_WhoIsActive

 @format_output = 0,

 @destination_table = 'WhoIsActiveLog';

I use this query to estimate which tables have a lot of page latch waits when things insert into them.

WITH InsertPageLatchWaits AS (

 SELECT

   TableName = SUBSTRING(sql_text, 13, PATINDEX('%[^a-z.]%', RIGHT(sql_text, LEN(sql_text) - 12)) - 1)

 FROM ActivityLog.dbo.WhoIsActiveLog

 WHERE wait_info LIKE '%PAGELATCH%' AND sql_text LIKE 'INSERT INTO %'

)

SELECT

 TableName,

 WaitCount = COUNT(*)

FROM InsertPageLatchWaits

GROUP BY TableName

ORDER BY WaitCount DESC;

My query gives me the info I need, and I proved the bottleneck.

My query is quite complex, though. I am parsing the table name out of the SQL text!

Is there an easier way to log and retrieve this information using sp_WhoIsActive?

I'm using v11.00.

Thanks!

March 18, 2014 9:03 AM
 

SWade said:

Thank you for the stored procedure it is very helpful.  I have some trouble with it that you might be able to help me with. When i click on the sql_text column i don't see the graphical execution plan like some of my peers do.  I get the xml version which is not as helpful.  Can you tell me what is missing from my configuration to not see the graphical execution plan?

thank you,

March 26, 2014 3:48 PM
 

Adam Machanic said:

March 26, 2014 10:05 PM
 

Pokir said:

The query_plan returned by sp_whoisactive, is it estimated execution plan or actual execution plan?

I have collected the data using sp_whoisactive for few days and noticed one query was taking longer on certain times and running quickly at certain times. I wanted to see the actual execution plan for good and bad runs, but I only see same execution plan for good and bad run and the plan shows in only estimated plan, so not sure if the actual execution plan is any different. Wait_type is showing null for the query while it was running long, so probably it was not waiting much.

How to know why some runs of the query is taking longer using sp_whoisactive data collected

April 29, 2014 3:56 AM
 

Chris92 said:

Hi !

Is it possible to add the name of the stored procedure / function executing ?

June 5, 2014 11:35 AM
 

Adam Machanic said:

@Chris92

Not really. You can usually get a much better idea if you use @get_outer_command = 1.

June 5, 2014 1:37 PM
 

Chris92 said:

Excellent ! It is exactly what i was looking for !

Really, this proc i killing everything !! thanx :)

June 6, 2014 11:42 AM
 

max said:

Hi Adam,

I have been using whoisactive for very long time and now we have started using SQL Server Azure and i understand that there are some limitation on Azure, but when i try to deploy the procedure i get the below error...

Msg 40515, Level 15, State 1, Procedure sp_WhoIsActive, Line 16

Reference to database and/or server name in 'msdb.dbo.sysjobs' is not supported in this version of SQL Server.

Would you happen to know why this would or is there anything we can do to fix this?  Is there a version of whoisactive for Azure by any chance?

July 17, 2014 4:42 PM
 

Adrian said:

Adam, thanks for thi wonderfull tool. Any idea about the release of the new SQL Server 2012 specific version?

Thanks in aadvance

July 29, 2014 10:26 AM
 

Paul said:

Apologies for all the ping backs from me, I used some of the same techniques you did in a stored procedure and mentioned this link in references section of an article and proc. Please feel free to delete them.

Thanks and regards

August 18, 2014 11:14 AM
 

Overcoming Issues with TempDB Contention said:

November 6, 2014 6:50 PM
 

Scott said:

Thanks very much for sharing this tool, it is invaluable.  One question, when trying to run it on one of our instances (SQL 2008 SP2 - 10.0.4000.0) I receive the follow errors:

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

Incorrect syntax near '.'.

Msg 156, Level 15, State 1, Procedure sp_WhoIsActive, Line 3941

Incorrect syntax near the keyword 'CASE'.

Msg 156, Level 15, State 1, Procedure sp_WhoIsActive, Line 3979

Incorrect syntax near the keyword 'CASE'.

I'm sure SQL 2008 can handle CROSS APPLY?

Kind Regards,

Scott

November 7, 2014 5:23 AM
 

sp_whoisactive | dbo said:

November 9, 2014 9:38 PM
 

Adam Machanic said:

@Scott: Your instance database is set to a legacy compatibility mode. Change it to 2008 mode and you should be all set.

--Adam

November 10, 2014 9:38 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