Have you ever wondered what SQL generated that FETCH statement?

Have you ever seen a FETCH statement show up when monitoring via activity monitor, sysprocesses, dm_exec_requests, or name your monitoring tool of choice?  These surface when a process is programatically fetching through a cursor.  Most of the time the fetch statement isn’t of interest when performance tuning.  Instead it is of more use to see the actual statement from the cursor that is being fetched.  In this post I’ll show how you can find the statement associated with the fetch.

sys.dm_exec_cursors

The key to finding the actually statement associated with a FETCH statement is using the DMV sys.dm_exec_cursors.  This is a table valued function that takes a session_id as an input.  You can return every cursor in every session by passing a 0 for the session_id.  In our example we’ll join to sys.dm_exec_requests on session_id instead.

This example will return sessions with open cursors.  It will not only display the sql text based on the sql_handle found in sys.dm_exec_requests, but it will show the associated declare statement used to create the cursor.

SELECT er.sql_handle, ec.sql_handle,
SUBSTRING(ers.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(ers.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text_er,
SUBSTRING(ecs.text, (ec.statement_start_offset/2)+1,
((CASE ec.statement_end_offset
WHEN -1 THEN DATALENGTH(ecs.text)
ELSE ec.statement_end_offset
END - ec.statement_start_offset)/2) + 1) AS statement_text_ec
FROM sys.dm_exec_requests er cross apply sys.dm_exec_cursors(er.session_id) ec
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) ers
CROSS APPLY sys.dm_exec_sql_text(ec.sql_handle) ecs

Some example output from this is below.  The statement_text_er column shows the statement text that is related to the sql_handle from sys.dm_exec_requests.  The statement_text_ec column shows the declaration of the statement associated with the open cursor in the session.  This is fairly representative of what you would see if declaring a cursor and iterating through it via a stored procedure or in a SQL Batch.

Below is another example of output when a cursor is declared in a program (this was declared in a COM object).  This typically shows up as FETCH API_CURSOR.

 

Leave a Reply

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