How to find “waiting” STATEMENT inside Microsoft SQL Server stored procedure

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 <> ”