SQL SERVER – What is the query used in sp_cursorfetch and FETCH API_CURSOR?

In a recent debugging exercise for performance, one of my friend recently posted a question to me:

“When I view activity monitor, under the recent expensive queries and under query column, there I see a “fetch api_cursor0000000000000003”. What is this? How can I know what was running behind this query?

Obviously, the hint from that question was the keywords “Fetch and Cursor”. So I knew what the starting point would be. How many times you have been into situation where you want to track a query but when you capture profiler or use conventional methods of sys.sysprocesses or DBCC INPUTBUFFER you would see something like this:

SQL SERVER - What is the query used in sp_cursorfetch and FETCH API_CURSOR? Cursor-01

The same thing in profiler we would look like below:

SQL SERVER - What is the query used in sp_cursorfetch and FETCH API_CURSOR? Cursor-02

EXEC sp_cursorfetch 180150003,16,8,1

Solarwinds

OR

FETCH API_CURSOR0000000000000001

Note: the number in the query might differ in your environment though. Here is a VBScript code to simulate the problem.

Dim strConnection
Dim MyConnection
Dim MyRecordSet
Dim strSQL
strConnection = “Provider=SQLOLEDB;Data Source=.\SQL2014;Initial Catalog=Master;Integrated Security=SSPI;”
Set MyConnection = CreateObject(“ADODB.Connection”)
MyConnection.Open strConnection
Set MyRecordSet = CreateObject(“ADODB.recordset”)
strSQL = “SELECT TOP 3 * FROM sysobjects”
MyRecordSet.Cursorlocation = 2
MyRecordSet.Open strSQL, MyConnection, 3,3
MyRecordSet.MoveFirst
WHILE NOT MyRecordSet.EOF
MsgBox(MyRecordSet(“name”).value)
MyRecordSet.MoveNext
WEND
MyRecordSet.Close
Set MyRecordSet = Nothing

To reproduce the issue, you can save above code in a file and keep extension .vbs. Then you can run from command prompt. You may need to change value of Source in the connection string to match your environment. Once we run the VB Script, you would get a popup with the table name. If profiler is capture from the beginning, we should see sp_cursoropen which can tell the query. If query is already running then we can use below query

SELECT creation_time,
cursor_id,
c.session_id,
c.properties,
c.creation_time,
c.is_open,
SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
(
CASE c.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE c.statement_end_offset
END - c.statement_start_offset) / 2) + 1) AS statement_text
FROM   sys.Dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st
GO

Here is the sample execution and as we can see, we can get query text which has opened cursor.

 SQL SERVER - What is the query used in sp_cursorfetch and FETCH API_CURSOR? Cursor-03_

Note: The cursor_id is same as what we saw in sp_cursorfetch . Additionally, if we capture complete profile, we can see the below output:

SQL SERVER - What is the query used in sp_cursorfetch and FETCH API_CURSOR? Cursor-04

Moral of the story: If you are seeing sp_cursorfetch or FETCH API_CURSOR0000000000000001 then either capture complete profiler, since the starting of the query OR use sys.Dm_exec_cursors to get exact query.

Do let me know if you every faced this problem in your environments before. How did you debug the same?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – What is is_not_trusted in sys.foreign_keys?
Next Post
Interview Question of the Week #002 – Script to Find Byte Size of a Row for All the Tables in Database

Related Posts

1 Comment. Leave new

  • Hi Pinal,

    I wonder if you ever looked into extended events and tried to “catch” FETCH_APICURSOR or EXEC sp_cursorfetch?

    Best regards
    Mötz

    Reply

Leave a Reply

Menu