THE SQL Server Blog Spot on the Web

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

Adam Machanic

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

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

Enjoy! 

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

Comments

 

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:

Thanks

You ate the best....

Pini

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:

@Muthukkumaran

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.

--Adam

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

DECLARE

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

@LondonDBA

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.

thanks

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?

Thanks

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.

@Costa

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

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/25/capturing-the-output-a-month-of-activity-monitoring-part-25-of-30.aspx

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!

Costa

October 24, 2016 3:51 PM
 

Adam Machanic said:

@Costa

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

--Adam

October 24, 2016 4:16 PM
 

John U said:

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

Thanks

John

October 25, 2016 2:15 PM
 

Alberto Castillo said:

Thank you Adam

November 4, 2016 9:08 PM
 

Alain Martin said:

Hello,

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.

--Adam

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:

@Donn

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.

--Adam

December 30, 2016 11:47 AM
 

Andrew Hill said:

@Adam,

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.

http://imgur.com/a/07aPK

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:

@Andrew

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?

--Adam

January 3, 2017 5:38 PM
 

Donn Edwards said:

@Adam

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.

--Adam

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:

@Tony

You can do that today.

EXEC sp_whoisactive @sort_order = '[program_name]'

Reference:

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/24/the-output-of-your-dreams-a-month-of-activity-monitoring-part-24-of-30.aspx

--Adam

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)

SQLQueryStress

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

SQLCMD

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

Microsoft SQL Server Management Studio - Query

DataLoad

January 12, 2017 3:11 PM
 

Adam Machanic said:

@Tony

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.

--Adam

January 12, 2017 4:16 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

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

This Blog

Syndication

Privacy Statement