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.

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

This blog has moved! You can find this content at the following new location:

http://dataeducation.com/a-gift-of-script-for-2008-whos-active-what-are-they-doing-and-who-is-blocked/

Published Monday, December 31, 2007 6:32 PM by Adam Machanic

Comments

 

Thomas Williams said:

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

January 1, 2008 11:47 PM
 

jerryhung said:

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

January 2, 2008 10:17 AM
 

Ranga said:

Excellent script...Thanks.

January 2, 2008 11:08 AM
 

Vamsi said:

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

January 2, 2008 11:32 AM
 

Aaron Bertrand said:

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

January 2, 2008 12:44 PM
 

aaron said:

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 (<!-- and -->) around the text but they will always be in the same place.

Hope that helps.

January 5, 2008 1:46 PM
 

Kevin Boles said:

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?

January 6, 2008 11:53 AM
 

Adam Machanic said:

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?

January 6, 2008 2:38 PM
 

Javier Villegas said:

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

January 9, 2008 3:56 PM
 

Pardini said:

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

January 11, 2008 3:14 PM
 

Adam Machanic said:

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

January 14, 2008 9:56 AM
 

Michael B said:

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.

August 22, 2008 3:30 PM
 

Adam Machanic said:

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

December 31, 2008 10:33 AM
 

Denis Gobo said:

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

December 31, 2008 10:37 AM
 

Adam Machanic said:

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

March 30, 2009 12:53 PM
 

Adam Machanic said:

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

April 20, 2011 10:43 AM
New Comments to this post are disabled

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