sys.dm_exec_cursors – Day 38 – One DMV a Day

Hello Geeks and welcome to the Day 38 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday I have talked about check trigger statistics using sys.dm_exec_trigger_stats. Today I am going to cover another execution related DMV. sys.dm_exec_cursors is used to get the cursor details in a session. This is a Dynamic Management Function. It accepts session id as a parameter and provides cursor details related to the session.

Sys.dm_exec_cursors provides the below useful information of a cursor in a session. This is useful when tracking the cursor status in the session. If you need to track the sessions that are using any cursors you can join sys.dm_exec_cursors with sys.dm_exec_sessions DMV and get the session details. By default if you provide “0” as the session id parameter sys.dm_exec_cursors gives details of all cursors.

--session 55
DECLARE cur1 CURSOR
FOR SELECT eid, ename FROM publogger_tbl

OPEN cur1

FETCH NEXT FROM cur1

Now run the below query to get the details from sys.dm_exec_cursors.

SELECT session_id,
	cursor_id,
	name,
	properties,
	creation_time,
	is_open,
	is_async_population,
	is_close_on_commit,
	fetch_status,
	fetch_buffer_size,
	fetch_buffer_start,
	ansi_position,
	worker_time/1000 as worker_ms,
	reads,
	writes
FROM sys.dm_exec_cursors(0)

You will observe that the is_open column is 1. The fetch_status is 0. When you are fetching data from the cursor you check @@FETCH_STATUS = 0. From sys.dm_exec_cursors you will know if there are still records to be fetched by the cursor. You can see the cursor creation time, properties, asynchronous check, worker time (I changed it to ms in the query), etc. The fetch_buffer_size and fetch_buffer_start depends on the cursor type.

Fetch_buffer_size – 1 for TSQL cursors. Higher value can be set for API cursors.
Fetch_buffer_start – For FAST_FORWARD and DYNAMIC cursors it is -1 when open and 0 when closed or before the first row. For STATIC and KEYSET it returns -1 if positioned beyond last row, 0 when not open and positioned row number in other cases.

Now close the cursor and run the above query on sys.dm_exec_cursors. You will observe is_open changes to 0. As this is DYNAMIC cursor you will see -1 for fetch_buffer_start. Fetch_buffer_size will be 1 as I am using TSQL cursor.

--session 55
CLOSE cur1

Now DEALLOCATE the cursor. You will not see the entry in sys.dm_exec_cursors.

--session 55
DEALLOCATE cur1

So from now on if someone asks who is running cursors on SQL instance? You know the answer. Tomorrow I will be covering one more execution related DMV. Stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook