EXECUTE....... WITH RECOMPILE: Does it discard the previously cached plan?

Not an advance theory, everyone knows that first execution of the stored procedure creates the best plan for the execution and keeps it in the memory for the next execution. The plan is created based on the values passed to parameters with its first execution. This behavior is called as Parameter Sniffing and it is considered as a good thing as long as the cached plan is still good for parameter values passed with the consecutive execution. This behavior becomes bad when a completely odd value is passed for the parameter or the value of the parameter is vary with initial value. In a situation like this, we consider that the cached plan is not the best for this execution, hence we force SQL Server engine to recompile the plan with new values.

Forcing is done with RECOMPILE option. This option can be added to the stored procedure at its creation, resulting recompilation with all executions or can be added to the execute statement, resulting recompilation only with the current execution. Now the question is, when we force to recompile the stored procedure and generate a new plan by adding RECOMPILE option, does it discard the previously cached plan? Does it keep the new plan for next execution?

Let's test this. Following code adds a stored procedure and executes it three times. Then it checks cache.

view plainprint?

1. -- Clearing the cache  
2. DBCC FREEPROCCACHE  
3. GO  
4.   
5. USE AdventureWorks2014;  
6. GO  
7.   
8. -- Drop if it is exist  
9. IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL  
10.  DROP PROC dbo.GetOrderDetails;  
11. GO  
12.   
13. -- Create the procedure  
14. CREATE PROC dbo.GetOrderDetails @Number int  
15. AS  
16.     SELECT * FROM Sales.SalesOrderDetail   
17.     WHERE SalesOrderDetailID <= @Number  
18. GO  
19.   
20. -- Execute three times  
21. EXEC dbo.GetOrderDetails 1;  
22. GO 3  
23.   
24. -- Seeing the cache  
25. SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text  
26. FROM sys.dm_exec_cached_plans p  
27. CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t  
28. WHERE p.usecounts > 0   
29.  AND TEXT LIKE '%Sales.SalesOrderDetail%'  
30.  AND TEXT NOT LIKE '%dm_exec_cached_plans%'  
31. ORDER BY p.usecounts DESC;  

-- Clearing the cache
DBCC FREEPROCCACHE
GO

USE AdventureWorks2014;
GO

-- Drop if it is exist
IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL
 DROP PROC dbo.GetOrderDetails;
GO

-- Create the procedure
CREATE PROC dbo.GetOrderDetails @Number int
AS
    SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID <= @Number
GO

-- Execute three times
EXEC dbo.GetOrderDetails 1;
GO 3

-- Seeing the cache
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE p.usecounts > 0 
 AND TEXT LIKE '%Sales.SalesOrderDetail%'
 AND TEXT NOT LIKE '%dm_exec_cached_plans%'
ORDER BY p.usecounts DESC;

As you see, plan has been created with its first execution and it has been used three times. Now let's try to use a different plan for executing the procedure. Let's force engine to recompile and check the cache again.

view plainprint?

1. -- Execute with RECOMPILE  
2. EXEC dbo.GetOrderDetails 1000 WITH RECOMPILE;  
3. GO   
4.   
5. -- Checking the cache  
6. SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text  
7. FROM sys.dm_exec_cached_plans p  
8. CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t  
9. WHERE p.usecounts > 0   
10.  AND TEXT LIKE '%Sales.SalesOrderDetail%'  
11.  AND TEXT NOT LIKE '%dm_exec_cached_plans%'  
12. ORDER BY p.usecounts DESC;  

-- Execute with RECOMPILE
EXEC dbo.GetOrderDetails 1000 WITH RECOMPILE;
GO 

-- Checking the cache
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE p.usecounts > 0 
 AND TEXT LIKE '%Sales.SalesOrderDetail%'
 AND TEXT NOT LIKE '%dm_exec_cached_plans%'
ORDER BY p.usecounts DESC;

This shows the same plan and no new plan. What does it mean? If a new plan is created, why cannot we see it? If no new plan is created, has the old plan been used? If so, why we do not see an increase on usercounts for the plan? Answer is simple. RECOMPILE creates a new plan based on passed values but it does not keep it and it does not remove the old one from the cache. The newly created plan is only for this execution.