Fetch API_CURSOR

Views (2964)

Christian Silva

707

Like(0)

Report

In Ax 2012, it is common to track sql queries to improve performance, to create new indexes and reduce locking. But sometimes the session that is locked and have a issue with time execution is in a Fetch API_Cursor000000666 or something in these lines. So there are some tricks as to how to check in content in this query.

First check the session number where this is running under sp_who2 in sql , then use this number to find out what is running in this query.For the example we used the session 55

SELECT result.text
FROM sys.dm_exec_connections dec
CROSS APPLY sys.dm_exec_sql_text (dec.most_recent_sql_handle) result
WHERE session_id = 55

SELECT dec.session_id, dec.properties, dec.creation_time, dec.is_open, result.text
FROM sys.dm_exec_cursors (55) dec
CROSS APPLY sys.dm_exec_sql_text (dec.sql_handle) result

I hope that it helps in your tunning, also check this amazing link that gives a lot of info on how to deal with cursor fetch

http://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/