sys.dm_exec_cursors (Transact-SQL)

 

Updated: August 9, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about the cursors that are open in various databases.

  
dm_exec_cursors (session_id | 0 )  

session_id | 0
ID of the session. If session_id is specified, this function returns information about cursors in the specified session.

If 0 is specified, the function returns information about all cursors for all sessions.

Column nameData typeDescription
session_idintID of the session that holds this cursor.
cursor_idintID of the cursor object.
namenvarchar(256)Name of the cursor as defined by the user.
propertiesnvarchar(256)Specifies the properties of the cursor. The values of the following properties are concatenated to form the value of this column:
Declaration Interface
Cursor Type
Cursor Concurrency
Cursor scope
Cursor nesting level

For example, the value returned in this column might be "TSQL | Dynamic | Optimistic | Global (0)".
sql_handlevarbinary(64)Handle to the text of the batch that declared the cursor.
statement_start_offsetintNumber of characters into the currently executing batch or stored procedure at which the currently executing statement starts. Can be used together with the sql_handle, the statement_end_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request.
statement_end_offsetintNumber of characters into the currently executing batch or stored procedure at which the currently executing statement ends. Can be used together with the sql_handle, the statement_start_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request.
plan_generation_numbigintA sequence number that can be used to distinguish between instances of plans after recompilation.
creation_timedatetimeTimestamp when this cursor was created.
is_openbitSpecifies whether the cursor is open.
is_async_populationbitSpecifies whether the background thread is still asynchronously populating a KEYSET or STATIC cursor.
is_close_on_commitbitSpecifies whether the cursor was declared by using CURSOR_CLOSE_ON_COMMIT.

1 = Cursor will be closed when the transaction ends.
fetch_statusintReturns last fetch status of the cursor. This is the last returned @@FETCH_STATUS value.
fetch_buffer_sizeintReturns information about the size of the fetch buffer.

1 = Transact-SQL cursors. This can be set to a higher value for API cursors.
fetch_buffer_startintFor FAST_FORWARD and DYNAMIC cursors, it returns 0 if the cursor is not open or if it is positioned before the first row. Otherwise, it returns -1.

For STATIC and KEYSET cursors, it returns 0 if the cursor is not open, and -1 if the cursor is positioned beyond the last row.

Otherwise, it returns the row number in which it is positioned.
ansi_positionintCursor position within the fetch buffer.
worker_timebigintTime spent, in microseconds, by the workers executing this cursor.
readsbigintNumber of reads performed by the cursor.
writesbigintNumber of writes performed by the cursor.
dormant_durationbigintMilliseconds since the last query (open or fetch) on this cursor was started.

Requires VIEW SERVER STATE permission on the server.

The following table provides information about the cursor declaration interface and includes the possible values for the properties column.

PropertyDescription
APICursor was declared by using one of the data access APIs (ODBC, OLEDB).
TSQLCursor was declared by using the Transact-SQL DECLARE CURSOR syntax.

The following table provides information about the cursor type and includes the possible values for the properties column.

TypeDescription
KeysetCursor was declared as Keyset.
DynamicCursor was declared as Dynamic.
SnapshotCursor was declared as Snapshot or Static.
Fast_ForwardCursor was declared as Fast Forward.

The following table provides information about cursor concurrency and includes the possible values for the properties column.

ConcurrencyDescription
Read OnlyCursor was declared as read-only.
Scroll LocksCursor uses scroll locks.
OptimisticCursor uses optimistic concurrency control.

The following table provides information about cursor scope and includes the possible values for the properties column.

ScopeDescription
LocalSpecifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created.
GlobalSpecifies that the scope of the cursor is global to the connection.

A. Detecting old cursors

This example returns information about cursors that have been open on the server longer than the specified time of 36 hours.

SELECT creation_time, cursor_id, name, c.session_id, login_name   
FROM sys.dm_exec_cursors(0) AS c   
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id   
WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 36;  
GO  

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_sessions (Transact-SQL)

Community Additions

ADD
Show: