1/15/2023 0 Comments With recompile![]() ![]() 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |