THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

sp_whoisactive: The Big Fixes!

Four and a half years have flown by since I released sp_whoisactive version 11.11.

It's been a pretty solid and stable release, but a few bug reports and requests have trickled in. I've been thinking about sp_whoisactive v.Next -- a version that will take advantage of some newer SQL Server DMVs and maybe programmability features, but in the meantime I decided to clear out the backlog on the current version.

And so, with that, I present sp_whoisactive version 11.16. Hosted on the brand new home for sp_whoisactive. (Yes, it's a bit minimalist for the moment.)


Published Tuesday, October 18, 2016 10:56 AM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Markus Pöhler said:

Thank you Adam, I will update my Version asap. Thanks for your fantastic work.

October 18, 2016 2:53 PM

Israr Haq said:

Great news!

October 18, 2016 11:16 PM

Israr Haq said:

Great news!

October 18, 2016 11:16 PM

Muthukkumaran Kaliyamoorthy said:

Thanks Adam Machanic for taking your time to build and making it up to date.

The download page has two version 11.16 & 17. Is the 11.17 is the latest one with fixed bug and removed CONCAT function (OR) it only removed of CONCAT function.

October 19, 2016 12:55 AM

Pini Krisher said:


You ate the best....


October 19, 2016 2:11 AM

Andre Batista said:

Thank you for this great piece of art Adam :)

October 19, 2016 3:51 AM

Christian Cobas said:

Thanks! I use it a lot!

October 19, 2016 4:48 AM

Dan said:

Great to hear about the new update to this already awesome tool!  Looking forward to hearing about & using the v.Next version.  Thank you! :-)  

October 19, 2016 5:33 AM

Rob D said:

Huge thanks for creating and maintaining this indispensable tool

October 19, 2016 8:36 AM

Adam Machanic said:


Yes, 11.16 had the CONCAT function. I wanted to be clear in the change sequence so I decided to create 11.17 when I backed it out, after I received some reports yesterday. Functionally they're identical but if you have SQL Server instances that use versions older than 2012 you'll want 11.17.


October 19, 2016 8:56 AM

Michael Wall said:

Thanks so much Adam!  This tool has made my life so much easier with various MS Dynamics products!

October 19, 2016 3:29 PM

Muthukkumaran Kaliyamoorthy said:

Thank you very much Adam! I started using it the new one.

October 20, 2016 2:23 AM

Israr Haq said:

We all have sql 2005 in our production environments! No need to fake. lol

October 20, 2016 5:29 PM

Chris Mercado said:

Great work Adam!  So glad to hear you are still working on this!

October 20, 2016 7:09 PM

Vamsy said:

Great work Adam, thank you. I will update in my environment very soon.

October 22, 2016 1:39 AM

LondonDBA said:

Just echoing all the others in saying thanks for this great procedure.

We are currently using Who Is Active? v11.11 (2012-03-22). For some of our apps we encounter unresolved deadlocks and for such cases we find that our collection job using this


   @destination_table VARCHAR(4000)

SET @destination_table = 'ourdb.dbo.ourcollectiontable'

EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 0,

         @find_block_leaders = 1, @DESTINATION_TABLE = @destination_table ;

results in an error mentioning maxrecursion and cannot insert null into one of the columns. Just wondering if you're aware of this and if this is fixed in the latest version.

Thanks again.

October 24, 2016 5:46 AM

Adam Machanic said:


Yes, the recursion bug is fixed.

October 24, 2016 7:55 AM

John U said:

Hey Adam,thanks for a pretty cool tool. I'm currently loading sp_whoisactive to all my SQL boxes at my new job and on one SQL Server 2008 instance the procedure will not load to 'master' DB, but will to individual DBs. I get what appear to be false errors. See below: Can you tell me what could be the problem. Runs well on the other boxes.


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

Incorrect syntax near '.'.

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

Incorrect syntax near the keyword 'CASE'.

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

Incorrect syntax near the keyword 'CASE'.

October 24, 2016 1:03 PM

Costa said:

Hi, Adam:

Do you have the create table statement that creates the destination table with all the columns that the procedure can insert?


October 24, 2016 2:24 PM

Adam Machanic said:

@John U:

Those are real errors! What is the compatibility mode of the master database? I'm guessing this was at some point a 2000 server that was upgraded and it's still in 2000 compatibility mode. Turn it up to 2008 and it should fix the problem. If that's not it, let me know.


Nope, that's something the proc generates for you. See:

October 24, 2016 2:54 PM

Costa said:

@Adam: Thanks for the quick response. I also figured it out after I posted the message and looking at the code.

Other things to mention:

- the create table statement depends on the parameters selected, i.e. the columns included depend on the different parameters. For instance, if you choose @format_output = 1 vs @format_output = 0 you get different column types. You might also get more or less columns depending on other fields. The bottom line is that you have careful, because if you change any parameters, you may have to regenerate the create table statement.

- the order of the columns in the @output_column_list matters, they have to match the order in the table otherwise it doesn't work. Found out this the hard way.

Anyway, thank you so much for this code!


October 24, 2016 3:51 PM

Adam Machanic said:


Yes, that is precisely why the stored proc scripts its own output table. So that you can collect what YOU want, the way YOU want to collect it, and it can be totally different from whatever work I'm doing, or even whatever work some other DBA you're working with is doing on the exact same server. Ultimate flexibility :-)


October 24, 2016 4:16 PM

John U said:

Wow! You are correct, it was an old 2000 box.



October 25, 2016 2:15 PM

Alberto Castillo said:

Thank you Adam

November 4, 2016 9:08 PM

Alain Martin said:


Thanks for the update!

Could you add context_info column?

Thanks again

November 8, 2016 1:34 PM

Eric Stephani said:

Looking forward to the next version.

December 5, 2016 5:11 PM

Evans said:

Is it possible to get Host IP instead of Host Name?

December 16, 2016 4:43 AM

Adam Machanic said:

@Alain and @Evans

Yes on both. Added to the backlog.


December 16, 2016 12:22 PM

Vladimir said:

Hi, Adam!

I use your sp. It is a very useful procedure.

I need you to help me. I need to save procedure's work results into a table. I want to use SQL Server Agent job but if I use @destination_table = 'DBName.dbo.TableName' I recieve error message. What do I need to do else?

Thank you for your help.  

December 23, 2016 5:30 AM

Vladimir said:

I understood my misstake. At first I need to create a table

December 23, 2016 7:41 AM

Donn Edwards said:

Thank you for helping me track down some problems on my 2008R2 server. sp_whoisactive is the most useful SQL Server tool I have. Thank you!

I have noticed some queries get stuck and never finish, usually when there is a problem on the user's computer. sp_whoisactive can show me these queries and I kill them off and see what is wrong on the workstation.

Is there a way of automatically (logging and) killing of queries that, say, have been running for over 20 minutes?

Please forgive me if this has been asked and answered before.

December 29, 2016 12:12 PM

Adam Machanic said:


Glad it's been helpful!

Yes, there are ways to do that, but I wouldn't know where to point you as I don't think there are many generalized solutions for that kind of thing. It's something I would consider to be a "worst practice" - i.e. it would be something I'd only do as a very, very last resort. Much better to fix the application, if at all possible, so that it can keep track of itself.


December 30, 2016 11:47 AM

Andrew Hill said:


running your very useful script on a server where the secondary half of an always on availability group is "Not Synchronising / Suspect" throws an error -

Msg 926, Level 14, State 3, Line 38

Database 'blah_AU_PRD' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

even though there are a bunch of other databases present and synchronised.

(the setup we have is 2 servers where the A node is primary on some db's the B node is primary on others, and they can both failover to each other; this is run on the B node, where the A->B replication has failed, but the B->A is fine)

January 3, 2017 5:06 PM

Adam Machanic said:


Interesting. It looks like you're not passing any parameters to the proc. Has someone changed the defaults? I believe it shouldn't try to open a database unless either @get_locks or @get_additional_info are set. (The latter, only in conjunction with @get_task_info = 2.) I'll have to double-check the code for that. Can you confirm that neither of these are turned on?


January 3, 2017 5:38 PM

Donn Edwards said:


Thanks for the sane advice. I guess I was being too reckless ;-)

January 4, 2017 7:29 AM

Mark Hayter said:

Hi Adam

I think I'm seeing a bug in the latest version...I just ran it and there is a session with a login_time of 09:36 today, but the time taken dd hh:mm:ss.mss is showing as 61 days and the start_time showing as 5th Nov (definitely not true as this query started today).

Running sp_BlitzWho shows it as correct (runSecs 2999). start time 2017-01-06 09:36:22.577

Has anyone else reported this issue?

January 6, 2017 5:26 AM

Adam Machanic said:

Hi Mark,

Is Nov 5 the last time you re-started the instance? (You can check the sqlserver_start_time column of sys.dm_os_sys_info.)

What kind of query is it? There are some situations where SQL Server reports startup time in these columns, especially for system queries, but you're the second person who has reported that something looks amiss, so perhaps I need to take a better look.

It looks like sp_BlitzWho is using the same basic columns as sp_whoisactive, though in a different way, so it's tough to say who is right or wrong.


January 6, 2017 10:54 AM

Tony Green said:

Hi Adam,

I've been using the sp_whoisactive for quite some time and one "feature" I think that is missing is the ability to sort by program_name.  Please consider adding this functionality soon?

January 12, 2017 12:25 PM

Adam Machanic said:


You can do that today.

EXEC sp_whoisactive @sort_order = '[program_name]'



January 12, 2017 12:39 PM

Tony Green said:

I just tried your suggestion with the latest version and it still does not sort by program name.  Here is my output from the program_name column:

Microsoft SQL Server Management Studio - Query

ULoad - ULoad (1)


SQLAgent - TSQL JobStep (Job 0xF2810FD6299DBB418EC612F878781A7A : Step 1)


SQLAgent - TSQL JobStep (Job 0x42F7014081D4DA40A4B88165FE2B2BA7 : Step 1)

Microsoft SQL Server Management Studio - Query


January 12, 2017 3:11 PM

Adam Machanic said:


You're right -- I just checked the code and program_name is not supported. I have no clue why it wouldn't be. Must have been on oversight on my part. I've already implemented it on this end and will have it ready for the next release. In the meantime if you like, feel free to drop me a line (use @help to get my email address) and I'll send you a test version so you can confirm that it works.


January 12, 2017 4:16 PM

M said:

Thanks for the great procedure! I use it all the time. If open_tran_count is > 0 and a SPID is sleeping, this usually indicates an unhandled exception on the application side, right? We have an application that will often have an open_tran for days while sleeping.

February 17, 2017 11:17 AM

Adam Machanic said:


An exception? Not necessarily, no. A lot of apps, especially Java-based, will connect and start a transaction right away. I'm not sure why; maybe something historical with JDBC. Anyway, in those cases you'll see that same behavior but it's not indicative of a problem.

What you might want to do is try @get_transaction_info = 1. If you see a sleeping session with open transactions and the [tran_log_writes] column reports activity, then that might be a real problem.


February 17, 2017 11:31 AM

M said:

@Adam Machanic

Using @get_transaction_info = 1, I get the following in the tran_log_writes column: master: 0 (0 kB),DBNAME_A: 0 (0 kB),DBNAME_B: 0 (0 kB)

This is indeed from a Java application. There appears to be little to no activity. CPU:0  Reads:200  Writes: 0  blocked_session_id: NULL  blocked_session_count: 0

Are you saying that it's possible that the app is opening a transaction for no reason, and just waiting to potentially do some querying before committing the transaction? Seems dangerous, but we have seen a lot of interesting things from  Java apps in our environment so it wouldn't surprise me!

February 17, 2017 12:18 PM

Adam Machanic said:


Yup, that's exactly what I'm saying. And you can see based on that output that it's touched the databases but hasn't written anything -- so it won't cause any blocking. Totally safe to leave sitting around, even though it's maddening that they code that way.


February 17, 2017 5:27 PM

James DiBernardo said:

@Adam So I stumbled across your amazing tool, and i'm so glad I did! Such dedication I cant thank you enough.

April 14, 2017 2:38 PM

Vladimir said:

Thank you for this useful procedure. It helps me to look for bottlenecks of T-SQL queries.

May 3, 2017 10:21 AM

BB said:

is there any reason this does not capture adhoc queries e.g. when running the query per below:

with cte_getTop10memqrys



SELECT TOP 10 replace(replace(replace(SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset


ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),char(10),''),char(13),''),char(9),'') as QueryText,

db_name(qt.dbid) databasename,


qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,



qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,



FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC -- logical reads

-- ORDER BY qs.total_logical_writes DESC -- logical writes

-- ORDER BY qs.total_worker_time DESC -- CPU time


i get a number of adhoc queries which do not appear when running whoisactive. is this because there is no database context

May 17, 2017 1:57 AM

Adam Machanic said:


It worked fine on this end. Are you running the most recent version?

You can get it from


May 17, 2017 9:41 AM

Vladimir Arzamazov said:

Hi, Adam! Will you do a new procedure's version for SQL Server 2017? I see that current version is 11.17

June 2, 2017 3:55 AM

Adam Machanic said:


I'm not sure what the next release will target. The current release works just fine in 2017, and there's not a lot of reason to update it.

I was going to write a new version a few years back, as soon as they finally got rid of the deprecated sysprocesses view...but it's still there.

And then I was going to do one that supported the new 2016 session waits DMV, but it turns out that it's totally unreliable, so no reason to do that.

I could integrate a bit more memory information that's now available in more recent releases, but that stuff is not especially useful in 99.9% of cases.

This leaves very little practical information, from my perspective, that 11.17 doesn't already do or support. The only thing that comes to mind that I've missed recently is the Resource Governor workload group per request.

Is there something in particular that you're looking for?


June 4, 2017 11:15 AM

Krish M said:

Hi Adam,

First of all thanks for such a great tool. Using the latest version.

I am experiencing an issue which was mentioned/reported earlier, the column

'dd hh:mm:ss.mss' is showing a value 25+ days which is the date the server was rebooted.

This is happening when I try to run queries from Entity Framework (Microsoft ORM framework). Doesn't happen for all the queries though.

June 19, 2017 11:35 PM

Adam Machanic said:


I assume you're seeing request_id > 0 for those? I'm guessing you have MARS enabled on the connection string, which should not be necessary for EF (and probably not necessary for much else -- it's a feature used by almost no one). Assuming that my assumption-based guess is correct, the solution is to simply change the connection string and move on with life. The bad time is a DMV reporting issue and I haven't found a workaround.

If I'm wrong about the scenario please share some more information and I'll see what I can do!


June 20, 2017 11:09 AM

Krish M said:


You're right! the request_id is > 0 and MARS is enabled.

Thanks for the quick response.

June 20, 2017 11:45 AM

BB said:

is there some reason this (wonderful) tool doesn't pick up all queries or seems to ignore ad hoc / dynamic sql. I have a highly transacted database that runs a heap of dynamic sql that is pulled out of sql tables and composed and then sent to the db from a (very legacy and badly written) .NET front end. for some reason even when running sp_whoisactive with no parameters and no filters doesn't seem to pick these up.

I can see these qureies fine using Extended events 'statement_completed' action btw.

i have seen this behaviour in the past but cannot figure out why this happens, any help or tips much appreciated.

As stated I am running with no filters or parameters just to check the output.

August 23, 2017 6:35 PM

Adam Machanic said:


How quickly are these statements running? 

I've definitely seen cases with very fast queries -- on the order of 1-10ms -- where the DMVs simply don't show them.

It's easy to repro. Grab SQLQueryStress or your favorite load simulation tool, fire up 200 threads, each running "SELECT TOP(1) * FROM sys.databases" 20000 times, and watch sys.dm_exec_requests. (Filter for: "session_id <> @@spid AND status IN ('running', 'suspended')".) Sometimes you'll capture two or three queries running, sometimes only one, but most often none at all. There are 200 threads running queries but the DMV simply doesn't reliably show them.

sp_whoisactive, being a DMV consumer, can only report whatever the DMVs report -- so in this case, unfortunately, I don't think there's anything I can do for you.

Correct me if your workload is different than what I described. If so please give me as much information as possible so I can attempt to debug. I'd love to improve things!


August 28, 2017 7:12 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement