SQL Server – Troubleshoot open cursors with sys.dm_exec_cursors

17 December,2012 by Tom Collins

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

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – Troubleshoot open cursors with sys.dm_exec_cursors

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer