17 December,2012 by Jack Vamvas
You receive a call from an application owner reporting a severe performance slow down on a production SQL Server Instance. They point you towards a blocking session id. Rather than killing the SPID – you decide to do a root cause analysis.
Step 1 - check for queries executing
The first you do is check all SQL Server – Current queries executing on SQL Server , using sys.dn_exec_requests. You can see clearly the queries executing and on a number of the queries , there is a blocking_session_id , in the example - 77. Although the session_id does not appear on the list of active queries.
Step 2 -
The dbcc inputbuffer gives you a clue – that cursors are involved.
dbcc inputbuffer(77) --Language Event 0 FETCH API_CURSOR0000000000036AB1
Step 3 - Use sys.dm_exec_cursors
Use the DMV sys.dm_exec_cursors - which returns Returns information about the open cursors in the databases. The query returns useful information to help troubleshoot the offending process.
SELECT ec.session_id, ec.name,ec.properties, ec.creation_time, ec.is_open, ec.writes,ec.reads,t.text FROM sys.dm_exec_cursors (77) ec CROSS APPLY sys.dm_exec_sql_text (ec.sql_handle) t