2

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)?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
5
  • 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.
    – J.D.
    Nov 29, 2019 at 18:00
  • 1
    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).
    – Tibor Karaszi
    Nov 29, 2019 at 18:07
  • 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?
    – J.D.
    Nov 29, 2019 at 18:39
  • 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".
    – Tibor Karaszi
    Dec 1, 2019 at 15:42
  • This popped up on my radar again, realized I never accepted your answer. I appreciate the input! 🙂
    – J.D.
    Mar 8, 2021 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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