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.

This entry was posted in SQL Server 2008. Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s