CyberWomble (12/8/2011)
I have a couple of questions about this article:1.) Are the plans cached by sp_prepexec stored in the same place as the plans cached when using Stored Procedures?
2.) Do you think that it is a bug with SQL Server that plans created using sp_prepexec are not re-used across connections?
3.) Does the "per connection" plan re-use behaviour change between versions of sql server 2000/2005/2008?
4.) If you are calling SP's from ADO.net are the plans re-used across connections in a web application?
sp_PrepExec stores its plans in the same place. You can actually store your query in a @variable and run it through sp_PrepExec and sp_ExecuteSQL and see one reuse the others plan. It matters not whether the query is called from separate sessions or applications providing the plan is absolutely identical. Bear in mind the cache is case sensitive.
What little documentation for sp_PrepExec describes the use of its plan as being local but I found that it is the handle that is local and not the plan. If that's a bug then please Microsoft DON'T FIX IT!
I haven't tested the behaviour against SQL2000 or 2005. I probably should but as an architect I'm desparately trying to avoid encouraging the use of 7+ year old technology.