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. This post has been republished via RSS it originally appeared at: SQL Server Support articles.

Option recompile sql