2

I have a customer who is reporting that a query appears in the activity monitor expensive query list with a very high executions per minute. The query is a FETCH API_CURSOR000000000005268C statement. Once the query there is a FETCH API_CURSOR query appearing in the expensive queries list the SQL server performance slowly degrades over time until they have to restart the SQL service. The executions per minute will fluctuate but can be 100,000s to millions of executions per minute.

Sometimes they run for months before this condition starts to happen, other times it occurs every couple of days.

By using an extended event session capturing both sp_statement_completed and cursor_execute events I believe I can determine the statement executing the cursor. I had their IT director setup an extended event session on their server. On one occasion we captured 2 executions of the cursor, all of the other traces do not show any matching executions of the cursor even though the IT director says the execution per minute in the expensive query list is increasing while the extended event session is running.

Is there anyway the statement would not appear in the session trace? Is there a better way to determine the statement the cursor is actually executing? Before using the extended event session we tried running this statement to identify the connection/session executing the cursor but it never returned results.

SELECT t.text, c.session_id
FROM   sys.dm_exec_connections c
        CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE 1=1
AND t.text LIKE ('API_CURSOR000000000005268C')

Here is an example screen capture of the expensive query list. enter image description here

They are running MS SQL Server 2012 SP1 (11.0.3128). Any suggestions or pointer would be appreciated.

My XE session is pretty wide open. Here is the script I had them run.

CREATE EVENT SESSION [TestSharpeSoft] ON SERVER 
ADD EVENT sqlserver.cursor_execute(
    ACTION(sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.module_end(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))) 
ADD TARGET package0.event_file(SET filename=N'D:\Temp\Sharpe\Trace\TestSharpeSoft'),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

One other note. This application is running at 100s of locations and this is the only customer reporting the problem.

3

Tracking cursors can be tricky. Joe talks about it in his blog post Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch

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

Alternatively, you can use sp_whoisactive @get_full_inner_text = 1 and you can log into a physical table for later analysis.

1

I'll make my comment an answer and get you started with a few thoughts.

The "FETCH_API_CURSOR" is part of the application using server side cursors to grab data. It's what happens when some APIs send server side cursosrs to SQL. You can learn more about the commands here.

You are probably running an expensive or top query extended event here. When you do that you are only seeing queries that pass a minimum expense threshold. So you won't see the prepare for this fetch, you'll just see the fetch.

Some are more expensive than others because the fetch can be for any number of queries being sent down.

It is quite hard to capture via trace or extended events because you have to have a wide open filter and that can be expensive. If you can catch a query as it is still live or in session, you can see the details about the running cursor using the sys.dm_exec_cursors DMV. This DMV accepts a SESSION_ID as an input argument and it returns useful information. You can join this with various other DMVs about executing queries to see more.

If you know the application from where these come, you can also try and filter for just that application or a host the application runs on or possibly do it in a dev environment where there is less noise.

  • Hi Mike, I am actually the developer of the application. Unfortunately, the language used is very cursor happy. We haven't been able to determine the session for the statement. – Rick-UpperPark Nov 23 '16 at 23:58
  • You can just join to dm exec requests on session I'd. – Mike Walsh Nov 23 '16 at 23:59
  • On session ID. Or supply a 0 for all. – Mike Walsh Nov 23 '16 at 23:59
1

This is just informational, testing showed it worked for me but I have not tested this on 2012 (only have SQL Server 2016 installed right now).

Confirmed the below XE session will work on SQL Server 2012 as well.

Test Scripts

I came across this article by Pinal Dave which shows a VBScript that can replicate the occurrence of the FETCH API_CURSORxxxx calls. I changed this into a PowerShell script to just run against my local instance.

Extended Event

Microsoft is adding new events to Extended Events with each version (and in some cases SPs). The events you are interested in to capture the query causing the cursor will be: sqlserver.cursor_close or sqlserver.cursor_open. I tried including the sqlserver.cursor_execute but that one did not show anything in my testing. I added specific actions for each of these events, taking out the attempt to use the cursor_execute. Your XE session definition would look like this:

CREATE EVENT SESSION [Track_api_cursor] ON SERVER 
ADD EVENT sqlserver.cursor_close(
    ACTION(sqlserver.database_name,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.cursor_open(
    ACTION(sqlserver.database_name,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[is_system]=(0)))
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=12 SECONDS,TRACK_CAUSALITY=ON)
GO

enter image description here

  • Thanks, Shawn. I'll look at adding those event to the session. If we restart the server and wait for the cursor to show up we should be able to find the original cursor open event. – Rick-UpperPark Nov 23 '16 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.