Hopefully you never have to deal with determining the real procedure that is being called under-the-covers with Prepared Execution and sp_execute. If you don't understand the issues then this is a pretty good post to get you started. I've also written about this in detail many years ago. For standards reasons it seems like more JDBC drivers are making prepared execution the default (or only) method of making stored procedure calls.
The issue is what do you do when Support Guy calls you up and says, "I see 'sp_execute 70,param,param' is performing horribly in Profiler, can you fix it?" If the problem is already manifest at your customer sites you have a real problem. You really should have caught this in your performance tests, but hopefully even earlier when you defined your supportability goals.
- if your driver/application can turn Prepared Execution off, do it.
- if you can write a custom wrapper around the sql statements in your Data Access Layer to instead construct dynamic sql that generates an "friendly" RPC that you can see in Profiler. The biggest problem here is sql injection, remembering to double-up the single quotes, and all of the other issues with dynamic sql.
- Add an optional last parameter to all of your stored procedures called @ProcName. Change your data access layer to always pass the name of the procedure into the last parameter. In Profiler you'll see something like this 'exec sp_execute 70,param1,param2,'MyProcedure'.
- if you run a "24x7" sqltrace then (using the example above) find the most recent call to 'sp_prepexec 70' on the same spid and it will tell you the actual query.
- If the plan is still in cache you can probably get the information from the DMVs. Be aware...this method is not always reliable. DMV information is transient in nature. Try this:
cross apply sys.dm_exec_sql_text(plan_handle)
I'll expand upon all of these possible solutions in the future.