FETCH API_CURSOR ....

Question

I am trying to get more info about a "FETCH API_CURSOR0000000003" that is showing in the activity monitor with much execution and much plan count.
I tried different queries to get the sessionid to know the exact query behind the FETCH but I failed to find any positive information.
I tried: Select * from sys.dm_exec_cursors(0) but it doesn't show any information.
tried different sp_who/sp_who2
I tried the XEvent profiler and tracking each sessionid, and none gave me the specific cursor causing the issue.

Answer 1

If they query is not returning any data right now, this is supposedly because there were no active cursors at the time you ran it.

You could run this query with some frequency to see what you get.

If you want to trace it, you should filter for sp_cursoropen and sp_cursorfetech. The latter is the one causing the FETCH APICURSOR, but sp_cursoropen will give you information about the query driving the cursor.

Then again, while the number of plans is staggering, the other numbers do not really look frightening to me. Do you have a real performance problem?

Answer 2

Hi,

Have you tried the query in the link?

it can list cursors for all sessions

    SELECT c.session_id, es.program_name, es.login_name, es.host_name, DB_NAME(es.database_id) AS DatabaseName, c.properties, c.creation_time, c.is_open, t.text  
    FROM sys.dm_exec_cursors (0) c  
    LEFT JOIN sys.dm_exec_sessions AS es ON c.session_id = es.session_id  
    CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t  

I read somewhere that cursors are hard to trace without running the appropriate queries, and it is recommended to run scripts to trace in advance

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @KZS ,

This example will return sessions with open cursors, you can try:

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  

https://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/
https://blog.sqlauthority.com/2015/01/10/sql-server-what-is-the-query-used-in-sp_cursorfetch-and-fetch-api_cursor/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Why it doesn't show any information ?

Answer 5

the query is not showing any data