How to find “waiting” STATEMENT inside Microsoft SQL Server stored procedure
Content provided by:
Microsoft SQL Server Escalation Services
I was working with Ajith the other day to track down a blocking problem for a customer. We know the “problem” stored procedure but wanted a quick way to determine which statement inside the stored procedure were being blocked. Ajith wrote the query below using the SQL Server Dynamic Management View sys.dm_exec_requests to pull the statements inside the executing stored procedure which were WAITING.
–SQL Statement Blocking
select wait_type , wait_time , wait_resource , database_id , blocking_session_id
, case when r.statement_start_offset is null then 1 else (r.statement_start_offset/2) end
, case when r.statement_end_offset is null then len(qt.text) else ((r.statement_end_offset–r.statement_start_offset)/2) end) as stmt
from sys.dm_exec_requests r
Cross Apply sys.dm_Exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) as qp
where wait_type <> ”
One thought on “How to find “waiting” STATEMENT inside Microsoft SQL Server stored procedure”
This is unfortunately a problematic approach. The "wait_type" in sys.dm_exec_requests indicates only the wait for the root/coordinator task (exec_context_id 0). It may not always show a wait type, and in the case of blocking will almost certainly not show a lock wait, when the request is using a parallel plan.
I've written a stored procedure that handles this all in a much more consistent manner, while delivering a lot of other useful information. Check it out next time you need this kind of information: