Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 148

sys.dm_exec_query_plan (DMF)
sys.dm_exec_query_plan is a table-valued dynamic management function. This DMF
returns the query execution plan for a specified plan_handle that is passed to this function
as a parameter. The following are some of the columns of this DMF:

Column name Description

dbid This column represents the ID of the database.

objected This column represents the ID of the object.

encrypted This column represents whether the SQL text is encrypted.

query_plan This column represents the actual query execution plan for specified

plan_handle .

sys.dm_exec_cursors (DMF)
sys.dm_exec_cursors is a table-valued dynamic management function. This DMF returns
the list of cursors that are currently open for a given session_id or all sessions (for all
sessions, session_id should be 0 ). The following are some of the useful columns of
this DMF:

Column name Description

session_id This column represents the ID of the session that executes

the cursor.

cursor_id This column represents the ID of the cursor object.

name This column represents the name of the cursor object.

properties This column represents the cursor options with which the cursor

was created.

sql_handle This column represents the sql_handle of the SQL batch that

declared the cursor object.

This column represents the time when the cursor was created.

creation_time

is_open This column represents the status of the cursor to indicate whether

the cursor is open or not.

fetch_status This column represents the last fetch status that was returned by

@@FETCH_STATUS .

worker_time This column represents the time duration in milliseconds that the

worker has taken to execute the cursor.

reads This column represents the number of reads caused by the cursor.

writes This column represents the number of writes caused by the cursor.