Running Queries - sys.dm_exec_cursors capture

[DavidWiseman]

For some environments it might be useful to capture cursor information from sys.dm_exec_cursors. On the running queries tab you might only see something like FETCH API_CURSOR000000000000000A without any additional context. Or a sp_cursorfetch call.

This is a simple example for testing (note: be sure to run ROLLBACK):

BEGIN TRAN
CREATE TABLE #handle(
    cursor_handle INT
)
DECLARE @cursor_handle INT
DECLARE @scrollopt INT = 2      -- Scroll locks
DECLARE @ccopt INT = 2          -- Optimistic concurrency
DECLARE @rowcount INT = -1

-- Open the cursor
EXEC sp_cursoropen 
    @cursor_handle OUTPUT,
    N'SELECT * FROM sys.databases',  -- Your query here
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT

INSERT INTO #handle
SELECT @cursor_handle

SELECT @cursor_handle AS cursor_handle, 
       @scrollopt AS scrollopt, 
       @ccopt AS ccopt, 
       @rowcount AS [rowcount]

GO
DECLARE @cursor_handle INT
SELECT @cursor_handle = cursor_handle FROM #handle
EXEC sp_cursorfetch @cursor_handle, 32, 1, 1  -- Fetch next 1 row
GO
DECLARE @cursor_handle INT
SELECT @cursor_handle = cursor_handle FROM #handle
EXEC sp_cursorfetch @cursor_handle, 32, 1, 1  -- Fetch next 1 row

We only capture the last statement after the GO. This tells us nothing about the original query.

Using sys.dm_exec_cursors, we can identify the query associated with the cursor. A session can have multiple cursors. It might be worth capturing as an additional resultset. Maybe with a link on Running Queries to show associated cursors.

The capture should be optional and disabled by default - many environments won't benefit from this.