SQL Server – Troubleshoot open cursors with sys.dm_exec_cursors

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 

Read More

SQL Server – Current queries executing on SQL Server

Top 5 SQL Server DMV for Index Analysis

SQL Server - Find all DMV and DMF

SQL Server sql_handle returns sql statement