Head Blocking Script, Again…

  • Article

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