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

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

Comments (1)

  1. Adam Machanic says:


    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:


    Adam Machanic