I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form
exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL Management Studio window, I get an error
Could not find prepared statement with handle 1073742882. I'm not sure, but it seems that a cached plan is connection-specific. I have no
sp_cursorprepare in my trace; repeating the use case reveales the same prepared handle ID with a new cursor. Since it's a shared environment I'm connecting to, I think I'd have to reset the app server and trace its startup in order to see it.
- Is there any way to use the cursor's prepared handle ID to see the underlying query?
- Is there any way to correlate a row in
dm_exec_cached_plansto this cursor?
- Is there any way to see the execution plan via
dm_exec_query_planor some other way?