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)

sys.dm_exec_cursors

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

sys.dm_exec_cursors

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

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *