Home Uncategorized A Gift of Script for 2008: Who’s Active, What Are They Doing,...

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

    486
    12

    As with all of the blog posts I keep meaning to write — I keep a list and given the infrequency with which I’ve been posting lately, it’s getting quite large — this script has been on the queue for quite some time. So here I find myself with a spare moment right on the cusp of the new year, and figured what better way to end the year than with a script that, at least for me, has been quite useful these last few months.

    The driving force behind my writing this script is that I found myself endlessly calling sp_who2 ‘active’ to see who was doing what on servers I needed to take a look at. Then I would have to sort through the results, and end up calling DBCC INPUTBUFFER to take a look at the SQL being used. This was a serious pain, and I finally caved a few months back and decided to end the madness once and for all with the help of some DMVs.

    The following script primarily uses the sys.dm_exec_requests view, and finds all “active” requests — i.e., those that are running, about to start running, or suspended. It also finds some other useful information, including the host name, login name, the start time of the batch, and whether or not the batch is currently blocked. In the outer query I use the sys.dm_exec_sql_text function to get the text of the SQL that all of the active requests are running, in addition to the SQL being run by the blocking sessions, if applicable. This way I don’t have to do two lookups to chase down what’s blocking what.

    You’ll notice that I use FOR XML PATH in the subqueries that pull the SQL text. This gives us a nice little bonus: instead of copying the text out of the cell in SSMS and pasting it somewhere else, you can simply click on it — and it maintains whatever formatting, including white space and carriage returns, that it originally had. This is much, much nicer than getting the batch on a single line. The only problem is that certain characters, such as greater-than and less-than, get “entitized” when the text is converted to XML. This means that some queries won’t be able to be run without a bit of editing. A small price to pay for nicer output, in my opinion. If anyone out there has a solution for the entitization, please let me know! The only way I know to solve it is to convert back to VARCHAR, and that defeats the whole purpose…

    Anyway, thanks all for a great 2007. Here’s to an even better 2008! Without further ado, the script:

    SELECT
        x.session_id,
        x.host_name,
        x.login_name,
        x.start_time,
        x.totalReads,
        x.totalWrites,
        x.totalCPU,
        x.writes_in_tempdb,
        (
            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,
            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.blocking_session_id
    ) x
    

    Enjoy!

    Previous articleAnti-Patterns and Malpractices, Volume 1: Tumbling Data
    Next articleWho’s On First? Solving the Top per Group Problem (Part 1: Technique)
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    12 COMMENTS

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

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

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

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

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

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

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

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

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

    Comments are closed.