What is the best way to see the query behind a cursor in Top SQL Reports coming from SQL Server

(0) Share

Report

Posted on by John_Castro

I have a SQL report coming from a 3rd party application, reporting that the following function has long waits. I have also seen something similar from Dynamics AX X++ SQL (Application) Logging. What is the best way for me to find the SQL query behind this cursor?

FETCH API_CURSOR0000000000688C29

Thank you,

John

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

John_Castro on at

Like (0)

Report

RE: What is the best way to see the query behind a cursor in Top SQL Reports coming from SQL Server

Thank you, i fell across the same article and found it very useful. In addition to the mentioned query, the following shows all active cursors.

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

Was this reply helpful? Yes No
Denis Macchinetti 16,444 on at

Like (1)

Report

RE: What is the best way to see the query behind a cursor in Top SQL Reports coming from SQL Server

No issue -))

"Here" we are in many with different knowledge.

So, it's better clarify according the Topic.

That's it!

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

RE: What is the best way to see the query behind a cursor in Top SQL Reports coming from SQL Server

yes

I understood it wrongly :-)

Was this reply helpful? Yes No
Denis Macchinetti 16,444 on at

Like (0)

Report

RE: What is the best way to see the query behind a cursor in Top SQL Reports coming from SQL Server

Hi Manish

John is talking about a SSRS report coming from a 3rd party Application so, the feature that you mention doens't help.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

RE: What is the best way to see the query behind a cursor in Top SQL Reports coming from SQL Server

If you want to check long running queries in AX, you may enable SQL statement trace log under user options >> SQL >> SQL Trace.

Mention the long queries threshold in milli seconds and enable Table(database).

All the long running SQL queries beyong the mentioned threshold will be captured in the SQL statement trace log under System Admin >> Inquiries >> Database >> SQL Statement trace log.

Was this reply helpful? Yes No
Verified answer

Denis Macchinetti 16,444 on at

Like (1)

Report

RE: What is the best way to see the query behind a cursor in Top SQL Reports coming from SQL Server

Hi John

The API cursors are used to fetch data rows from the server.

Take a look to this post www.sqlskills.com/.../hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch

You have to run the below query and indicate the SQL Session ID

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text

FROM sys.dm_exec_cursors (SESSION_ID) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

Finally, check the expensive queries on SQL Activity Monitor. Maybe it help you.

Was this reply helpful? Yes No