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

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

Uri Dimant — Tue, 31 Mar 2009 04:57:21 GMT

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?

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

Adam Machanic — Tue, 31 Mar 2009 12:24:28 GMT

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

begin tran

update production.product

set name = name

--/window #1--

--window #2--

use adventureworks

select * from production.product

--/window #2--

--window #3--

exec sp_whoisactive

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

--/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]'

--/window #3 (alternate)--

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

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

Uri Dimant — Wed, 01 Apr 2009 04:25:48 GMT

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?

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

Adam Machanic — Wed, 01 Apr 2009 12:26:35 GMT

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.

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

Adam Machanic — Wed, 01 Apr 2009 12:32:02 GMT

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?

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

Uri Dimant — Wed, 01 Apr 2009 12:57:18 GMT

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

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

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

Uri Dimant — Wed, 01 Apr 2009 13:06:35 GMT

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

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

Adam Machanic — Wed, 01 Apr 2009 14:10:45 GMT

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

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

Michelle Ufford — Fri, 10 Apr 2009 16:52:45 GMT

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.

Connect Item: Globally-Scoped Metadata Views

Adam Machanic — Thu, 16 Apr 2009 15:17:06 GMT

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

Do You Change the Results to Grid Font in SSMS?

Adam Machanic — Fri, 24 Apr 2009 20:34:47 GMT

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

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

Derek Frye — Fri, 01 May 2009 18:43:23 GMT

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.

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

Daniel — Mon, 18 May 2009 21:28:35 GMT

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?

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

Adam Machanic — Tue, 19 May 2009 14:15:34 GMT

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

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

Daniel — Tue, 19 May 2009 20:48:37 GMT

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

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

Scott_Lotus — Wed, 08 Jul 2009 09:05:56 GMT

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

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

Adam Machanic : Who is Active? v8.82 - Harder, Better, Faster, Stronger — Thu, 20 Aug 2009 18:14:06 GMT

PingBack from http://sqlblog.com/blogs/adam_machanic/archive/2009/08/20/who-is-active-v8-82-harder-better-faster-stronger.aspx

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

Ludwig — Mon, 02 Nov 2009 07:58:35 GMT

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

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

Adam Machanic — Wed, 04 Nov 2009 00:11:54 GMT

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

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

Ludwig — Wed, 04 Nov 2009 15:47:16 GMT

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

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

Adam Machanic — Wed, 04 Nov 2009 18:08:06 GMT

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!

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

Ludwig — Mon, 09 Nov 2009 10:01:28 GMT

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

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

Adam Machanic — Tue, 10 Nov 2009 02:07:57 GMT

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.

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

Adam Machanic — Tue, 17 Nov 2009 21:25:54 GMT

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.

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

Shimon — Thu, 05 Aug 2010 20:38:27 GMT

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.

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

toplum — Sat, 21 Aug 2010 13:25:07 GMT

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