View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

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_plans to this cursor?

• Is there any way to see the execution plan via dm_exec_query_plan or some other way?

I've not had to track down cursor activity since SQL2000 i.e. pre-DMV days. The old way would still be viable I assume, use profiler and include execution plans in the trace.

I can't remember if the plan will be included in the sp_cursorexecute call or if you need to go back through the trace and find the sp_cursorprepexec or sp_cursorprepare event associated with the handle.

StackExchange Database Administrators Q#5013, answer score: 2