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.

view plainprint?

1. -- Cleaning buffer  
2. DBCC FREEPROCCACHE  
3. DBCC DROPCLEANBUFFERS  
4. GO  
5.   
6. -- Simple adhoc query with a filter  
7. SELECT * FROM Sales.SalesOrderDetail  
8. WHERE SalesOrderDetailID = 1;  
9.   
10. -- Checking chached plans  
11. -- We should see one record for above query  
12. SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text  
13. FROM sys.dm_exec_cached_plans p  
14. CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t  
15. WHERE p.usecounts > 0 AND  
16. TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'  
17. ORDER BY p.usecounts DESC;  

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

view plainprint?

1. -- Executing the same   
2. -- and with two different values  
3. -- Run these SELECT separately  
4. SELECT * FROM Sales.SalesOrderDetail  
5. WHERE SalesOrderDetailID = 1;  
6.   
7. SELECT * FROM Sales.SalesOrderDetail  
8. WHERE SalesOrderDetailID = 1000;  
9.   
10. SELECT * FROM Sales.SalesOrderDetail  
11. WHERE SalesOrderDetailID = 100000;  
12.   
13. -- Check cached plans again  
14. SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text  
15. FROM sys.dm_exec_cached_plans p  
16. CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t  
17. WHERE p.usecounts > 0 AND  
18. TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'  
19. ORDER BY p.usecounts DESC;  

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