What is Plan Cache? Why it is important?

Download PDF – Plan Cache

Let’s go through some basic notes about Plan Cache –

1. Well SQL Server creates execution plans for queries and stores them in a location of memory called Plan cache. Plan cache uses some part of the memory which is allocated to Buffer Pool.

2. Prior to SQL 2005 it was called Procedure Cache.

3. Now the question is that why we are saving these execution plans- See for SQL Server it expensive to go through all the steps that are required to generate execution plans each time the query is submitted. While SQL Server can do all this in less than a millisecond, depending on the query it can take seconds or even minutes to create an execution plan, so SQL Server will keep plans in Plan cache for further usage.

4. Execution plans are stored in 4 different memory areas.
a. CACHESTORE_OBJCP – Stored Procedures, Triggers and Functions
b. CACHESTORE_SQLCP – Used for Ad-hoc SQL queries
c. CACHESTORE_PHDR – Used for View, Constraints and defaults.
d. CACHESTORE_XPROC – Used for Extended Stored Procedures.

5. Each plan cache store contains a hash table with full of buckets.

6. Plans are stored in each bucket based on simple hash algorithm.
a. The algorithm is (Object_id * Database_Id) mod (Hash Table Size)

7. Whenever you get a query to execute SQL Server check the plan cache if the execution plans exists in the plan cache.

It checks this using Hash Key + Cache Key

8. How to check whether query execution plan exists in the plan cache or not
a. SELECT * FROM sys.dm_exec_cached_plans

9. It is one of the great strengths of SQL Server, since it reduces the expense of creating plans.

Please note that SQL Server does not keep execution plans in memory forever.

11. Compilation means Plan creation. You will get a new plan.

12. Recompilation means existing plan cannot be used any more so a new one is made.

13. Recompilations happens in below cases- (Not all)
a. If there are any schema related changes
b. sp_recompile on a table or view
c. Changing the set options with in the execution of the query
d. Changing/updating/Deleting Index
e. Updating Statistics manually or via automated means
f. Stale Statistics deleted – 20% of rows + 500 new rows added.
g. Manual recompile
h. With Recompile Option in a stored procedure. 2 types of recompile options are available – Statement level (Recompile query hint @ statement level) and procedure level (With Recompile).

14. Let’s say you don’t want to recompile. There are some ways to achieve this.
a. Trivial Plan
b. If you have used OPTION hint KEEPFIXED Plan
c. Automatic statistics updates have been disabled

How to clear Plan Cache
DBCC FREEPROCCACHE

a. Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

--

USE AdventureWorks2012;
GO

SELECT * FROM Person.Address;
GO

SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO

--