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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy