How SQL Server finds the Execution Plan in the Plan Cache Store

You know that SQL Server creates a plan for your query and keeps them in the cache for reusing when it is required. When you make a request again, how SQL Server knows that the plan is exist in the cache or not? What sort of techniques it uses for finding the plan? Let's try to understand it.

There are four main plan cache stores. These plan cache stores maintain different types of plans and plans are reused when the same request is made by users or plans are discarded when SQL Server needs space in the store (Let's discuss the techniques it uses for discarding with another post). These for stores are;

• Object plans store for storing plans for procedures, functions and triggers.
• SQL plans store for storing plans for ad-hoc queries.
• Bound Trees plan store for storing structures created by Algebrizer for views, defaults and constraints.
• Extended Stored Procedure plan store for storing plans for Extended Stored Procedures.

A store is a Hash Table and it contains series of Buckets. A Bucket contains zero or more plans and the Hash value is calculated;

• Using databaseId and objectId for Object plan, Bound Tree plan and Extended Stored Procedure plan stores.
• Using databaseId and the statement for SQL plan store.

The assignment of execution plan to store bucket is done using the Hash Value. The Plan Handle (or Cache Key) which is made up with various attributes, is used for referencing the plan in the cache.

When you make a request to SQL Server, it does following steps for find the plan in the cache;

1. Generate the Hash Value for the request for the appropriate store.
2. Search all execution plans in the bucket along with the cache key.
3. If found, it uses it, else create a new plan.

For understanding the process, let's check with a simple code;

-- Clear the cache
DBCC FREEPROCCACHE

-- Checking the number of buckets for each store
-- and how many have been used.
SELECT cc.name, buckets_count, buckets_in_use_count
FROM sys.dm_os_memory_cache_hash_tables AS ht 
 JOIN sys.dm_os_memory_cache_counters AS cc 
  ON ht.cache_address = cc.cache_address 
WHERE cc.name IN ('Object Plans','SQL Plans','Bound Trees','Extended Stored Procedures');

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate = '01/06/2014';

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate = '01/07/2014';

-- Check the cached plans
-- You will see two plans for above two statements
SELECT p.*, 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.SalesOrderHeader%'  
 AND text NOT LIKE '%SELECT p.*%'
ORDER BY p.usecounts DESC; 

-- Check how the cache key is formed
-- Note the two bucket ids
-- And note that only object id is different
-- For SQLs, it is the internal hash of the batch text
-- not an id in sys.objects table
SELECT p.bucketid, t.text  , ph.*
FROM sys.dm_exec_cached_plans p  
 CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t  
 CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS ph
WHERE --p.usecounts > 0 AND  
 text LIKE '%SELECT * FROM Sales.SalesOrderHeader%'  
 AND text NOT LIKE '%SELECT p.*%'
 AND is_cache_key = 1
ORDER BY p.usecounts DESC; 

How to fill the plan cache efficiently - Optimize for ad hoc workloads option

It is good to keep all plans used for compiled queries in the memory, as it helps to optimize the query processing, allowing engine to reuse the cached plan without recompiling query again for subsequent executions. But what if users frequently issue one-time-execute adhoc queries, and very rare to issue the same again? In that case, we should not waste the memory allocated to plans with them and should hold them only if they are executed again. How do we do it?

We can instruct SQL Server not to cache the plan for adhoc queries at the first execution but at the second execution using a server level setting. It is not recommend to change the default behavior but if we are opening the database for a similar situation, it may offer some performance benefit for other queries specifically with stored procedures.

This setting is called as optimize for ad hoc workloads. By default, it is disable, and it can be enabled passing 1 for this setting using sp_configure.

Here is the code for enabling it.

sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
GO

Now it is enabled. Let's run a query and see how it is cached.

-- 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, there is a record indicating that plan is cached but the type is different. It means that plan has not been fully cached and SQL Server will not be able to use it again for next execution. This small compiled plan stub helps to identify the query with next execution, and then plan is recreated and stored.

Execute the SELECT query again and see the plan cache.

It is cached. Execute again and see.

It is being reused now. This saves resources heavily but have to enable only if we are 100% sure that we always get dissimilar adhoc queries.

For more info on this setting, refer: https://msdn.microsoft.com/en-us/library/cc645587.aspx

Plan Caching in SQL Server 2008

I got many questions on the post I published “Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?”, seems many are interesting on it. While looking for some info related to it, I found an interesting technical article that speaks about Plan Caching written by Greg Low. Read it, it explains everything well;

http://msdn.microsoft.com/en-us/library/ee343986.aspx

Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?

While Preethi was presenting his presentation on Query Plans at the SQL Server Sri Lanka User Group meeting, he showed a situation where query plan gets recompiled when a value set for WHERE clause is vary. Although it recompiles, I doubt that it will not be the same if the query is a parameterized query with a stored procedure. There were couple of arguments on this favoring both sides; thought to test it and see.

Let me take simple two queries. The queries below are same except the value passed for the WHERE clause.

SELECT * FROM Sales.SalesOrderDetail 

    WHERE SalesOrderDetailID <= 1

SELECT * FROM Sales.SalesOrderDetail 

    WHERE SalesOrderDetailID <= 1000

SQL Server selects one plan for the first one but it does not use the same for the next though it is same.

This shows clearly that the statement has been recompiled since the value was different. But this is not going to happen if the logic is implemented with a stored procedure;

CREATE PROC GetOrderDetails @Number int

AS

    SELECT * FROM Sales.SalesOrderDetail 

    WHERE SalesOrderDetailID <= @Number

GO

The plan for the procedure will be generated at the first execution. Once generated, the generated plan will be used for next executions even though the value of parameters are vary greatly. See the code below;

EXEC GetOrderDetails 1

EXEC GetOrderDetails 1000

 
You can see that the same plan which was generated for the first execution has been used for second execution even though it is not the best plan for the value 1000. All we have to remember is, stored procedures are not getting re-compiled when values of parameters are vary. If you expect a situation like this, use WITH RECOMPILE with the stored procedure. This instructs SQL Server to not to stored the plan generated, hence it creates a plan at every execution. If it is rare, use WITH RECOMPILE when you call the stored procedure. This creates the plan again when it is executed.

ALTER PROC GetOrderDetails @Number int

WITH RECOMPILE

AS

...

-- OR

EXEC GetOrderDetails 1000

WITH RECOMPILE

The next is whether the stored procedure gets recompiled when an index is added to a table used in it. See what BOL says;

It clearly says that the stored procedure will not get recompiled when an index is added to a table used with it. But what I found is the opposite of it. Sometime one of the statements gets compiled, sometime the entire stored procedure gets compiled. This is how I tested;

This code creates a new table and then creates a stored procedure. The execution of the stored procedure creates a plan and it is used for subsequent execution.

SELECT *

INTO TempSalesOrderDetail

FROM Sales.SalesOrderDetail

CREATE PROC GetTempOrderDetails

AS

    SELECT SalesOrderDetailID FROM TempSalesOrderDetail 

    ORDER BY SalesOrderDetailID

GO

EXEC GetTempOrderDetails 

Here is the generated plan for it.
 
As per the BOL, stored procedure does not get recompiled when an index is added…

CREATE INDEX IX_1 ON TempSalesOrderDetail (SalesOrderDetailID)

GO

EXEC GetTempOrderDetails 

Here is the plan now.

Now the plan is different, means the stored procedure has been recompiled. There might be a situation where the behavior as same as BOL says, will see whether we can find a such situation.