Therefore, there is no RE-compile here in the true sense of a the word, it is really a sequence of Cache Insert and Cache Remove event under the cover. 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":ġ. For more information, see Recompile a Stored Procedure.I wanted to make this post because I think the term “recompile” is used to describe a few slightly different concepts in SQL Server. Also use OPTIMIZE FOR when you create plan guides. OPTIMIZE FOR can counteract the optimizer's default parameter detection behavior. The data type of must be implicitly convertible to the data type that references in the query. can be of any SQL Server system data type that can be expressed as a literal constant. is used only during query optimization, and not as the value of during query execution. Is a literal constant value to be assigned for use with the OPTIMIZE FOR query hint. Specifies that the Query Optimizer uses statistical data instead of the initial value to determine the value for a local variable during query optimization. The value is used only during query optimization, and not during query the name of a local variable used in a query, to which a value may be assigned for use with the OPTIMIZE FOR query hint. Instructs the Query Optimizer to use a particular value for a local variable when the query is compiled and optimized. Transact-SQL Syntax Conventions Syntax ::= Because the SQL Server Query Optimizer typically selects the best execution plan for a query, we recommend only using hints as a last resort for experienced developers and database administrators.
0 kommentar(er)
