T-SQL Query to display all currently executing user commands | SansSQL

Saturday, October 12, 2013

T-SQL Query to display all currently executing user commands

Here is a T-SQL Query to display all currently executing user commands on an SQL Server Instance

SELECT r.session_id
            ,status
            ,SUBSTRING(qt.text,r.statement_start_offset/2, 
                  (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 query_text
            ,qt.dbid
            ,qt.objectid
            ,r.cpu_time
            ,r.total_elapsed_time
            ,r.reads
            ,r.writes
            ,r.logical_reads
            ,r.scheduler_id
FROM sys.dm_exec_requests 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

No comments:

Post a Comment