Plan Caching and OPTION (RECOMPILE) hint

1

I am trying to understand what gets cached in a stored procedure, when the stored procedure has (for example) 3 query statements, and one of those query statements has an OPTION (RECOMPILE) hint?

Let's say that the OPTION (RECOMPILE) hint is on Query statement #3. While Query statements #1 and #2 do not have any hints and nothing is inhibiting statements #1 and #2 from being cached.

In the stored procedure plan that gets cached, do we find Query statements #1 and #2, but NOT #3?

2

Right, #3 will not be there, because of the RECOMPILE hint.

The others may or may not be there. SQL has other criteria it uses to determine to cache a plan or not. Generally more complex plans will be cached, but you can't be 100% sure.

2 Likes