March 13, 2015 at 2:42 pm
Comments posted to this topic are about the item Show running queries
March 20, 2015 at 6:44 am
Nice. I *love* that you included the execution plan.
Thanks for sharing.
January 21, 2016 at 3:00 am
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
February 25, 2016 at 4:28 pm
What a great script, thanks.
April 27, 2016 at 7:00 am
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