In other words, the originally cached plan remains (if there was one) while this separate plan is created and destroyed just for this one execution. And if a plan did get placed in cache prior to using WITH RECOMPILE option, it never gets touched by running the query WITH RECOMPILE. What actually happens when you use WITH RECOMPILE or OPTION (RECOMPILE) is that a new, temporary, "private" plan gets created just for that execution of the query and once the execution completes that plan is discarded and is never cached. In other words, the name leads you to believe that a query plan that is already sitting in procedure cache and is ready to be reused gets dropped out of cache and a new plan is "installed" in its place. The confusion with this option (introduced by its name I think) is that it causes a plan already in cache to be recompiled or recreated. The way this is accomplished is sometimes a source of confusion. These two options are designed primarily for one purpose: deal with issues arising from having a single compiled plan in cache which may not be suited for all parameter values of a query (the atypical parameter problem: see here and here ). WITH RECOMPILE or OPTION (RECOMPILE) Explained This happens for multiple reasons: a stored procedure was altered, sp_recompile was executed on a stored procedure/function, etc. Cache Remove - removed/dropped a plan from plan cache.Will likely be followed by a Cache Insert
Cache Miss - did not find a plan in cache.Cache Hit - found a matching/existing plan in plan cache and therefore will re-use it.Cache Insert - create/insert a new plan in plan cache.Plan Cache Events: Insert, Hit, Miss, RemoveĪllow me to introduce a few more terms here to help with the explanation. For more information see SP:Recompile event (EventSubClass section) "Automatic" Recompiles - occurs due to statistics/data change, schema change, deferred name resolution, SET option changes, and so on. In my mind this is the "proper" use of the term "recompile"ģ. sp_recompile and automatic recompile - in these contexts, the term is used to indicate that a plan resident in cache will be dropped and recreated (i.e. Or if a plan does exist due to prior caching, it is not affected by this option.Ģ. In my mind this is confusing because RE-compile suggests that a "compile is performed again on an existing compiled plan" yet no plan exists. WITH RECOMPILE or OPTION (RECOMPILE) - in this context a new plan is compiled but not cached when a query runs. Here are three uses of the term "recompile":ġ. I wanted to make this post because I think the term “recompile” is used to describe a few slightly different concepts in SQL Server.
0 kommentar(er)
