SQL 2005 Current Query Activity

By on October 30th, 2007 

SELECT    sder.session_id AS [SPID],    sdes.login_name AS [Login],    sd.name AS [DBName],    sder.start_time AS [Start Time],    sder.status AS [Status],    sder.command AS [Command],    sdet.text AS [SQL Text],    sder.percent_complete AS [Pct Cmplt],    sder.estimated_completion_time AS [Est Cmplt Time],    sder.wait_type AS [Wait],    sder.wait_time AS [Wait Time],    sder.last_wait_type AS [Last Wait],    sder.cpu_time AS [CPU Time],    sder.total_elapsed_time AS [Total Elpsd Time],    sder.reads AS [Reads],    sder.writes AS [Writes],    sder.logical_reads AS [Logical Reads]FROM    sys.dm_exec_Requests sder    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sdet    JOIN sys.dm_exec_sessions sdes on sder.session_id = sdes.session_id    JOIN sys.databases sd on sder.database_id = sd.database_idWHERE    sder.session_id <> @@SPID;
Categorized under: 2005, SQL Server.
Tagged with: no tags.

One Response to “SQL 2005 Current Query Activity”

  1. Daniel says:
    2011/07/19 at 6:13 AM

    This came in handy, thanks.

    I modified it slightly to also return a CSV of the SET options in place:

    SELECT sder.session_id AS [SPID], sdes.login_name AS [Login], sd.name AS [DBName], sder.start_time AS [Start Time], sder.status AS [Status], sder.command AS [Command], sdet.text AS [SQL Text], sder.percent_complete AS [Pct Cmplt], sder.estimated_completion_time AS [Est Cmplt Time], sder.wait_type AS [Wait], sder.wait_time AS [Wait Time], sder.last_wait_type AS [Last Wait], sder.cpu_time AS [CPU Time], sder.total_elapsed_time AS [Total Elpsd Time], sder.reads AS [Reads], sder.writes AS [Writes], sder.logical_reads AS [Logical Reads]
    ,Stuff( ( SELECT ‘, ‘ + v.name
    FROM sys.dm_exec_plan_attributes( sder.plan_handle ) AS pa
    JOIN master..spt_values v
    ON v.type = ‘sop’ — Set OPtions I guess
    AND v.number & Cast( pa.value AS int ) = v.number
    WHERE attribute = ‘set_options’
    FOR XML PATH(”)
    ), 1, 13, ” ) AS [Set Options]
    FROM sys.dm_exec_Requests sder
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sdet
    JOIN sys.dm_exec_sessions sdes
    on sder.session_id = sdes.session_id
    JOIN sys.databases sd
    on sder.database_id = sd.database_id
    WHERE sder.session_id @@SPID;

    Respond

Leave a Response