Content provided by:
Ajith Krishnan 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 ,SUBSTRING(qt.text , 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 , qp.query_plan
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 <> ''