This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
First published on MSDN on Jan 01, 2013
I wanted to make this post because I think the term “recompile” is used to describe a few slightly different concepts in SQL Server. Here are three uses of the term "recompile":
1. WITH RECOMPILE or OPTION (RECOMPILE) - in this context a new plan is compiled but not cached when a query runs. 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. Or if a plan does exist due to prior caching, it is not affected by this option.
2. 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. compiled again or recompiled ). In my mind this is the "proper" use of the term "recompile"
3. "Automatic" Recompiles - occurs due to statistics/data change, schema change, deferred name resolution, SET option changes, and so on. For more information see SP:Recompile event (EventSubClass section)
Plan Cache Events: Insert, Hit, Miss, Remove
Allow me to introduce a few more terms here to help with the explanation.
- Cache Insert - create/insert a new plan in plan cache
- Cache Hit - found a matching/existing plan in plan cache and therefore will re-use it
- Cache Miss - did not find a plan in cache. Will likely be followed by a Cache Insert
- Cache Remove - removed/dropped a plan from plan cache. This happens for multiple reasons: a stored procedure was altered, sp_recompile was executed on a stored procedure/function, etc.
WITH RECOMPILE or OPTION (RECOMPILE) Explained
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 ). The way this is accomplished is sometimes a source of confusion. 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. 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. 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. 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 . 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. 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. However, if you were tracing this, you would see a SP:Recompile event with event subclass = 11 (Option (Recompile) requested). If the query is always executed with OPTION (RECOMPILE), no plan will to be found in plan cache.
From MSDN article : "Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed". Technically the document should have stated that "the stored procedure is compiled every time it gets executed", not "recompiled", in my opinion. Unfortunately, the name WITH RECOMPILE is often misconstrued to mean that the plan in cache is actually dropped and recreated, whereas the recompilation in this case simply points to the fact that a new plan is compiled for each execution. As far as I am concerned less confusing names should have been given to this option. Here are a few less-than-perfect suggestions to help drive the concept home: WITH TEMP_COMPILE, WITH PRIVATE_COMPILE, WITH COMPILE_NO_CACHE.
Here is a demo of this concept
1. Create a simple stored procedure
use tempdb
go
create procedure test_p1
as select 1
2. Let's execute the procedure using WITH RECOMPILE and check procedure cache for any entries.
exec test_p1 with recompile
go
select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_query_plan(plan_handle)
where objtype='proc' and objected = object_id('test_p1')
We find no entries in cache for this procedure because no plan was placed there permanently.
( 0 row(s)affected)
3. If we execute the procedure "normally" will get one entry in cache. Note that usecounts = 1 indicating the plan was used once.
exec test_p1
go
select cacheobjtype, refcounts, usecounts, size_in_bytes, objectid, dbid from sys.dm_exec_cached_plans cross apply sys.dm_exec_query_plan(plan_handle)
where objtype='proc' and objected = object_id('test_p1')
4. The next step aims to demonstrate the store procedure usecounts increases based on number of executions/uses. After 3 more executions usecounts=4 now.
exec test_p1
go
exec test_p1
go
exec test_p1
go
select cacheobjtype, refcounts, usecounts, size_in_bytes, objectid, dbid from sys.dm_exec_cached_plans cross apply sys.dm_exec_query_plan(plan_handle)
where objtype='proc' and objected = object_id('test_p1')
5. Finally, if we re-execute the procedure using WITH RECOMPILE, we will observe, as expected, that usecounts did not change. This indicates that the plan in cache was not only not used, but was also not dropped and recreated (not recompiled).
exec test_p1 with recompile
go
select cacheobjtype, refcounts, usecounts, size_in_bytes, objectid, dbid from sys.dm_exec_cached_plans cross apply sys.dm_exec_query_plan(plan_handle)
where objtype='proc' and objected = object_id('test_p1')
For comparison, here are the uses of recompile that I deem more appropriate.
Manual Recompiles – SP_RECOMPILE (description is from white paper )
Running sp_recompile on a stored procedure, function or a trigger causes it to be recompiled the next time they are executed. When you execute sp_recompile on a stored procedure/trigger/UDF, that object is immediately removed from plan cache (Cache Remove). Therefore a subsequent execution of that object finds no plan in cache which triggers a Cache Miss followed by a Cache Insert.
When sp_recompile is run on a table or a view, all of the stored procs/triggers/UDFs that reference that table or view will be recompiled the next time they are run. sp_recompile accomplishes recompilations by incrementing the on-disk schema version of the object in question. You will see an SP:Recompile event with event subclass =1 (Schema Changed).
"Automatic" Recompiles
As you know there are conditions that cause an "automatic" recompile of queries - schema change, statistics change, SET options changed, etc (for a complete list see SP:Recompile event and KB 308737 . In SQL 2000, an entire batch (stored procedure, trigger, UDF) would get recompiled, whereas in SQL 2005 and later, the automatic recompiles, only dropped and recreated a plan for a specific statement. Again here, the term recompile means that a plan in cache is dropped and recreated. The SP:Recompile Profiler event keeps track of these: again note that In SQL Server 2005 and later, recompilations reported by SP:Recompile event apply to statement level recompile, whereas those in SQL Server 2000 occurred at the batch level (entire procedure for example).
Namaste
Joseph