sys.dm_exec_cursors (Transact-SQL)
Returns information about the cursors that are open in various databases.
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:
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. |
The following table provides information about the cursor declaration interface and includes the possible values for the properties column.
Property | Description |
|---|---|
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.
Type | Description |
|---|---|
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.
Concurrency | Description |
|---|---|
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.
Scope | Description |
|---|---|
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
