Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
This appears to work. Thanks for the tip. –  Travis Pettijohn Aug 26 '11 at 6:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.