26 performance tuning questions and solutions, Analyzing the SQL Server Plan Cache, Execution Plan Caching and Reuse, How to tune SQL queries, Improve SQL Server Performance by Looking at Plan Cache, Interview questions for SQL Server Performance Tuning, Introducing the SQL Server Plan Cache, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, plan cache in sql server, Plan Cache Object, Plan Caching in SQL Server 2008, Query Optimization, Query Performance Tuning, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Server Plan Cache, SQL SERVER Tips, SQL Tuning Overview, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases
What is Plan Cache? Why it is important?
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
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 --