Skip to Content

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!

192 Comments

  • Tony

    Mar 22, 2012 at 06:00 PM

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

  • Tony: Maybe in the next version 🙂

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

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

  • Mar 23, 2012 at 04:40 AM

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

  • Craig M.

    Mar 24, 2012 at 10:03 AM

    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.

  • Max

    Apr 18, 2012 at 04:30 PM

    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.

  • Scott

    Apr 18, 2012 at 06:02 PM

    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.

  • max

    Apr 19, 2012 at 08:27 AM

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

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

  • Chris

    Apr 27, 2012 at 10:55 AM

    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.

  • 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

  • Phil Carter

    Jun 06, 2012 at 03:35 AM

    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

  • 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 🙂

  • RIzwan Hassan

    Jun 21, 2012 at 09:34 PM

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

    Thanks!

  • Rizwan:

    EXEC sp_whoisactive @filter_type = ‘database’, @filter = ‘your DB name’

  • Rizwan Hassan

    Jul 02, 2012 at 09:07 AM

    Thank you and great work!

  • Sergio Pacheco

    Jul 25, 2012 at 10:20 AM

    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?

  • 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

  • Rob Kraft

    Aug 13, 2012 at 09:50 AM

    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.

  • 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

  • DanOrc

    Aug 13, 2012 at 10:35 AM

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

  • 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

  • Rob Kraft

    Aug 13, 2012 at 01:35 PM

    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!

  • Max

    Aug 20, 2012 at 10:55 AM

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

  • max

    Aug 20, 2012 at 01:13 PM

    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 ?

  • 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

  • max

    Aug 20, 2012 at 02:30 PM

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

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

    EXEC sp_whoisactive @format_output = 0

  • max

    Aug 20, 2012 at 06:31 PM

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

  • TheSeventhDawn

    Sep 17, 2012 at 02:44 PM

    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

  • Chris at DEA not that DEA

    Dec 07, 2012 at 02:49 PM

    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.

  • Gianluca Sartori

    Dec 12, 2012 at 05:46 AM

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

  • max

    Dec 17, 2012 at 01:00 PM

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

  • 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

  • Max

    Dec 18, 2012 at 01:16 PM

    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…

  • Max

    Dec 18, 2012 at 01:39 PM

    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 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…]

  • HIMANSHU

    Jan 10, 2013 at 07:51 PM

    Hi Adam

    I am getting this error

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

  • Himanshu:

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

    –Adam

  • HIMANSHU

    Jan 11, 2013 at 02:15 PM

    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.

  • dbiDBA

    Jan 14, 2013 at 02:26 PM

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

  • Shiju Samuel

    Jan 28, 2013 at 08:49 AM

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

  • 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

  • Dustin Mueller

    Feb 06, 2013 at 12:26 PM

    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!

  • Chris

    Feb 28, 2013 at 08:42 AM

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

  • @Chris: EXEC sp_whoisactive @help = 1

  • yazalpizar

    Mar 14, 2013 at 08:07 AM

    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!

  • NewKidInTown

    Apr 18, 2013 at 10:10 AM

    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?

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

  • Mike Stuart

    Apr 23, 2013 at 11:10 AM

    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?

  • Hi Mike,

    EXEC sp_whoisactive @help = 1

    🙂

  • Mike Stuart

    Apr 23, 2013 at 11:49 AM

    … 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 😛 Thanks.

  • Jordon (HeavenCore)

    Apr 29, 2013 at 10:05 AM

    Hi there,

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

  • Cary Davis

    Apr 29, 2013 at 11:32 AM

    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.

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

    @Cary: Cheers!

  • Steelie

    May 23, 2013 at 01:33 PM

    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

  • Ed

    May 28, 2013 at 04:19 AM

    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?

  • @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

  • Ed

    Jun 06, 2013 at 04:22 AM

    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.

  • Alexander

    Jun 10, 2013 at 03:37 PM

    is it work in sql server 2008 r2 express?

  • @Alexander: I think so.

  • Alexander

    Jun 11, 2013 at 12:29 PM

    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.

  • Alexander

    Jun 11, 2013 at 01:04 PM

    thanks Adam 🙂 , thanks a lot!!!!

  • Shadab Shah

    Jul 02, 2013 at 01:18 PM

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

  • Anna

    Oct 09, 2013 at 01:51 PM

    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

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

  • Boomer

    Oct 24, 2013 at 07:28 PM

    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?

  • Boomer

    Oct 24, 2013 at 08:51 PM

    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.

  • Eric Stephani

    Oct 29, 2013 at 09:50 AM

    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.

  • 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

  • ross

    Nov 13, 2013 at 01:32 AM

    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?

  • Sam

    Dec 02, 2013 at 04:35 AM

    tell me how to see the IP address of the user

  • Andrea

    Dec 05, 2013 at 11:22 AM

    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

  • @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

  • Israr Haq

    Dec 14, 2013 at 01:27 AM

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

  • @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

  • Sam

    Dec 16, 2013 at 11:51 PM

    @Adam Machanic
    Help please

    “how to see the IP address of the user”

    now i use this sql
    http://pastebin.com/ULfsrta7

  • @Sam

    It is not currently returned by sp_whoisactive.

    Why is the hostname not enough for you?

  • Sam

    Dec 18, 2013 at 02:09 AM

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

  • Sampath

    Dec 26, 2013 at 07:37 AM

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

  • Toby H

    Jan 01, 2014 at 09:00 PM

    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

  • @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

  • Toby H

    Jan 02, 2014 at 06:16 PM

    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.

  • @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

  • Sandra

    Jan 21, 2014 at 11:55 AM

    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?

  • @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

  • Iyer

    Feb 13, 2014 at 02:14 PM

    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.

  • Carmine

    Mar 17, 2014 at 10:47 AM

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

  • @Carmine

    Excellent! That’s exactly why I published it 🙂

  • Iain Elder

    Mar 17, 2014 at 08:35 PM

    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?

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

  • Iain Elder

    Mar 18, 2014 at 08:53 AM

    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 🙂

  • Iain Elder

    Mar 18, 2014 at 09:03 AM

    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!

  • SWade

    Mar 26, 2014 at 03:48 PM

    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,

  • Pokir

    Apr 29, 2014 at 03:56 AM

    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

  • Chris92

    Jun 05, 2014 at 11:35 AM

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

  • @Chris92

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

  • Chris92

    Jun 06, 2014 at 11:42 AM

    Excellent ! It is exactly what i was looking for !
    Really, this proc i killing everything !! thanx 🙂

  • max

    Jul 17, 2014 at 04:42 PM

    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?

  • Adrian

    Jul 29, 2014 at 10:26 AM

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

  • Paul

    Aug 18, 2014 at 11:14 AM

    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

  • Scott

    Nov 07, 2014 at 05:23 AM

    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

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

    –Adam

  • Srinivas

    Dec 31, 2014 at 08:33 AM

    when i execute this command ‘Exec sp_WhoIsActive’ i got below output.

    O/P

    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure ‘sp_WhoIsActive’.

    what should i do to fix this.i am using SQL 2012 enterprise edition

  • Jan 04, 2015 at 08:10 PM

    @srinivas

    Did you run the script to install it?

    –Adam

  • James Anderson

    Jan 14, 2015 at 10:57 AM

    Hi Adam,

    Does disabling the auto stats on your temp table #sessions make a big difference to the run time?

  • Jan 14, 2015 at 04:28 PM

    @James

    In some cases, yes. Depends on the server bottlenecks (if any) and how many sessions are in the table. But it’s certainly there for a reason.

    –Adam

  • Sean Walker

    Jan 21, 2015 at 04:01 PM

    Is there a reason, beyond performance, for a Primary Key on the table variable @blockers? The procedure will fail in the (albeit unusual) case where you get negative SessionIDs.

    Admittedly, taking out the PK is easy, but I’m not as certain as to how the output will be affected since the negative spids are not going to be unique in sys.sysprocesses.

  • Jan 22, 2015 at 09:21 AM

    @Sean

    I think negative SPIDs will be fine. Are you sure they’re causing a problem? Everything is rolled up by SPID, so uniqueness in sysprocesses shouldn’t matter. (As a matter of fact one of the reasons I first wrote it was that I was sick of seeing so many rows in that view every time I had a parallel query plan.)

    There is definitely another issue where sometimes there can be a PK violation due to the DMVs erroneously reporting a loop in the blocking chain — which of course shouldn’t (and hopefully can’t) ever happen (at least, not without the deadlock monitor figuring it out long before Who is Active does). The solution there is not to remove the PK but rather to fix the code.

    Getting back to your question: Yes–for the most part it’s only there for performance. The code should make everything unique enough on its own. But as I mentioned the PK has thrown errors in the past so it’s actually serving a dual purpose.

    –Adam

  • Sean Walker

    Jan 22, 2015 at 02:02 PM

    Sorry, I was unclear, it’s not that SPIDs are negative that is the problem. It was that I was getting a PK violation on the table variable. No deadlocks were reported in the system, just a lot of blocking that we were trying to diagnose.

    If I infer correctly, the only time that the PK on @blockers should throw an error is when there was a loop in the blocking chain. The issue presented on a server I was brought in to “consult” on, I find the output from sp_whoisactive so invaluable as a starting point, so I was surprised when they informed me it aborted with a PK violation error. The issue went away, I just want to ensure I can get information back if it were to ever represent itself.

    Thanks for such a great proc btw.

  • Jan 22, 2015 at 02:28 PM

    @Sean

    Yes, it’s not a REAL deadlock, just crap data in the DMVs.

    I think changing the definition of @blockers to the following will fix it — but I haven’t tested yet:

    DECLARE @blockers TABLE
    (
    session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)
    )

    –Adam

  • Jhoysser

    Jan 30, 2015 at 11:32 AM

    hi, it is my first time running this procedure but i can’t run, how is the most simple exec code to diagnostic my DB

  • Jan 30, 2015 at 03:08 PM

    @Jhoysser

    What do you mean you can’t run it? What have you done so far?

    Did you open the script in SSMS, connect to the server, and hit F5 to “install” it? After that you can do EXEC sp_whoisactive — and you should see a result set (maybe an empty one).

  • Stefan Gustafson

    Feb 05, 2015 at 05:19 AM

    I have found a bug in sp_whoisactive version 11.11 when used on SQL server2014

    I have found the problem and fixed it, but I am not sure if I have broken any otjher functionality.

    Thank you for an excellent piece of code!

    /Stefan Gustafsson

    PROBLEM DESCRIPTION

    Sometimes sp_whoisactive returns 0 for CPU and NULL for query_plan for a long-running parallell query
    For me this occurred when running a stored procedure that contained several long-running parallell statements

    ANALYSIS

    While the problematic query is running:

    select spid, login_time, last_batch,status from sys.sysprocesses where spid=72;
    select session_id, login_time, last_request_end_time from sys.dm_exec_sessions where session_id=72;

    spid login_time last_batch status
    —— ———————– ———————– ——————————
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 suspended
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 runnable
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 runnable
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 runnable
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:36.003 runnable

    (13 row(s) affected)

    session_id login_time last_request_end_time
    ———- ———————– ———————–
    72 2015-02-05 07:10:36.003 2015-02-05 07:10:35.993

    (1 row(s) affected)

    The following code in sp_whoisactive:

    LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON
    sp.status <> ”sleeping”
    AND r.session_id = sp.session_id
    AND r.request_id = sp.request_id
    AND
    (
    (
    s.is_user_process = 0
    AND sp.is_user_process = 0
    )
    OR
    (
    r.start_time = s.last_request_start_time
    AND s.last_request_end_time = sp.last_request_end_time —< <<<<< PROBLEM HERE ) ) Assumes that sys.sysprocesses.last_batch and sys.dm_exec_sessions.last_request_end_time are exactly the same. This is not true in my case. I do not understand the purpose of this condition. I have fixed the problem by removing the row indicated above, but I am not sure if this might cause other problems select @@version Microsoft SQL Server 2014 - 12.0.2430.0 (X64) Oct 15 2014 16:05:37 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )

  • Feb 07, 2015 at 10:43 AM

    @Stefan

    I am aware of this problem but I don’t consider it to be a “bug” in WhoIsActive — rather I think it is a bug in the DMVs. The purpose of the predicate is to make sure that all data is aligned, even in high throughput environments.

    Remember that the DMVs utilize no locking or other means by which to guarantee transactional consistency. Consider the case of an environment in which there are thousands of queries running each second. Over the course of a given WhoIsActive run we might read some rows off of one DMV for SPID 123 as query #1 is running. Then the SPID running WhoIsActive gets pushed off of the scheduler for a few milliseconds, during which SPID 123 finishes query #1 and is reused for query #2. Now we read some data off of another DMV. If we join only on SPID, we’re reporting data on one row, about TWO different queries — and that can’t possibly be correct.

    To get around this issue I decided to take a conservative approach and throw out potentially suspect data. I leveraged the batch time columns, which seem to be aligned in 99.99% of cases and which I assumed at the time were read from the same memory location. (Why wouldn’t they be?!?) Alas, there are those odd cases in which the two values are a couple of milliseconds off, which throw the entire scheme out of whack.

    Bottom line: If your workload is all long-running queries removing that predicate shouldn’t matter much. (Perhaps it would matter on an edge where one query finishes and another starts.) I think it’s safer to leave it in. I have been trying to come up with a workaround but to date I’ve not managed to think of anything reasonable.

    A good idea, IMO, would be if the DMVs would expose the Extended Events activity_id (i.e. correlation key). This could serve as a great surrogate key for individual requests. Alas, the SQL Server team does not agree… (https://connect.microsoft.com/sql/Feedback/Details/432686)

    –Adam

  • Stefan Gustafsson

    Feb 17, 2015 at 05:34 AM

    @Adam

    I understand what you are saying about the problem of combining data from different DMVs.

    But I respectfully disagree about what is the conservative approach.

    I think it is much worse to give wrong answers for long-running queries than for very fast-running queries. (Of course i might be biased since i mostly work with data warehouses with lots of extremely long-running queries)

    If you use sp_WhoIsActive with lots of fast-running queries I think it is reasonable to expect the information to be unstable – after all the result is only valid at a certain point in time – if you run the query again a second later the result will be completely different.

    On the other hand, if you have a very long-running query it is very confusing that sp_whoisactive is unable to return a correct CPU counter and query plan. You can rerun the procedure all you want – it will always give exactly the same result.

    I agree that having a real correlation key would be the best solution, but since this is not available I think it would be better to remove this condition and accept the risk for incorrect results with fast-running queries.

    Best regards
    /SG

  • Dennis Post

    Feb 18, 2015 at 10:34 AM

    @Adam,

    Thanks for creating this !!

    Is see you are output job_ids instead of job names.
    Here’s how I get the actual job name from Program_name.

    IF OBJECT_ID(‘TempDB..#TmpWho2’) IS NOT NULL
    DROP TABLE #TmpWho2

    CREATE TABLE #TmpWho2
    (
    SPID Int,
    [Status] Varchar(25),
    [Login] Varchar(50),
    HostName Varchar(50),
    Blkby Varchar(20),
    DBName Varchar(50),
    Command Varchar(50),
    CPUTime Int,
    DiskIO Int,
    LastBatch Varchar(20),
    ProgramName Varchar(150),
    SPID2 Int,
    Requested Bit
    )

    INSERT INTO #TmpWho2 EXEC sp_Who2

    — Blocking only
    SELECT SPID, Blkby, HostName, DBName, CPUTime, DiskIO, RTRIM(status) [Status],

    CASE
    WHEN RTRIM(ProgramName) LIKE ‘SQLAgent – TSQL JobStep (Job %’ THEN ‘SQLAgent Job : ‘ + J.Name
    ELSE RTRIM(ProgramName)
    END ProgramName

    , RTRIM(Command) Command, RTRIM([Login]) LoginName, LastBatch
    FROM #TmpWho2
    CROSS APPLY
    (
    VALUES (
    CASE
    WHEN RTRIM(ProgramName) LIKE ‘SQLAgent – TSQL JobStep (Job %’ THEN
    CAST(CONVERT(VARBINARY(MAX),’0x’ + SUBSTRING(RTRIM(ProgramName), 32, 32),1) AS uniqueidentifier)
    ELSE NULL
    END
    )
    ) Val1(UqID)
    OUTER APPLY
    (
    SELECT DISTINCT Name
    FROM MSDB.dbo.SysJobs
    WHERE Job_ID = Val1.UqID
    ) J
    WHERE Blkby <> ‘ .’

  • Feb 18, 2015 at 10:45 AM

  • Dennis Post

    Feb 19, 2015 at 03:53 AM

    @Adam,

    I should have known. 🙂
    Great stuff! I’ll (re)read them all.

  • Adrian

    Feb 26, 2015 at 02:11 PM

    @Adam,

    A newbie question: what is the unit of measurement for CPU? Miliseconds? And how do I convert that into CPU usage (percentage)?

    I spent hours looking for this info.

  • Feb 26, 2015 at 03:39 PM

    @Adrian:

    Milliseconds.

    And how to convert that into a percentage? “Percentage” is an instantaneous measure, not something that’s time bound. So the simple answer is that you can’t; it just doesn’t make sense, given the granularity and form of the data.

    –Adam

  • Feb 26, 2015 at 11:02 PM

    @Adrian:

    I thought about this some more and I think you can cobble something together if you use the @delta_interval option (http://sqlblog.com/blogs/adam_machanic/archive/2011/04/26/delta-force-a-month-of-activity-monitoring-part-26-of-30.aspx). You might want to enable it in conjunction with @get_task_info = 2 (http://sqlblog.com/blogs/adam_machanic/archive/2011/04/28/who-is-active-s-hidden-gems-a-month-of-activity-monitoring-part-28-of-30.aspx).

    So you’d do something like:

    EXEC sp_whoisactive
    @get_task_info = 2,
    @delta_interval = 5,
    @get_additional_info = 1

    This would give you accurate CPU time for all tasks that were running for the entire 5-second interval. You could then divide the CPU time for each task by 10, and that would give you an approximate percentage of a single CPU, based on the 5 second collection interval. (For parallel tasks the number would go over 100.) I guess you’d also have to use the data collection mode to get the data into a temp table in order to do the calculations.

    Hope that gets you a bit closer to your goal.

    –Adam

  • Adrian

    Mar 02, 2015 at 07:02 AM

    @Adam:

    Thanks a lot for this info. 🙂

  • Rudy Panigas

    Mar 30, 2015 at 09:21 AM

    Hello,

    Excellent script!!!

    I do get warnings (see below) and wanted to know if others are getting the same on SQL 2012

    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.
    Warning: Null value is eliminated by an aggregate or other SET operation.
    dd hh:mm:ss.mss

  • Mar 30, 2015 at 09:27 AM

    @Rudy:

    Those are expected. Don’t worry about it 🙂

  • Rudy Panigas

    Mar 30, 2015 at 10:27 AM

    @Adam.. Thanks for the reply 🙂

  • Larry Meklemburg

    May 19, 2015 at 08:16 AM

    I may have given you kudos a while back, but this v11.11 is absolutely excellent. And the comments given have been some shining examples of the usefulness and the complexity of the procedure.

    Thank you.
    Larry
    Clevelend, OH

  • Guy-Roger Tilkin

    Jul 30, 2015 at 10:33 AM

    Hello,

    A nice feature would be to have the serverName as column output. In a great environment with several instances (50-100 SQl Server) and if you want to automate monitoring from one Admin instance at the same time…

    Thanks anyway for this great tool, combined with a SQL Counter monitoring, it does the job!

    Guy-Roger

  • Marty

    Oct 19, 2015 at 09:00 AM

    Adam, great procedure! I use it all the time. If I see a lot of processes running for hours that are all in a SLEEPING status, I am thinking these are from an application using connection pooling, and nothing to worry about. Do you think that’s a safe guess? Is it a problem if the open_tran_count is > 0 with everything sleeping? If I see processes that are blocked by a sleeping process. That does seem like a problem. Would you agree? Perhaps in this case, the sleeping process has actually errored or timed out on the application side.

  • Oct 19, 2015 at 12:55 PM

    @Marty

    It may or may not be connection pooling. I’ve seen some non-pooled applications hold open sleeping connections — WITH an open transaction — for no good reason I can think of. One thing I can say for certain is that it’s just bad transaction management! If you can talk to the vendor and/or developers (for an in-house app) it would in my opinion definitely be worth the effort to push them to cleaning up their mess.

    –Adam

  • Ross Ilott

    Jan 08, 2016 at 06:47 AM

    Hi Adam,

    Thanks for your work on this!

    Does whoisactive deal with availability groups appropriately?

    “The target database (‘‘) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.”

  • Wellington Prado

    Feb 05, 2016 at 05:54 PM

    Hi Adam,

    Great tool! Congrats!!!
    I saw when I have a long running transaction, the values on CPU, READS and so on are accumulating and if someone wants to plot this session information at excel the values are too much high (because of transaction not end’s yet), but we know the rigth view would be the delta values between each exec even at inside of a big transaction.

    I know too the tool has a delta option but I’m talking about of the simple view.

    I fix that situation doing something like this:

    Case When Cpu = 0 Then Cpu
    Else
    Cpu – LAG(Cpu, 1,0) OVER (PARTITION BY [session_id],[login_name],[database_name],[Login_Time] ORDER BY [collection_time] ) END AS CPU,

    Thanks again…

  • John Couch

    Feb 10, 2016 at 10:39 PM

    Hi Adam, I was using sp_whoisactive and set @find_block_leaders = 1 and it is returning all active sessions regardless of being blocked. The instance version is SQL Server 2008 SP2 10.0.4279.0 (64 bit). Is there something I am missing?

  • Feb 11, 2016 at 09:10 AM

    @Ross:

    Not sure what’s going on there. Sounds like something might be misconfigured on your end with regard to aliases — the proc works perfectly on both RW and RO nodes on this end. What parameters are you using? Maybe there’s a weird edge case or something.

    @Wellington:

    Good tip!

    @John:

    That is the correct behavior. The find_block_leaders option adds some additional information to the output; it doesn’t filter anything.

  • Gmanunta81

    Feb 11, 2016 at 11:22 AM

    Hello I tryed to use your script on my system SSQLServer 2012

    I created the Procedure in master Database

    EXEC sp_whoisactive @filter_type = ‘database’, @filter = ‘TESTDB’

    But the procedure return an empty table

    why ? (to test the procedure I have a web application the executes queries on db )

    Best regards and thank you to provide your script to the comunity

  • Feb 11, 2016 at 02:51 PM

    @Gmanunta81:

    No clue, sorry. Did you try running it without the filters?

  • Gmanunta81

    Feb 12, 2016 at 03:45 AM

    Dear Adam Thanks for your help ang Good morning (Italian time) !!!!!
    I tried these script on development server (Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor))

    EXECUTE [dbo].[sp_WhoIsActive]

    EXEC sp_whoisactive @filter_type = ‘database’, @filter = ‘DBTOTEST’

    EXEC sp_whoisactive @help = 1

    GRANT EXEC ON dbo.sp_WhoIsActive TO [public];

    EXEC sp_whoisactive @find_block_leaders = 1

    I also Tried the Version 11.00 and 11.11
    I also installed the procedure on a Master database
    I also debugged the procedure but I didn’t find any error
    I sqw the the query SELECT * FROM sys.dm_broker_activated_tasks used in the procedure doesen’t return rows

    thank you in advance for you help
    The system table

  • Gmanunta81

    Feb 12, 2016 at 04:21 AM

    Hello it works !!! Dear Adam there are not issue on the server or DBMS !!!
    The reason is in dev environment there are no active process

    So I ‘m sorry soi mutch Great jobss !!!!!!!!!!!!!

  • Wes3flex

    Apr 20, 2016 at 04:28 PM

    Adam,

    Once in a blue moon I receive a “String or binary data would be truncated error”. Any ideas?

    Thanks

  • Apr 21, 2016 at 01:20 PM

    @Wes3flex:

    Not sure. What parameters are you using? ANY idea about common factors?

    –Adam

  • Wes3flex

    Apr 21, 2016 at 01:52 PM

    Adam,

    I am running the following. I am inserting it into a table and it appears to be giving me that “String or binary data would be truncated error”.

    EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @destination_table = @destination_table ;

  • Apr 22, 2016 at 03:02 PM

    @wes

    How did you create the table? Did you have sp_whoisactive generate the schema for you? Sounds like one of the string columns is too small.

    –Adam

  • Wes3flex

    Apr 25, 2016 at 10:19 AM

    Adam,

    I actually had went back and put all the strings at max size. I also put in a job just to run the whoisactive and just running the job produces the same error. So it seems the output is larger than what is declared in the job.

  • Herman

    May 08, 2016 at 08:54 AM

    Hi Adam,

    Is it possible to include the column ‘resource_description’ when the lock type is ‘KEY’? It is more easier to locate the row with the virtual column %%lockres%% . Now we need to query the sys.dm_tran_locks table again.

    Thanks.

  • May 08, 2016 at 03:17 PM

    @Herman

    Unfortunately that would make the output way too heavy. There might be, e.g., three thousand key locks and instead of passing back one fact that says there are there thousand key locks, the output would have to include all three thousand hashes.

    What is the scenario in which you have to do lockres lookups on a regular basis?

    –Adam

  • Herman

    May 11, 2016 at 10:28 AM

    Hi Adam,

    We will locate the record and simulate how the deadlock occur in some batch functions. Since we process the sql statement with prepared statements, the identity column and the related column value cannot be shown in sql_text. When the system crash, we cannot notify our users and the affected row which cannot be processed.

  • May 11, 2016 at 02:09 PM

    @Herman

    While I understand your goal, I really do not think the lock hash is a good way to accomplish what you’re after. The reasons are: A) It is not scalable; it’s not indexed or anything like that. B) It can change, for a given row, over time. C) It could be a red herring. Perhaps that row just happened to participate in that particular deadlock; who is to say that some other row wouldn’t have had the same problem, or that next time that row wouldn’t cause any issues at all?

    Personally, approaching a problem like deadlocks:

    A) Have the application re-try the transaction once or twice.
    B) If it keeps failing due to a deadlock, then yes, you should log it. But keep track of the arguments in the application, and log whatever you sent.
    C) Use the SQL Trace or Extended Events deadlock events to help identify the problems on both sides of the equation.

  • Herman

    May 16, 2016 at 02:10 AM

    Hi Adam,
    Thank you for your advice. I will try it

  • dm_exec_session_wait_stats

    May 16, 2016 at 02:36 PM

    Are there plans to release a new version that can use dm_exec_session_wait_stats on SQL 2016?

  • May 16, 2016 at 10:22 PM

    @dm_exec_session_wait_stats

    Maybe. I’d certainly like to leverage it and I know exactly how I’d put it together, but so far I have been having a lot of issues with the view. If the output stabilizes and looks reasonable then I’ll definitely give it a go.

  • jbjones

    May 17, 2016 at 01:47 PM

    Were you able to correct the maxrecusion issue when finding block leaders?

  • Geoff Patterson

    May 24, 2016 at 05:18 PM

    A nice small improvement for a future version might be to allow retrieval of both the sql_text and the “full_inner_text”. The @get_full_inner_text toggles which you get, but cannot provide both. When we ported our previous internal tool to be a wrapper around sp_whoisactive (which is wonderful, thank you!), we were able to add many new features. This was the one thing that was missing, so we made that modification ourselves and allowed both “sql_text” and “full_sql_text” as valid values in the @output_column_list.

  • May 24, 2016 at 09:41 PM

    @jbjones: Apologies, I haven’t released that new version yet! Really sorry, I know it’s been a long time.

    @Geoff P: That sounds a bit heavy. Why do you need both? Maybe better to store the full_inner_text along with the statement start and end offsets. Then you can simply parse it out yourself as needed. Those offsets will appear, along with the plan and statement handles, in the [additional_info] column in the next version of the proc.

    –Adam

  • Rajesh Kumar Behera

    Nov 06, 2016 at 04:32 PM

    Hi Adam,

    I ran sp_whoisactive in my ssms but got error that could not find a stored procedure.

    Please let me know what i need to do?

    Best Regards,
    Rajesh

  • Nov 06, 2016 at 04:37 PM

    @Rajesh

    Sounds like you forgot to install it on your instance.

    –Adam

  • Rajesh Kumar Behera

    Nov 06, 2016 at 05:12 PM

    Hi Adam,

    Can you please provide me the link from where i need to download and install it in my instance.

    BR,
    Rajesh

  • Rajesh Kumar Behera

    Nov 06, 2016 at 08:08 PM

    @Adam

    Could you please provide me the link?

    Regards,
    Rajesh

  • Nov 07, 2016 at 08:56 AM

    @Rajesh

    The link is in the post on which you’re leaving these comments 🙂

    –Adam

  • Rajesh Kumar Behera

    Nov 07, 2016 at 12:09 PM

    Hi Adam,

    The name of the link is (who_is_active_v11_11 ) ??

    Please confirm.

    Regards,
    Rajesh

  • Nov 07, 2016 at 01:44 PM

    @Rajesh

    That’s the one!

  • Rajesh kumar Behera

    Nov 08, 2016 at 09:07 AM

    Hi Adam,

    Can I run this store procedure in my production environment??

    Is there any issue if I run this on my master database??

    -Rajesb

  • Nov 08, 2016 at 10:20 AM

    @Rajesh

    I can’t tell you what to do or not do in your environment. I would recommend that you talk to your DBA about this.

  • Kiran

    Nov 14, 2016 at 04:46 PM

    Hi Adam, I am a great fan of this SP. Have a quick question about executing this SP over remote databases. We have few DW servers with exact replica and wanted to get a instant view of all active connections at anytime. Please let me know if this is possible with this SP. We have server links established where only DBA’s can use those links.

    Thanks again
    -Kiran

  • Nov 15, 2016 at 06:00 PM

    @Kiran

    I think it should work, as long as you have the remote procedure call option enabled on your linked servers.

    –Adam

  • Kiran

    Nov 16, 2016 at 09:40 AM

    Thanks Adam. It worked fine with no issues. It looks like the sessions where request_id column is 0 are currently active sessions and the one’s which are NULLs are not active connections. When I executed SP_WHO3, it matches with my assumption. Can you please confirm if that is the case and let me know if i am doing anything wrong.

    Thanks again
    -Kiran

  • Nov 16, 2016 at 12:18 PM

    @Kiran

    Yes, that is correct, although the column I look at for this information is [status]. If it’s either “suspended” or “running” there is an active request.

    –Adam

  • Kiran

    Nov 17, 2016 at 10:14 AM

    Thanks Adam. Is there a way to filter results only for request_id=0 using this SP? If not, its not a big deal as this is giving so much information already and made our lives easy. I am running this SP every few seconds and capturing the data in a table so we can analyze performance issues going back in time.

    –Kiran

  • Nov 17, 2016 at 11:02 AM

    @Kiran

    The default behavior is to show only active requests or sleeping sessions that are holding an open transaction. Do you have a lot of the latter for some reason? That should be a red flag — something you probably want to deal with rather than filtering out.

    –Adam

  • Kiran

    Nov 17, 2016 at 11:37 AM

    Thanks for the update Adam. The reason for my request was, we have a database called Informatica PIM (former Hailer) where they use connection pool from application server (or manage connection using Java Threads from the app server ) and most of the times the sessions are showing as request id NULL with the exception of few with request_id 0. Hence i was asking for that.

    Thanks again

  • Nov 17, 2016 at 06:53 PM

    @Kiran

    Yeah, JDBC is kind of notorious for that. What you can do as a workaround is insert into a temp table and then insert the rows you want from there into your final table. A bit ugly but that’s the only way to make it work today. I hope for a better solution in a future version as I’ve had requests for solving some other similar scenarios.

    –Adam

  • Kiran

    Nov 21, 2016 at 01:43 PM

    Great!! thanks for the update Adam.

    – Kiran

  • Irfan Rasheed

    Nov 22, 2016 at 07:22 AM

    do you have any updated script named 11_12 on internet?

  • Nov 22, 2016 at 05:16 PM

    @Irfan

    Nope. Did you see one somewhere?

    You can now find all official updates here:

    http://whoisactive.com

  • Rocky

    Mar 13, 2017 at 08:44 PM

    Hi Adam,

    Sp_whoisactive is getting blocked by one of our application job and the job is inserting int o temporary table which means it is using tempdb databaase.

    the code that gets blocked ,Can you please let me know how to fix .

    we are unable to collect traces due to this blocking

    UPDATE l SET object_name = REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( o.name COLLATE Latin1_General_Bin2, NCHAR(31),N’?’),NCHAR(30),N’?’),NCHAR(29),N’?’),NCHAR(28),N’?’),NCHAR(27),N’?’),NCHAR(26),N’?’),NCHAR(25),N’?’),NCHAR(24),N’?’),NCHAR(23),N’?’),NCHAR(22),N’?’), NCHAR(21),N’?’),NCHAR(20),N’?’),NCHAR(19),N’?’),NCHAR(18),N’?’),NCHAR(17),N’?’),NCHAR(16),N’?’),NCHAR(15),N’?’),NCHAR(14),N’?’),NCHAR(12),N’?’), NCHAR(11),N’?’),NCHAR(8),N’?’),NCHAR(7),N’?’),NCHAR(6),N’?’),NCHAR(5),N’?’),NCHAR(4),N’?’),NCHAR(3),N’?’),NCHAR(2),N’?’),NCHAR(1),N’?’), NCHAR(0), ” ), index_name = REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( i.name COLLATE Latin1_General_Bin2, NCHAR(31),N’?’),NCHAR(30),N’?’),NCHAR(29),N’?’),NCHAR(28),N’?’),NCHAR(27),N’?’),NCHAR(26),N’?’),NCHAR(25),N’?’),NCHAR(24),N’?’),NCHAR(23),N’?’),NCHAR(22),N’?’), NCHAR(21),N’?’),NCHAR(20),N’?’),NCHAR(19),N’?’),NCHAR(18),N’?’),NCHAR(17),N’?’),NCHAR(16),N’?’),NCHAR(15),N’?’),NCHAR(14),N’?’),NCHAR(12),N’?’), NCHAR(11),N’?’),NCHAR(8),N’?’),NCHAR(7),N’?’),NCHAR(6),N’?’),NCHAR(5),N’?’),NCHAR(4),N’?’),NCHAR(3),N’?’),NCHAR(2),N’?’),NCHAR(1),N’?’), NCHAR(0), ” ), schema_name = REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( s.name COLLATE Latin1_General_Bin2, NCHAR(31),N’?’),NCHAR(30),N’?’),NCHAR(29),N’?’),NCHAR(28),N’?’),NCHAR(27),N’?’),NCHAR(26),N’?’),NCHAR(25),N’?’),NCHAR(24),N’?’),NCHAR(23),N’?’),NCHAR(22),N’?’), NCHAR(21),N’?’),NCHAR(20),N’?’),NCHAR(19),N’?’),NCHAR(18),N’?’),NCHAR(17),N’?’),NCHAR(16),N’?’),NCHAR(15),N’?’),NCHAR(14),N’?’),NCHAR(12),N’?’), NCHAR(11),N’?’),NCHAR(8),N’?’),NCHAR(7),N’?’),NCHAR(6),N’?’),NCHAR(5),N’?’),NCHAR(4),N’?’),NCHAR(3),N’?’),NCHAR(2),N’?’),NCHAR(1),N’?’), NCHAR(0), ” ), principal_name = REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( dp.name COLLATE Latin1_General_Bin2, NCHAR(31),N’?’),NCHAR(30),N’?’),NCHAR(29),N’?’),NCHAR(28),N’?’),NCHAR(27),N’?’),NCHAR(26),N’?’),NCHAR(25),N’?’),NCHAR(24),N’?’),NCHAR(23),N’?’),NCHAR(22),N’?’), NCHAR(21),N’?’),NCHAR(20),N’?’),NCHAR(19),N’?’),NCHAR(18),N’?’),NCHAR(17),N’?’),NCHAR(16),N’?’),NCHAR(15),N’?’),NCHAR(14),N’?’),NCHAR(12),N’?’), NCHAR(11),N’?’),NCHAR(8),N’?’),NCHAR(7),N’?’),NCHAR(6),N’?’),NCHAR(5),N’?’),NCHAR(4),N’?’),NCHAR(3),N’?’),NCHAR(2),N’?’),NCHAR(1),N’?’), NCHAR(0), ” ) FROM #locks AS l LEFT OUTER JOIN [tempdb].sys.allocation_units AS au ON au.allocation_unit_id = l.allocation_unit_id LEFT OUTER JOIN [tempdb].sys.partitions AS p ON p.hobt_id = COALESCE ( l.hobt_id, CASE WHEN au.type IN (1, 3) THEN au.container_id ELSE NULL END ) LEFT OUTER JOIN [tempdb].sys.partitions AS p1 ON l.hobt_id IS NULL AND au.type = 2 AND p1.partition_id = au.container_id LEFT OUTER JOIN [tempdb].sys.objects AS o ON o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) LEFT OUTER JOIN [tempdb].sys.indexes AS i ON i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) LEFT OUTER JOIN [tempdb].sys.schemas AS s ON s.schema_id = COALESCE(l.schema_id, o.schema_id) LEFT OUTER JOIN [tempdb].sys.database_principals AS dp ON dp.principal_id = l.principal_id WHERE l.database_name = @database_name OPTION (KEEPFIXED PLA

  • Mar 14, 2017 at 09:20 AM

    @Rocky,

    sp_whoisactive should not get blocked except by an exclusive schema lock. That should only occur for a moment in the case of a temp table insert. Are you sure it’s actually “blocked” and not just “very slow?”

    The @get_lock_info mode is known to be a major resource hog, and I do not recommend using it except for occasional targeted debugging. (It’s even documented that way!) So my recommendation is to simply turn it off.

    Do you really need info about all locks, all the time? If what you’re really after is blocked object resolution, I recommend running @get_additional_info = 1 in conjunction with @get_task_info = 2. That’s a much lighter weight option.

    Best,

    Adam

  • Rocky

    Mar 14, 2017 at 11:58 AM

    Thank you Adam for prompt reply.

    Yes ,I am using get_lock=1 to get locks information , this option is very useful to understand the level of locks during blocking.
    If i turned it off then we will miss the locks information ,IS there any other option which we can use to get the locks info?

  • Mar 14, 2017 at 03:11 PM

    @Rocky

    What question are you trying to answer?

    No, there is no other option in sp_whoisactive for that info, and you can’t get it any other faster way either. The issue is that the DMV from which this data is sourced is simply very big and very slow. And it’s really not useful all that often, which is why Microsoft hasn’t bothered improving it.

    –Adam

  • Rocky

    Mar 14, 2017 at 03:41 PM

    sp_whoisactive got blocked by one of our application sql job which inserts records into temp variable table(Inset @temptablename) and and the update query from the sp_whoisative is actually waiting on LCK_M_SCH_S until the Insert statement completes.

    It looks like only solution is to remove option @get_locks=1 in sp_whoisactive to avoid blocking.

    FYI ,we did not face the same issue on SQL 2008 r2 but only seeing this issue on SQL 2014 .

  • Mar 14, 2017 at 03:45 PM

    @Rocky

    That’s a shared schema stability lock. All queries take them. Your insert has an X lock for some reason, and you need to figure out why. That should not be the case for something using a table variable. There is simply no way to get around an X schema lock.

    –Adam

  • Antony

    Mar 22, 2017 at 04:02 PM

    I would like to write the result of the sp_whoisactive into a table and save it everyday. Which is the easiest way to do so.

  • Mar 23, 2017 at 10:26 AM

  • Mikoyan

    May 25, 2017 at 06:39 AM

    Hi Adam,

    I am running the sp_whoisactive via tempdb

    I then run the following:

    exec tempdb.dbo.sp_whoisactive

    Yet it yields no results…what am I missing?

    There is activity on the server is I use sp_who2…?

    Thanks

    Mikoyan

  • May 25, 2017 at 10:50 AM

  • Sergio Aranda

    Jun 22, 2017 at 06:24 PM

    Great Tool and great answers, I was trying to implement some improvements and with yours comments I found everything.

    Thank You!!!

  • Greg Dowman

    Jul 14, 2017 at 12:30 PM

    Hi Love this tool, quick question I am wanting to tidy up the data so we only keep maybe a months worth is there a way to do this easily as all the dates in the table are stored as NVARCHAR

Receive comment updates via RSS

Leave a Comment