SQL Server Cached Plan Reuse

SQL Server tries to reuse pre-computed execution plans from the cache when possible to speed up execution times. In today's episode, we examine what kinds of code characteristics will prevent SQL Server from reusing a plan from the execution plan cache.
Subscribe and turn on notifications to never miss a weekly video: www.youtube.co...
Check out the related blog post for all code samples:
bertwagner.com...
Follow me on Twitter:
/ bertwagner

[@walterferreiradossantos2378]

Very good examples. Thanks man.

[@zanonilabuschagne7628]

Wow, thanks Bert. Eye-opener of note. This is definitely something to watch out for!! Great vid, as always!!

[@user-tk2ef6cq5q]

Good one bert.. Keep up the good work..

[@bassfischer4273]

Thanks Bert and a question.. Are there particular characters or operators or anything syntactically that immediately causes a new plan to be generated, even if an identical plan with exactly the same characters and structure exists in the cache? I've got a parameterized (fully, correctly) query in a sproc that's spinning up new plans with every execution (same data type/length in the parameters) and I can't figure out how to get it to reuse. Wondering if there might be any 'poison' characters or syntax that are doing this just by being there.

[@RonaldJon12]

Bert are you still developing or are you fully focused on the CZcams channel? I started out in DTS and Cognos 7.2 but I still learn something new on every video.

[@stefanotorelli3688]

What about trivial optimization? ... and of course thank you for a very good video!

[@reverdk]

In 4:53 the query hash and query plan hash for the two queries are the same - which means the plan will be reused...?

[@jonathanbrotto7278]

Hence don't rebuild indexes too often?....