Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch

Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch

So picture the following scenario on a SQL Server 2008 R2 instance (an amalgam of various DBA situations you've no doubt seen before)… 

You get a call from the application team reporting slow performance on a specific service.  They don’t know why it is slow but they do know the session that is running too slow based on the connection and session properties.  They tell you that the issue is happening right now and that they are seeing the offending session issue the following RPC:Completed events in SQL Profiler:

·        exec sp_cursorfetch 180150003,32,1,1

·        exec sp_cursorfetch 180150003,32,1,1

·        exec sp_cursorfetch 180150003,32,1,1

·        exec sp_cursorfetch 180150003,32,1,1

They ask you to take it over and find out what is happening. You’re not sure what the original query is or why this is showing up, but you see the session id they are pointing to is “53” (and you don’t remember the syntax around pulling SQL text “the new way” – so you execute the following ):

DBCC INPUTBUFFER (53)

This returns:

               FETCH API_CURSOR0000000000000004

You do a few quick searches and it seems to have a relationship with server-side cursors. 

You try activity monitor – just in case – but again, no luck:

You take out the new DMV queries – but your query against sys.dm_exec_requests isn’t turning up anything for session id 53 because the executions of this cursor are erratic and you're not timing it (but SQL Profiler does show it plodding along in fits and starts).

You then run the following query against sys.dm_exec_connections and see if that turns up anything useful based on the most recent SQL handle:

SELECT t.text

FROM sys.dm_exec_connections c

CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t

WHERE session_id = 53

 

This returns:

FETCH API_CURSOR0000000000000004

Didn’t help.  

So what about other DMVs?  You eventually find a reference to the sys.dm_exec_cursors DMV and see it can tell you about open cursors, their properties and associated SQL handle.  But you're not sure the SQL Handle will be any help because it hasn't been helpful with the other DMVs:

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text

FROM sys.dm_exec_cursors (53) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

What do we get this time? Something a bit more useful:

From the results we see the properties of the cursor (using scroll locks) and we also see when it was created – and we see the original query text (unlike the cryptic FETCH API_CURSOR business or the sp_cursorfetch).  We see it was a SELECT * FROM dbo.FactResellerSales.

Now this isn’t to say that SQL Profiler wouldn’t have helped in this situation – but in this case the cursor was defined before the developers captured the downstream activity.  

If they had been tracing it sooner, you might have seen something like this (and then see it followed by sp_cursorfetch):

declare @p1 int

set @p1=180150003

declare @p3 int

set @p3=2

declare @p4 int

set @p4=2

declare @p5 int

set @p5=-1

exec sp_cursoropen @p1 output,N'SELECT * FROM dbo.FactResellerSales',@p3 output,@p4 output,@p5 output

select @p1, @p3, @p4, @p5

 

But in a situation where you’re reacting to an incident (fox has already left the henhouse, so to speak), chances are you weren’t tracing this activity.  And if that’s the case, you’ve now found one reason to use sys.dm_exec_cursors if you didn’t already have one.

SOURCE: http://www.sqlskills.com