Show running queries

  • Comments posted to this topic are about the item Show running queries

  • Nice. I *love* that you included the execution plan.

    Thanks for sharing.

  • It's not my script but I think my friend will not be mad :cool:. It is showing every open session and current running query. Very helpful when some session is blocked by another.

    SELECT s.session_id,s.original_login_name, s.session_id, s.program_name, r.command,

    (SELECT TOP (1) SUBSTRING(t.text, r.statement_start_offset / 2 + 1,

    ((CASE WHEN r.statement_end_offset = -1

    THEN (LEN(CONVERT(nvarchar(max), t.text)) * 2)

    ELSE r.statement_end_offset

    END) - r.statement_start_offset) / 2 + 1)) AS SqlStatement,

    r.wait_type, r.wait_time, r.blocking_session_id, t.*

    FROM sys.dm_exec_sessions AS s

    LEFT JOIN sys.dm_exec_requests AS r

    ON r.session_id = s.session_id

    OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS t

    WHERE s.is_user_process = 1

    and r.command is not null

    SELECT s.session_id,s.original_login_name, s.session_id, s.program_name, r.command,

    (SELECT TOP (1) SUBSTRING(t.text, r.statement_start_offset / 2 + 1,

    ((CASE WHEN r.statement_end_offset = -1

    THEN (LEN(CONVERT(nvarchar(max), t.text)) * 2)

    ELSE r.statement_end_offset

    END) - r.statement_start_offset) / 2 + 1)) AS SqlStatement,

    r.wait_type, r.wait_time, r.blocking_session_id, t.*

    FROM sys.dm_exec_sessions AS s

    LEFT JOIN sys.dm_exec_requests AS r

    ON r.session_id = s.session_id

    OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS t

    WHERE s.is_user_process = 1

  • What a great script, thanks.

  • Just wanted to say again, thanks for sharing.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply