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.

Who is Active? v8.40 - Now With Delta Power!

NOTE: v8.40 is outdated at this time. Please try v9.57, which you can find here.

 

It has been only a month and a half since I posted the last version of Who is Active? (v7.30), but in that time I've made a huge number of changes, fixes, and enhancements. The new version, v8.40, is faster, more robust, and includes a few exciting features.

Click here to download Who is Active? v8.40

 

Following is a list of some of the things I've done with it, in no particular order, along with descriptions where applicable.

 

Added a collection interval option, @DELTA_INTERVAL. This option causes the script to collect numeric metrics -- such as reads, writes, and context switches -- twice. The script will wait between collections, based on the duration passed into the parameter (in seconds). The script will then calculate the differences (deltas), between the first and second collections, for any requests that were active for each collection. These deltas will be output in a new set of columns, reads_delta, writes_delta, context_switches_delta, etc.

For example, to use the default options and wait two seconds between collections, you would use the following:

EXEC sp_WhoIsActive @DELTA_INTERVAL = 2;

 

If you played with Who is Active? in the past you know that the script outputs a lot of data in a lot of different columns. I started feeling that the column list was getting unwieldy, so I implemented dynamic column lists as a fix. There are two changes here. First of all, the default column list will only contain columns associated with those features you have turned on. So for example, if you don't have @GET_LOCKS enabled, the locks column won't appear.

The second part of this fix is a new feature to make things even more dynamic: custom column lists. I sent a slightly earlier test version to Jimmy May, and his response was something along the lines of, "it's pretty good, but I really don't like the column order." To make Jimmy happy, I implemented the @OUTPUT_COLUMN_LIST option. To use this option, simply pass in whatever columns you would like to see, in whatever order you would like to see them, and the script will do the rest. Note that the output will be the intersection of whatever options are enabled and whatever columns are found in the list.

Here's an example I've been using in my own work recently:

EXEC sp_WhoIsActive
    @OUTPUT_COLUMN_LIST =
        '[login_name],[dd hh:mm:ss.mss],[sql_text],[reads],[writes],
        [context_switches],[physical_io],[wait_info],[blocking_session_id],
        [tempdb_writes],[tran_log_writes],[query_plan],[session_id]';

 

Another feature I added that makes things a bit more dynamic and customizable is the ability to sort the output based on whatever column and direction you would like. Interested in seeing the requests that are currently blocked? No need to scroll, just ask for them to sort high:

EXEC sp_WhoIsActive
    @SORT_COLUMN = '[blocking_session_id]',
    @SORT_COLUMN_DIRECTION = 'DESC';

 

While viewing the results on the screen in SSMS is great, sometimes I like to capture the results to a table for later analysis. So I added a few features in this most recent version to support automated collection.

To begin with, I realized that the default, nicely-formatted output of the script, while great for on-screen viewing, isn't good if you want to do your own analysis later. So I added a new option, @FORMAT_OUTPUT, that lets you control whether the output is or is not formatted. The default value for the parameter is 1, keeping with the previous behavior.

I also figured that if you want to do some later analysis it might be nice to know what time period you're working with. So I added a column called collection_time, which outputs a datetime instance representing the time that the script finished running.

Taking things one step further, I decided that with all of the dynamic column options it would be a pain to put together a table matching the output schema if you start playing with different settings. So I made things easy; no need to figure out what the output looks like; the script will write a CREATE TABLE statement for you. Simply enable the @RETURN_SCHEMA option and collect the output value from the @SCHEMA parameter.

Finally, due to nested INSERT/EXEC limitations, it's impossible to insert the result into a table when calling the stored procedure. No worries, the script will insert its output into a table for you. Just tell it where to send the data, via the @DESTINATION_TABLE option.

Here's an example of asking for the unformatted output schema, creating a destination table based on the current day, and collecting data 10 times, waiting 15 seconds between each collection:

DECLARE @destination_table VARCHAR(4000);
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112);

DECLARE @schema VARCHAR(4000);
EXEC sp_WhoIsActive
    @FORMAT_OUTPUT = 0,
    @RETURN_SCHEMA = 1,
    @SCHEMA = @schema OUTPUT;

SET @schema =
    REPLACE
    (
        @schema,
        '<table_name>',
        @destination_table
    );

EXEC(@schema);

DECLARE @i INT;
SET @i = 0;

WHILE @i < 10
BEGIN;
    EXEC sp_WhoIsActive
        @FORMAT_OUTPUT = 0,
        @DESTINATION_TABLE = @destination_table;
   
    SET @i = @i + 1;
   
    WAITFOR DELAY '00:00:15'
END;
GO

 

One of the key reasons I created the original version of Who is Active? was to help identify blocked and blocking SPIDs. And I thought it worked pretty well for that purpose until recently, when I discovered that sys.dm_exec_requests fails to properly identify blocking when queries go parallel. Oops. Fixed in the new version; it now uses the waiting tasks DMV for this purpose, instead of the requests DMV.

As part of this process I discovered that the joins to the tasks DMVs were slightly flawed in v7.30, so I fixed them up. And I was able to play some games to improve performance--enough that I decided to eliminate the @TASK_INFO option, making this the default behavior. This means that you'll always get correct blocker identification from Who is Active? v.8.40, along with a few bonus task-based metrics including physical I/O stats and the number of context switches.

 

Interested in information about transactions? So are most DBAs, which is why I promoted the transaction start time, which was previously embedded in the tran_log_writes column, to a top-level column of its own. This will still populate only with transactions that have actually done a write, but from what my testers tell me this is okay behavior. Let me know if you disagree. While I was in there I played even more performance games, and greatly improved the speed of the query when the @GET_TRANSACTION_INFO option is enabled.

 

A few smaller fixes are also worth mentioning here. First of all, I tested the script against a case-sensitive instance, and fixed a few issues there. So it should work for you no matter what collation you're using. Second, thanks to a discussion I had with Roman Nowak, I was able to finally solve the entitization problem that plagued the "clickable" query output produced by the script. Third, I put on my security hat and added some code to verify input arguments, as well as did a review of all of the dynamic SQL used by the script to make sure that it is not injectable.

 

Thank you to everyone who sent me feedback and/or feature requests! If you have an idea for th script, please make sure to send it my way. I have quite a bit of momentum at the moment and want to keep going and see just how far I can take this thing. I can only do this with your help.

Enjoy!

Click here to download Who is Active? v8.40

Published Monday, March 30, 2009 1:41 PM by Adam Machanic

Attachment(s): who_is_active_v8_40.zip

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

 

Uri Dimant said:

Hi Adam

First of all I would like to thank you for this great stored procedure, well done. I have a question. You mentioned that it was to help identify blocked and blocking SPIDs so I have tried recently the simple script to identify  blocking.

One con has

begin tran

update tbl set col='blbla'

Sec.con

select * from tbl

Then I ran you script and see under'blocking session id' column  the second statement (select * from tbl), should not it be as BLOCKED (waiting)statement and UPDATE as a blocking one? And I do not see blocked session column, so I hope that I am missing something , can you please clarify?

March 31, 2009 12:57 AM
 

Adam Machanic said:

Hi Uri,

This works on my end:

(SQL Server 2005 AW -- please share actual code if it's not working for you):

--window #1--

use adventureworks

go

begin tran

update production.product

set name = name

go

--/window #1--

--window #2--

use adventureworks

go

select * from production.product

go

--/window #2--

--window #3--

exec sp_whoisactive

@output_column_list = '[session_id] [blocking_session_id] [sql_text]'

go

--/window #3--

... or another option, for window #3, if you want to see the sleeping SPID that's doing the blocking:

--window #3 (alternate)--

exec sp_whoisactive

@get_transaction_info = 1,

@output_column_list = '[session_id] [blocking_session_id] [sql_text]'

go

--/window #3 (alternate)--

Does this make sense and is it working fine on your end?

March 31, 2009 8:24 AM
 

Uri Dimant said:

Hi Adam

begin tran

update production.product

set name = name

The above does no create blocking :-)))))

begin tran

update production.product

set name ='T'+ name--- THAT does

But again ,my point was that SELECT query is not BLOCKING but it is WAITING (blocked) and I was expected to see blocked AND bloking statements (in our case UPDATE and SELECT)

exec sp_whoisactive

@output_column_list = '[session_id] [blocking_session_id] [sql_text]'

<?query --

select * from Production.Product

--?>

What do you think?

April 1, 2009 12:25 AM
 

Adam Machanic said:

Hi Uri,

The script shows blocking session ID.  It's up to the user to look at the blocking session ID to find out what it is doing.  This was a decision I made to help improve performance and usability.  The script returns all of the data you need; you just need to do a bit of scrolling.  And as I mentioned before, you need to enable the @GET_TRANSACTION_INFO option if you want to see sleeping SPIDs with open transactions.

April 1, 2009 8:26 AM
 

Adam Machanic said:

By the way, the script I posted does indeed create blocking on my end.  Did you test it and not see blocking?  If so, what version are you testing against?

April 1, 2009 8:32 AM
 

Uri Dimant said:

Hi Adam

Yes, sure, (btw I have been recomending your sp for many folks in newsgroup) , it would be nice to have blocking and waiting sessions as I can see here (without sleeping sessions), sorry for this long script. And yes,  your script did not created blocking on my end..

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

-- Detailed blocking information with query information

SELECT

owt.session_id AS waiting_session_id,

   owt.blocking_session_id,

DB_NAME(tls.resource_database_id) AS database_name,

   (SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,

(CASE WHEN ers.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2

ELSE ers.statement_end_offset

END

- ers.statement_start_offset

) / 2)

FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,

CASE WHEN owt.blocking_session_id > 0

THEN (

SELECT

est.[text] FROM sys.sysprocesses AS sp

CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est

WHERE sp.spid = owt.blocking_session_id)

ELSE

NULL

END AS blocking_query_text,

   (CASE tls.resource_type

WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)

WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)

ELSE (SELECT  OBJECT_NAME(pat.[object_id], tls.resource_database_id)

FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)

END

) AS object_name,

owt.wait_duration_ms,

owt.waiting_task_address,

owt.wait_type,

tls.resource_associated_entity_id,

tls.resource_description AS local_resource_description,

tls.resource_type,

tls.request_mode,

tls.request_type,

tls.request_session_id,

owt.resource_description AS blocking_resource_description,

qp.query_plan AS waiting_query_plan

FROM sys.dm_tran_locks AS tls

INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address

INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id

OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp

GO

April 1, 2009 8:57 AM
 

Uri Dimant said:

Uh Adam, sorry, I got the info I was asking for , (it is time to go home). I have not enabled @GET_TRANSACTION_INFO=1

exec sp_whoisactive @GET_TRANSACTION_INFO=1 ,

@output_column_list = '[session_id] [blocking_session_id] [sql_text]'

Now, testing your script (where name=name) I get blocking session ID is NULL and I see the UPDATE statement

April 1, 2009 9:06 AM
 

Adam Machanic said:

Hi Uri,

I'm considering having the script show, by default, all active SPIDs as well as sleeping SPIDs with open transactions.  I guess that would eliminate the confusion and remain true to the central goal of the script, which is to not show a bunch of garbage (a la sp_who).  It still wouldn't show the data in one row, but that's not something I'm willing to do at this point due to performance and blocking issues...

April 1, 2009 10:10 AM
 

Michelle Ufford said:

Adam, this is awesome!  The only change I made was to move the login_name and host_name further to the left, so it's faster to find out who I need to holler at.  ;)

Thanks for your work on this, it's really an excellent script.  

April 10, 2009 12:52 PM
 

Adam Machanic said:

Ever tried writing a more or less database-agnostic administrative script? If the experience didn't make

April 16, 2009 11:17 AM
 

Adam Machanic said:

Please respond and let me know: A) Whether you change it at all and B) If so, whether you use a fixed

April 24, 2009 4:34 PM
 

Derek Frye said:

Thanks for writing this, it works well.

But now I know that sys.dm_exec_query_memory_grants doesn't exist on 2005 servers prior to SP1. Commenting out all of the references to it and "used_memory" allow it to function on those instances.

May 1, 2009 2:43 PM
 

Daniel said:

Hi,

Well done Adam!

I'm wondering it is possible add some extra filters like dbname, hostname, loginnme, program_name or even sql_test, is it?

May 18, 2009 5:28 PM
 

Adam Machanic said:

Hi Daniel,

Anything is possible :-) ... I've been thinking about some of these for a while but now that I have an actual request I'll bump them up in the queue.  Watch for them in the next version.  By the way, have you seen the current "beta build"?  

http://sqlblog.com/files/folders/beta/entry13855.aspx

May 19, 2009 10:15 AM
 

Daniel said:

Hi Adam,

Thank you, I'm really looking forward to new "stable" version.

No, I haven't. I didn't know you write beta builds, I'll tray it today.

By the way how long is your queue :-) ?

Regards

May 19, 2009 4:48 PM
 

Scott_Lotus said:

Excellent procedure Adam, and thanks to Uri for pointing me in this direction.

July 8, 2009 5:05 AM
 

Adam Machanic : Who is Active? v8.82 - Harder, Better, Faster, Stronger said:

August 20, 2009 2:14 PM
 

Ludwig said:

Dear Adam,

i've downloaded your script and tried to get it working in order to hunt down some cpu hogs.

However if I execute it, I don't get any results.

Anything I might have missed? Using SQL Server 2005

November 2, 2009 2:58 AM
 

Adam Machanic said:

Hi Ludwig,

Hard to say... There is something running, right? :-) You might want to try the most recent version and see if that works better for you:

http://sqlblog.com/files/folders/beta/entry18386.aspx

November 3, 2009 7:11 PM
 

Ludwig said:

Hi,

thanks for your feedback. Yes, something is definitly running (activity monitor also shows the activity), however the most recent version produces no output either. Any pitfalls I may have overseen?

Thans,

Ludwig

November 4, 2009 10:47 AM
 

Adam Machanic said:

Hi Ludwig,

That is really odd. Are you certain the SPIDs are active? Try setting @get_sleeping_spids = 2 in the newest version and see if that returns something. Let me know, either way! I would like to understand what's happening here.

Thanks!

November 4, 2009 1:08 PM
 

Ludwig said:

Hi Adam,

sorry it took me so long to reply.

With @get_sleeping_spids=2 I get some results however not the active ones. We have found the root cause for the problem btw for our performance issues, which was a bug in the VM Ware infrastructure we are using - all i/o got delayed and the clocks where not updated. So it may be that the active spids are only short-time running from a systems perspective but where long-running from a global perspective- sorry, this sounds confusing. I will try again, when we have huge load on the server and get back to you.

Best regards,

Ludwig

November 9, 2009 5:01 AM
 

Adam Machanic said:

Hi Ludwig,

Interesting, thanks for sharing. Who is Active does use start times in some of its logic so if these are off it could cause it to misfire. But I haven't heard of this happening anywhere else yet. Will be quite curious to hear about what happens when you re-test.

November 9, 2009 9:07 PM
 

Adam Machanic said:

Ludwig: If you're still paying attention to this thread, please drop me a line at amachanic [at] gmail [dot] com. I would like to send you a new version of the proc to try out on your end.

November 17, 2009 4:25 PM
 

Shimon said:

Hi Adam!

Thanks for the great feature.

I have a question.

If there a way  to find a real Windows account if a  user connects to the database thru the service account used by the application.

If not always is it possible in PeopleSoft application that runs on Sql Server.

August 5, 2010 4:38 PM
 

toplum said:

sure hope inplace will be possible. lots of customers want that.

August 21, 2010 9:25 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

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

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement