Sql-server – Expensive Query not showing in extended events trace

cursorsextended-eventssql serversql-server-2012

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.

Best Answer

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.

Related Question