How to find “waiting” STATEMENT in 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 <> ''