Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?

Question

If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored procedure getting it's own execution plan?

If so, could this be potentially more globally efficient on my server if there are other ad-hoc queries that exactly match the dynamic SQL queries in my procedure (or even if there are other procedures that use dynamic SQL to call the same exact queries as well)?

Answer 1

Yes, the dynamic SQL you submit will be treated as ad-hoc. I.e., a hash is generated from the text, making it sensitive to everything (space, upper/lower, search arguments, etc).

If you happen to submit the exact same string as a regular ad-hoc (not dynamic sql from a proc), or from other procedures as dynamic SQL, then the plans can be re-used.

What is best for you, we can't say. An option for you is to use sp_executesql and parameterize your queries to allow for plan re-use. OTOH, you will now have parameter sniffing. Either "pay" for plan generation for every execution or re-use plans. Your choice.

• J.D.: Sorry I lost you on your last two sentences. Why would I automatically have parameter sniffing if I used dynamic SQL with sp_executesql? Are you saying using dynamic SQL with sp_executesql would result in plan generation for every execution? I thought it would be the opposite and increase the chances of re-use.
• Tibor Karaszi: If you use sp_executesql and parameterize your queries, then you will have plan re-use even with different parameters. The parameter values are sniffed. So you will have the advantages of plan re-use and also parameter sniffing (which can be both and advantage and a disadvantage).
• J.D.: Gotcha. If my query converts all parameters to locals before utilizing them (which usually fixes any disadvantages of parameter sniffing for me), would that also work in the dynamic SQL executed via sp_executesql?
• Tibor Karaszi: Yes. Assuming you consider parameter sniffing something to be "fixed" in the first place. :-) You can also set the db option to disable param sniffing, but that generates a different behavior. That causes SQL Server to treat every query as if you had "optimize for unknown".
• J.D.: This popped up on my radar again, realized I never accepted your answer. I appreciate the input! 🙂