Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Did I mention I use this blog to keep scripts so I do not lose them? Well, I do, it is easier to find stuff on the internet than it is on my own machine. This script is not new, it is not extra special for 2014, it's just a basic head blocker script with query statement text. Finding who is backed up behind your head blocker is pretty easy, but finding who is in front requires more time than I care to take when I need it RIGHT NOW! Which did happen today, my immediate stand by is the sql_perf_stats_script that ships with pssdiag and sqldiag, but it has entirely too much logic and sometimes you just need low impact quick and dirty!
SELECT db_name(er.database_id),
er.session_id,
es.original_login_name,
es.client_interface_name,
er.start_time,
er.status,
er.wait_type,
er.wait_resource,
SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text,
er.*
FROM SYS.dm_exec_requests er
join sys.dm_exec_sessions es on (er.session_id = es.session_id)
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
where er.session_id in
(SELECT distinct(blocking_session_id) FROM SYS.dm_exec_requests WHERE blocking_session_id > 0)
and blocking_session_id = 0
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in