sys.dm_exec_requests

One of the dynamic management views (DMVs) that is very useful in troubleshooting query performance is sys.dm_exec_requests.  The documentation around this DMV, however, is quite lacking in two areas.

percent_complete

This column shows the "percent of work completed for certain operations, including rollbacks."  Okay, great, now could you tell us WHICH certain operations?  Through experimenting with commands that I thought could be slow and/or interesting, I came up with this list of commands that *do* report percent_complete:

  •     backup / restore
  •     dbcc checkdb / checktable / etc.
  •     dbcc shrinkdatabase / shrinkfile
  •     dbcc indexdefrag
  •     alter index reorganize
  •     rollback operations

And this list of commands which I thought might report percent_complete, but do not:

  •     create / drop database
  •     create / drop index / statistics
  •     alter index rebuild
  •     waitfor delay / time
  •     drop table
  •     truncate table
  •     any DML operations whatsoever

Do you know of anything that should be in either list?

samples

In the SQL Server 2005 topic, there are three samples which are just not very good IMHO.  Worse yet, in the SQL Server 2008 topic, the samples have been removed completely.

The first one explains how to find the query text for a running batch, and it basically says run the following:

SELECT * FROM sys.dm_exec_requests;

Then pick the spid you are interested in, copy the value from the sql_handle column, and paste it into this query:

SELECT * FROM sys.dm_exec_sql_text(< copied sql_handle >);

Yuck!  How about doing this in one step:

SELECT
    [spid] = r.session_id,
    [database] = DB_NAME(r.database_id),
    r.start_time,
    r.[status],
    r.command,
    /* add other interesting columns here */
    [obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
    + '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
    t.[text]
FROM
    sys.dm_exec_requests AS r
CROSS APPLY
    sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
    r.session_id <> @@SPID
    AND r.session_id > 50
    /*
    — optionally:
    AND r.session_id IN (< list of interesting spids >)
    */
;

The second sample does something similar with transactions.  Run a select * from dm_exec_requests, take the transaction_id, and copy it into a query against sys.dm_tran_locks.  How about:

SELECT
    [spid] = r.session_id,
    [database] = DB_NAME(r.database_id),
    r.start_time,
    r.[status],
    r.command,
    [obj] = QUOTENAME(OBJECT_NAME(t.resource_associated_entity_id, r.database_id)),
    /* add other interesting columns here */
    t.request_mode,
    t.request_type,
    t.request_status
FROM
    sys.dm_exec_requests AS r
LEFT OUTER JOIN
    sys.dm_tran_locks AS t
ON
    r.transaction_id = t.request_owner_id
WHERE
    t.request_owner_type = N'TRANSACTION'
    AND r.session_id <> @@SPID
    AND r.session_id > 50
    /*
    — optionally:
    AND r.session_id IN (< list of interesting spids >)
    */
;

And finally, the third sample in the 2005 docs shows you how to get all of the sessions that are blocked.  How about retrieving both the blocked *and* blocking processes?  While this is a much more convoluted example and it involves many more objects, it certainly does a better job of showing off the power of the DMVs:

WITH blocking_info AS
(
    SELECT
        [blocker] = wait.blocking_session_id,
        [waiter] = lock.request_session_id,
        b_handle = br.[sql_handle],
        w_handle = wr.[sql_handle],
        [dbid] = lock.resource_database_id,
        duration = wait.wait_duration_ms / 1000,
        lock_type = lock.resource_type,
        lock_mode = block.request_mode
    FROM
        sys.dm_tran_locks AS lock
    INNER JOIN
        sys.dm_os_waiting_tasks AS wait
        ON lock.lock_owner_address = wait.resource_address
    INNER JOIN
        sys.dm_exec_requests AS br
        ON wait.blocking_session_id = br.session_id
    INNER JOIN
        sys.dm_exec_requests AS wr
        ON lock.request_session_id = wr.session_id
    INNER JOIN
        sys.dm_tran_locks AS block
        ON block.request_session_id = br.session_id
    WHERE
        block.request_owner_type = 'TRANSACTION'
)
SELECT
    [database] = DB_NAME(bi.[dbid]),
    bi.blocker,
    blocker_command = bt.[text],
    bi.waiter,
    waiter_command  = wt.[text],
    [duration MM:SS] = RTRIM(bi.duration / 60) + ':'
        + RIGHT('0' + RTRIM(bi.duration % 60), 2),
    bi.lock_type,
    bi.lock_mode
FROM
    blocking_info AS bi
CROSS APPLY
    sys.dm_exec_sql_text(bi.b_handle) AS bt
CROSS APPLY
    sys.dm_exec_sql_text(bi.w_handle) AS wt;

So, now what?

Well, I complained about these issues to some extent in the following Connect items:

http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354545

http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284207

The former is fairly new, and I am awaiting some response.  The latter was closed as "fixed" but this made no sense — the topic has actually taken a step backward since I lodged my initial complaint.  I re-opened it earlier today.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

2 Responses

  1. Eric says:

    I gave your last query a try and found that a blocker has to be actively running for that to return results.  I was giving it a go by running an update with a begin tran.  Those statements completed and in the other window I did a select on that table.  It waits for a share lock, but the blocking query fails to join on any rows based on this join (because the blocker isn't active, it's sleeping):
    INNER JOIN
    sys.dm_exec_requests AS wr
    ON lock.request_session_id = wr.session_id
    Thanks,
    Eric

  2. Linchi Shea says:

    Yeah, it would be nice if it reported percent_complete for the ALTER INDEX operations that are expensive, for instance, ALTER INDEX ALL ON abc REBUILD.
    I'm not sure whether there is a real need for reporting percent_complete for DROP TABLE and TRUNCATE TABLE.