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 name||Data type||Description|
|session_id||int||ID of the session that holds this cursor.|
|cursor_id||int||ID of the cursor object.|
|name||nvarchar(256)||Name of the cursor as defined by the user.|
|properties||nvarchar(256)||Specifies the properties of the cursor. The values of the following properties are concatenated to form the value of this column:
Cursor nesting level
For example, the value returned in this column might be "TSQL | Dynamic | Optimistic | Global (0)".
|sql_handle||varbinary(64)||Handle to the text of the batch that declared the cursor.|
|statement_start_offset||int||Number 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_offset||int||Number 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_num||bigint||A sequence number that can be used to distinguish between instances of plans after recompilation.|
|creation_time||datetime||Timestamp when this cursor was created.|
|is_open||bit||Specifies whether the cursor is open.|
|is_async_population||bit||Specifies whether the background thread is still asynchronously populating a KEYSET or STATIC cursor.|
|is_close_on_commit||bit||Specifies whether the cursor was declared by using CURSOR_CLOSE_ON_COMMIT.
1 = Cursor will be closed when the transaction ends.
|fetch_status||int||Returns last fetch status of the cursor. This is the last returned @@FETCH_STATUS value.|
|fetch_buffer_size||int||Returns 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_start||int||For 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_position||int||Cursor position within the fetch buffer.|
|worker_time||bigint||Time spent, in microseconds, by the workers executing this cursor.|
|reads||bigint||Number of reads performed by the cursor.|
|writes||bigint||Number of writes performed by the cursor.|
|dormant_duration||bigint||Milliseconds 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.
|API||Cursor was declared by using one of the data access APIs (ODBC, OLEDB).|
|TSQL||Cursor 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.
|Keyset||Cursor was declared as Keyset.|
|Dynamic||Cursor was declared as Dynamic.|
|Snapshot||Cursor was declared as Snapshot or Static.|
|Fast_Forward||Cursor was declared as Fast Forward.|
The following table provides information about cursor concurrency and includes the possible values for the properties column.
|Read Only||Cursor was declared as read-only.|
|Scroll Locks||Cursor uses scroll locks.|
|Optimistic||Cursor uses optimistic concurrency control.|
The following table provides information about cursor scope and includes the possible values for the properties column.
|Local||Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created.|
|Global||Specifies 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