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.

Check out the related blog post for all code samples:

Follow me on Twitter:

[zanonilabuschagne]

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

[bassfischer]

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.

[tkefcqq]

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

[RonaldJon]

Bert are you still developing or are you fully focused on the YouTube channel? I started out in DTS and Cognos 7.2 but I still learn something new on every 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...?

[stefanotorelli]

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

[jonathanbrotto]

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