sys.dm_exec_cursors (Transact-SQL)
Returns information about the cursors that are open in various databases.
Syntax
dm_exec_cursors (session_id | 0 )
Arguments
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.
Table Returned
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. |
Permissions
Requires VIEW SERVER STATE permission on the server.
Remarks
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. |
Examples
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