Tracing FETCH API_CURSOR

Question

Tracing FETCH API_CURSOR

pkemp

Old Hand

Points: 345
More actions
January 10, 2013 at 9:43 pm

#272722

Hi all
I'm wondering if somebody can help with some ideas on tracing server side cursors? I'm currently trying to tune our Dynamics AX database and have identified via SQL profiler a high number of "FETCH API_CURSOR0000000000008FA4" and corresponding "exec sp_cursorfetch 182347859,2,1,1" type statements that are responsible for billions of reads. I'm aware that I can see the statement being executed by running the following TSQL:
[font="Courier New"]SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (53) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t [/font]
which is all good as long as the cursor is being executed at the time I run the script.
What I'm hoping to do is expose the query being run with either SQL Profiler or a server side trace - is this at all possible?
Thanks for any help!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

No. But there is a dynamicsperf add on that traps this information. If you have that installed, dynamics may be writing that info out to the dynamicsperf database so you can see historically the query performed by the cursor.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pkemp Old Hand Points: 345 More actions · Answer 2

Thanks for the info Jason, I'll definitely be trying this out.

Regards

Peter

Degradable SSC Veteran Points: 250 More actions · Answer 3

Peter

Any chance you can update this call on what add-on and if you found an offending process / SP.

I am suffering from a performance issue in SQL 2008 R2 on a single run of a batch process (Run through Navision Client). Like you I see masses of CursorFetch activity but have no idea what is going on.

Would be interested in what you found out.

Regards

pkemp Old Hand Points: 345 More actions · Answer 4

Here's a link to the Dynamicsperf download for SQL2008:

http://archive.msdn.microsoft.com/DynamicsPerf

It covers multiple Dynamics products eg AX, NAV, CRM etc. There's a bit of setup to get it up and running which I'm only part way through at the moment. It looks quite comprehensive in what it's capable of but I think you need a decent chunk of time to get it setup and understand the output it produces.