sys.dm_exec_requests

Aaron Bertrand July 1st, 2008 SQL Server 2

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.

Eric says:

January 9, 2009 at 8:43 AM

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
Linchi Shea says:

July 5, 2008 at 5:31 PM

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.