DMVs – sys.dm_exec_Requests and Percent_Complete

One of my most used Dynamic Management Views (DMV’s) is sys.dm_exec_requests. This DMV returns one row for each request executing within SQL Server. One thing to note is that it doesn’t return info on code that is executing outside of SQL Server such as distributed queries or extended stored procs.

One of my best features of this view is the Percent_Complete column.

Which operations does Percent_Complete provide information on?

Percentage of work completed for the following commands:

  • ALTER INDEX REORGANIZE

  • AUTO_SHRINK option with ALTER DATABASE

  • BACKUP DATABASE

  • DBCC CHECKDB

  • DBCC CHECKFILEGROUP

  • DBCC CHECKTABLE

  • DBCC INDEXDEFRAG

  • DBCC SHRINKDATABASE

  • DBCC SHRINKFILE

  • RECOVERY

  • RESTORE DATABASE,

  • ROLLBACK

  • TDE ENCRYPTION

In my opinion, this doesnt go far enough. for someone who has become used to using Toad for Oracle, and its long ops tab for sessions, this doesnt go far enough. I would love to see it implemented further and be avaiable for some of the following:

  • ALTER INDEX REBUILD

  • FULL TABLE SCANS

  • CREATE/DROP INDEX

  • Useful scripts using this DMV

    Basic script which puts percent_complete as first column, making it easy to find a session which is reporting its progress:

    select percent_complete, * from sys.dm_exec_requests

    How to find the query text for a running batch:

    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 following script find all blocked and blocking sessions:

    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;

    Credit for the scripts to Aaron Bertrand

    Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: