SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A DMV a Day – Day 24

The DMV for Day 24 is sys.dm_exec_requests, which is described by BOL as:

Returns information about each request that is executing within SQL Server.

This DMV is useful for getting a quick snapshot of currently executing requests on your instance of SQL Server. This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

    -- Look at currently executing requests, status and wait type
    SELECT r.session_id, r.[status], r.wait_type, r.scheduler_id, 
                (CASE WHEN r.statement_end_offset = -1 
                    THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
                    ELSE r.statement_end_offset 
                 END - r.statement_start_offset)/2) AS [statement_executing],
        DB_NAME(qt.[dbid]) AS [DatabaseName],
        OBJECT_NAME(qt.objectid) AS [ObjectName],
        r.cpu_time, r.total_elapsed_time, r.reads, r.writes, 
        r.logical_reads, r.plan_handle
    FROM sys.dm_exec_requests AS r
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
    WHERE r.session_id > 50
    ORDER BY r.scheduler_id, r.[status], r.session_id;

I like to periodically run this query multiple times against an instance to get a “feel” for what queries and stored procedures are regularly encountering which types of waits, and which ones are expensive in different ways. Unless you have a particularly long running query, the output will be different each time you run this query on a busy server.


Posted by Anonymous on 24 April 2010

Pingback from  Dew Drop – April 24, 2010 | Alvin Ashcraft's Morning Dew

Posted by kyb127 on 25 April 2010


Posted by Dukagjin Maloku on 26 April 2010

Cool info from the script, thanks again!

Posted by Anonymous on 26 April 2010

Pingback from  What are my remedies beyond the settlement? | Rebuilder Cars

Posted by Jason Brimhall on 27 April 2010

As Dugi said.

Leave a Comment

Please register or log in to leave a comment.