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.

-- 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.

-- 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.

How to check parameters values used for compiling the stored procedure?

It is a known fact that the initial plan of the stored procedure is created with its first execution. SQL Server engines creates the plan based on the values passed for parameters with stored procedure's first execution and places the plan in the cache. This plan will be reused with consecutive executions regardless of the values passed to parameters. This behavior is called as Parameter Sniffing, and it helps to improve the overall performance of the stored procedure's execution as well as downgrade the performance of it. For more info on this, read one of my old post: Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?

With consecutive executions, if you want to see the values that have been used for compiling the procedure, or in other words, parameters values used for the plan in the cache, what is the easiest way of seeing it? Simplest way is, get the Properties of last component in the plan and see Parameter List property. Here is the way of doing it;

First, let's create a procedure and execute once.

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 once. This adds the plan which is optimized for 
-- parameter value 1.
EXEC dbo.GetOrderDetails 1;

Now let's execute it with a different value enabling Include Actual Execution Plan.

Open the Execution Plan tab and click on SELECT.

Properties window can be opened by either pressing F4 or Right-Clicking and Selecting Properties. Once it is opened, You will see property called Parameter List and it has the values used for compiling the stored procedure and values used for current execution.

Is this useful? Yes, if you are planning to recompile or want to know whether the plan is optimized for new executions, this holds some of required information you need on it.

SQL Server Brain Basher of the Week #005

Can I use sp_recompile with tables?

What would be your answer? Yes or no?

Answer should be Yes but it does not mean that we can recompile tables using this procedure.

Generally we use sp_recompile for invalidating existing plans in the cache for stored procedures, triggers or user-defined functions and plans will be regenerated with next run. This requires one parameter: Name of the procedure, user-defined function or trigger. Remember, this does not recompile procedures, triggers, and user-defined functions, it just marks existing plans as invalid. New plan will be created when we use the object again.

This allows us to pass a table name or view name as the parameter value. If the parameter value is either a table name or view name, it invalidates all existing plans of procedures, user-defined functions and triggers that have reference to the table or view. Similarly, new plans will be generated at the next run of referenced objects.

Let's test this. Execute the first statement few times and then execute the next statement. First statement executes a stored procedure in AdventureWorks2014 databases and second statement checks for Compiled Plans in the Cache.

-- Cleaning buffer
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
   
USE AdventureWorks2014;
GO

-- First staement
-- Execute this three times
EXEC dbo.GetFrieghts 2011

-- Second statement
-- We should see 3 counts on compiled plan
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
 TEXT LIKE '%GetFrieghts%';

Now, let's call sp_recompile for the table Sales.SalesOrderHeader which has been referenced by dbo.Getfrieghts procedure.

EXEC sp_recompile N'Sales.SalesOrderHeader';

Once done, execute the stored procedure once and see whether engine uses the same plan or a new plan.

-- Execute again - separately
EXEC dbo.GetFrieghts 2011

-- Check compiled plans
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
 TEXT LIKE '%GetFrieghts%';

Surprise? Has the plan been invalidated and new plan has been created? It looks like no, old plan has been used again. Though we see the result looks like that, the reality is different. It has recompiled the stored procedure but not fully, only statements that have reference to the table. Let's test it.

Execute the first code set again. Once executed we have a compiled plan in the cache and it has been hit 3 times. Next, start the Profiler and add SP:CacheHit and SP:Recompile.

Run the Profiler. Execute the second code set that calls sp_recompile with the table and execute the stored procedure again.. Once executed, check the Profiler. This is what you should see.

As you see, two statements that have a reference to the table have been recompiled. Since the entire stored procedure has not been recompiled, the count of old compiled plan is getting increased, but remember, few parts of the procedure has been recompiled.

Does SQL Server use same cached plan for same statement with subsequent execution?

When you send a request to SQL Server, it takes your statement via four main steps. Generating a plan for it is part of these four steps. These steps are called as: Parsing, Normalization, Compilation, and Optimization. Parsing makes sure the query syntax is correct and reforms the query for compilation. Normalization checks all the object that have been referenced in the query for existence. This is step that throws errors such as Invalid object, Object not found. Compilation generates multiple plans for the query and Optimization picks the best and places it into the buffer.

Now the question is, once the plan is generated for the query, will it be reused with subsequent execution. Most of the time, we expect the plan to be reused but it is not always the case. Here is an example for it;

This adhoc statement queries against Sales.SalesOrderDetail table in AdventureWorks2014, filtering SalesOrderDetailID to 1. Do not execute queries together, execute one by one without selecting comments.

-- Cleaning buffer
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Simple adhoc query with a filter
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1;

-- Checking chached plans
-- We should see one record for above query
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 '%SELECT * FROM Sales.SalesOrderDetail%'
ORDER BY p.usecounts DESC;

As you see we have one cached plan for the query executed. Let's run the query again with the same filter and with some different filter values. Again, run these queries separately.

-- Executing the same 
-- and with two different values
-- Run these SELECT separately
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1;

SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1000;

SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 100000;

-- Check cached plans again
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 '%SELECT * FROM Sales.SalesOrderDetail%'
ORDER BY p.usecounts DESC;

Can you see how the plan has been reused. SQL Server has reused the plan only for the first statement and for other statements, it has created new plans even though the query is same. How this can be happened?

This is not an issue or bug, this is how it compiles queries. When SQL Server receives a query, it checks the cache for plans by comparing the entire statement. As you see, even though the statement is same, it creates a new plan because statement does not match with previous one (because of the filter value). So, keep this in mind, if the query is lengthy and complex, the time it takes for executing will be always same when filter values are different, even though you expect it to be optimized by reusing the plan. This is one of reasons for recommending Stored Procedure for queries that are executed frequently with different filters.