I wanted to make this post because I think the term “recompile” is used to describe two slightly different concepts in SQL Server. Here are the two 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"
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, in fact 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 really never cached. And if a plan did get placed in cache prior to using WITH RECOMPILE option, it never gets touched. 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
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
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.
select cacheobjtype, refcounts, usecounts, size_in_bytes, objectid, dbid from sys.dm_exec_cached_plans cross apply sys.dm_exec_query_plan(plan_handle)
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.
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).
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 or a trigger causes them to be recompiled the next time they are executed. When sp_recompile is run on a table or a view, all of the stored procedures 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.
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).