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:

• 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_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.

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.

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.

Read Only

Cursor was declared as read-only.

Scroll Locks

Cursor uses scroll locks.

Optimistic

Cursor uses optimistic concurrency control.

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.