Released: Who is Active v11.11

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

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

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.

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

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.

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.

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.    

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.

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

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.

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

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

Thanks!

Thank you and great work!

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?

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.

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

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!

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

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 ?

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

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

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

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.

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!

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>

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

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

Hi Adam

I am getting this error

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

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.

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

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

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!

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

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!

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?

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?

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

PingBack from http://qandasys.info/landed-as-bi-but-databases-are-a-big-wtf-what-to-do/

PingBack from http://www.borgolte.ch/?p=714

Hi there,

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

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.

PingBack from http://www.dbnewsfeed.com/2013/04/30/five-ways-to-improve-your-productivity-with-ssms/

PingBack from http://www.fradensql.com/2013/05/estimating-remaining-query-time/

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