A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Thomas Williams — Wed, 02 Jan 2008 04:47:13 GMT

G'day Adam, this is a handly script - the only thing I would add is the option to ignore whoever's running the script (unless they were blocking or being blocked, maybe) and the database name.

Cheers, and Happy New Year,

Thomas

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

jerryhung — Wed, 02 Jan 2008 15:17:28 GMT

Great script, thank you

I had my own modified sp_who2 to return data I want, but not as detailed as yours

I added in the db_name (the only way I know how, if there's a better way feel free)

SELECT

x.session_id,

x.host_name,

x.login_name,

x.start_time,

x.totalReads,

x.totalWrites,

x.totalCPU,

x.writes_in_tempdb,

x.dbname, -- new addition by Jerry

(

SELECT

text AS [text()]

FROM sys.dm_exec_sql_text(x.sql_handle)

FOR XML PATH(''), TYPE

) AS sql_text,

COALESCE(x.blocking_session_id, 0) AS blocking_session_id,

(

SELECT

p.text

FROM

(

SELECT

MIN(sql_handle) AS sql_handle

FROM sys.dm_exec_requests r2

WHERE

r2.session_id = x.blocking_session_id

) AS r_blocking

CROSS APPLY

(

SELECT

text AS [text()]

FROM sys.dm_exec_sql_text(r_blocking.sql_handle)

FOR XML PATH(''), TYPE

) p (text)

) AS blocking_text

FROM

(

SELECT

r.session_id,

s.host_name,

s.login_name,

r.start_time,

r.sql_handle,

r.blocking_session_id,

dbname = DB_NAME(r.database_id), -- new addition by Jerry

SUM(r.reads) AS totalReads,

SUM(r.writes) AS totalWrites,

SUM(r.cpu_time) AS totalCPU,

SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb

FROM sys.dm_exec_requests r -- select * FROM sys.dm_exec_requests

JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id

JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id

WHERE r.status IN ('running', 'runnable', 'suspended')

GROUP BY

r.session_id,

s.host_name,

s.login_name,

r.start_time,

r.sql_handle,

r.blocking_session_id

,DB_NAME(r.database_id) -- new addition by Jerry

) x

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Ranga — Wed, 02 Jan 2008 16:08:08 GMT

Excellent script...Thanks.

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Vamsi — Wed, 02 Jan 2008 16:32:15 GMT

Handy script especially when we have sp_who2 returning > 1000 rows.

Added for xml to my script, good tip to get the text back as a link. Command type << sys.dm_exec_requests.Command>>would be helpful too..

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Aaron Bertrand — Wed, 02 Jan 2008 17:44:38 GMT

This might be useful to people too; I created a more flexible version of sp_who2 in September 2006, which I've been meaning to go back and update (because the version I actually use has been enhanced a bit). But still worth a look IMHO:

http://sqlserver2005.databases.aspfaq.com/better-sp-who2.html

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

aaron — Sat, 05 Jan 2008 18:46:43 GMT

If you put the text into a comment node rather then a text node entities will not be escaped. e.g.

SELECT

text AS [comment()]

FROM sys.dm_exec_sql_text(x.sql_handle)

FOR XML PATH(''), TYPE

You will have to deal with the xml comment wrappers () around the text but they will always be in the same place.

Hope that helps.

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Kevin Boles — Sun, 06 Jan 2008 16:53:08 GMT

In one spid I did this (pubs, sql2005):

BEGIN TRAN

update authors set au_lname = 'asdf'

In a second spid I did this:

select * from authors

Running your query did not show the blocking spid nor (and more importantly) did it show the blocking sql query's text (although it did show that it was spid NN doing the blocking). Any way we can get it to pick up that blocking spid's text even though said spid is in a SLEEPING state?

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Adam Machanic — Sun, 06 Jan 2008 19:38:24 GMT

Thanks, all, for the great comments! Responses inline:

Thomas W: Add an outer WHERE clause: "WHERE x.SessionId <> @@SPID"

JerryHung: Great addition.

Arron B: Awesome!

Aaron <no last name>: Thanks, that's great!!

Kevin: Great feedback. How about if we change the inner WHERE clause to:

"WHERE r.status IN ('running', 'runnable', 'suspended') OR r.blocking_session_id IS NOT NULL"

Any other situations in which a sleeping session might be interesting to see?

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Javier Villegas — Wed, 09 Jan 2008 20:56:31 GMT

Very useful script!!!! Thanks a lot!. I modified it a little bit to show just the T-SQL statement (in xml format) instead of the whole procedure. Also totalElapsedTime , db name and object name are now included and current spid was excluded.

SELECT

x.session_id,

COALESCE(x.blocking_session_id, 0) AS blocking_session_id,

x.Status,

x.TotalCPU,

x.Start_time,

x.totalElapsedTime,

x.totalReads,

x.totalWrites,

x.Writes_in_tempdb,

( SELECT substring(text,x.statement_start_offset/2,

(case when x.statement_end_offset = -1

then len(convert(nvarchar(max), text)) * 2

else x.statement_end_offset end - x.statement_start_offset)/2)

FROM sys.dm_exec_sql_text(x.sql_handle)

FOR XML PATH(''), TYPE ) AS text,

db_name(x.database_id) as DBName ,

(SELECT object_name(objectid) FROM sys.dm_exec_sql_text(x.sql_handle)) as ObjName,

x.Wait_type,

x.Login_name,

x.Host_name,

( SELECT

p.text

FROM

(

SELECT

MIN(sql_handle) AS sql_handle

FROM sys.dm_exec_requests r2

WHERE

r2.session_id = x.blocking_session_id

) AS r_blocking

CROSS APPLY

(

SELECT substring(text,x.statement_start_offset/2,

(case when x.statement_end_offset = -1

then len(convert(nvarchar(max), text)) * 2

else x.statement_end_offset end - x.statement_start_offset)/2)

FROM sys.dm_exec_sql_text(r_blocking.sql_handle)

FOR XML PATH(''), TYPE

) p (text)

) AS Blocking_text

FROM

( SELECT

r.session_id,

s.host_name,

s.login_name,

r.start_time,

r.sql_handle,

r.database_id,

r.blocking_session_id,

r.wait_type,

r.status,

r.statement_start_offset,

r.statement_end_offset,

SUM(r.total_elapsed_time) as totalElapsedTime,

SUM(r.reads) AS totalReads,

SUM(r.writes) AS totalWrites,

SUM(r.cpu_time) AS totalCPU,

SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb

FROM sys.dm_exec_requests r

JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id

JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id

WHERE r.status IN ('running', 'runnable', 'suspended')

GROUP BY

r.session_id,

s.host_name,

s.login_name,

r.start_time,

r.sql_handle,

r.database_id,

r.blocking_session_id,

r.wait_type,

r.status,

r.statement_start_offset,

r.statement_end_offset

) x

where x.session_id <> @@spid

order by x.totalCPU desc

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Pardini — Fri, 11 Jan 2008 20:14:33 GMT

This is very interesting. I ran your query and compared it to SQL Management Studio's Activity Monitor, when running an ALTER TABLE statement. AM reports correctly the statment being run, which was

ALTER TABLE dbo.xx ALTER COLUMN yyy FLOAT NULL

But your query returned something very interesting:

UPDATE [zzz].[dbo].[xxx] SET [yyy] = [yyy]

Which makes some sense.

This batch really took around half an hour running, and I'm sure it's the same batch. Do you think there's a way to get to the original SQL? Not the 'underlying' SQL isn't interesting...

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Adam Machanic — Mon, 14 Jan 2008 14:56:54 GMT

Pardini:

AM uses DBCC INPUTBUFFER, which is the only way I'm aware of to get that information. Unfortunately, the DBCC command does not play well with DMVs, so it can't be integrated into this script. Please vote here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=322262

re: A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?

Michael B — Fri, 22 Aug 2008 19:30:53 GMT

I get this error on one of my servers. FWIW, it is running in compatability mode 80 on a couple databases, but most are 90.

Msg 6841, Level 16, State 1, Line 1

FOR XML could not serialize the data for node 'NoName' because it contains a character (0x001C) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

A Gift of Script for 2009: Who is Active, Redux

Adam Machanic — Wed, 31 Dec 2008 15:33:51 GMT

Last year on December 31 I posted part of a larger monitoring script that I had been working on for a

A year in review, The 31 best blog posts on SQLBlog for 2008

Denis Gobo — Wed, 31 Dec 2008 15:37:49 GMT

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog

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

Adam Machanic — Mon, 30 Mar 2009 16:53:40 GMT

It has been only a month and a half since I posted the last version of Who is Active? ( v7.30 ), but

A Brief History of Monitoring (A Month of Activity Monitoring, Part 1 of 30)

Adam Machanic — Wed, 20 Apr 2011 14:43:26 GMT

This post is part 1 of a 30-part series about the Who is Active stored procedure. A new post will run