none
How to troubleshoot performance issues due to FETCH API_CURSOR ?

    Question

  • Our database occasionally has high CPU due to sql server process and it's virtually impossible to pinpoint the cause. 99% of the connections are using FETCH API_CURSOR so I can't even tell what sql statements the connections are executing (this is a third-party application). The sys.dm_exec_query_stats shows around 10,000 entries and the sys.dm_exec_sql_text  dmv for these sql handles only show entries like this - "FETCH API_CURSOR00000000000171D9". It's a quad-core machine and runs pretty smoothly 90% of the time. So far I have not been able to pinpoint a particular connection or connections (out of around 1,000 concurrent on an average day) which could be the cause. Is there any way to find out more about FETCH API_CURSOR inner workings  or to troubleshoot high CPU usuage on a SQL Server ? I have gone through the document at http://msdn.microsoft.com/en-us/library/cc966540.aspx but could not find anything of significance on my server.
    • Moved by Tom PhillipsModerator Thursday, April 29, 2010 9:37 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Thursday, April 29, 2010 9:30 PM

Answers

  • Sometimes you may find a query plan for this fetch in the plan cache. If not, you can create a server trace straight to disk using the completed events with columns cpu, duration, reads, writes, textdata,... and some others you like, along with the query plan. For spid that issues this fetch, seek the next record in the trace, this should be smething like "sp_cursorfetch <number>". The first record above for the same spid should be the execution plan. to find the creation statement, Seek the cursor number upwards in the trace for this spid until you find it.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance guru - www.sql-server.de
    • Marked as answer by KJian_ Thursday, May 06, 2010 8:56 AM
    Thursday, April 29, 2010 11:32 PM

All replies

  • Sometimes you may find a query plan for this fetch in the plan cache. If not, you can create a server trace straight to disk using the completed events with columns cpu, duration, reads, writes, textdata,... and some others you like, along with the query plan. For spid that issues this fetch, seek the next record in the trace, this should be smething like "sp_cursorfetch <number>". The first record above for the same spid should be the execution plan. to find the creation statement, Seek the cursor number upwards in the trace for this spid until you find it.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance guru - www.sql-server.de
    • Marked as answer by KJian_ Thursday, May 06, 2010 8:56 AM
    Thursday, April 29, 2010 11:32 PM
  • I am having similar performance issues and see the same Fetch API_CURSOR during that time. Were you able to solve the issue? We have recently upgraded our DB to SQL2008 R2 and have also upgraded our 3<sup>rd</sup> party ERP system.

    Wednesday, October 05, 2011 12:19 PM
  • Not quite. The query plan contains only the FETCH_API_CURSORXXXXX statements. The server trace may work but doing so would put extra load on the server and we can't have trace running 24x7. I was looking at doing realtime analysis, so haven't quite found a solution.
    Monday, October 24, 2011 10:29 PM
  • Any new findings on this issue?

    Monday, November 28, 2011 10:50 PM
  • Whats up with Moderator?!
    HydPhani
    Monday, January 23, 2012 6:52 AM
  • Monday, January 30, 2012 9:58 AM