Share via

Plan Cache Concepts Explained

  • Article

Since the release of SQL Server 2005 there have been several questions around how plan caching has been implemented in this release and how to diagnose (and distinguish) plan cache related performance problems. In a series of blog articles we will attempt to address many of these questions. Lists of topics that will be covered in these blog articles are as below:

  1. Structure of the Plan Cache and Types of Cached Objects

1.1 Types of Cache Objects

1.2 Understanding Memory Layout of Compiled Plans using DMV’s

  1. Sql_Handle and Plan_Handle Explained

2.1 What is a Plan_Handle

2.2 What is a Sql_Handle

2.3 Sql_Handle : Plan_Handle :: 1 : N

2.3 Plan_Handle, Sql_Handle and Plan Cache DMV’s

  1. How Cache Lookups Work

3.1 Cache Look Up Mechanisms

3.2 Cache Keys

  1. Query Parameterization

4.1 Client Side Parameterization

4.2 Server Side Parameterization: Simple Parameterization

4.3 Server Side Parameterization: Forced Parameterization

  1. Retrieving Query Plans from Plan Cache DMV’s

5.1 Query Plans for Safe Auto-Parameterized Queries

5.2 Query Plans for Unsafe Auto-Parameterized Queries

5.3 Query Plans for Multi-Statement Batch

5.4 When and How to Use Sys.dm_exec_text_query_plan

  1. Best Programming Practices

6.1 Client Side Parameterization of Queries

6.2 Use Fully Qualified Names

6.3 Use RPC Events over Language Events

6.4 Choose Suitable Object Names

6.5 Exact Match of High Re-use Adhoc Query Text

  1. Costing Cache Entries
  1. Factors that affect Batch Cache-ability
  1. Memory Pressure Limits

9.1 Local Memory Pressure

9.2 Global Memory Pressure

  1. Plan Cache Flush
  1. Temporary Tables, Table Variables and Recompiles

11.1 Temporary Tables versus Table Variables

11.2 Recompiles Based on Temporary Tables

  1. Plan Cache Trace Events and Performance

12.1 Trace Events

12.2 Performance Counters

In addition to these articles, we will also post another series of blog articles specifically focused on trouble shooting query performance issues related to plan cache in SQL Server 2005 RTM and SP1. The topics covered will include:

  1. Machine Configuration Information That Can Impact Plan Cache Size/Performance

1.1 Understanding Machine Configuration

1.2 SQL Server Configuration Options

1.3 Database Options

1.4 Workload Characteristics

  1. Diagnosing Plan Cache Related Performance Problems and Suggested Solutions

2.1 How to Analyze Wait Types

2.2 How to Analyze Spins/Collisions data

2.3 Plan Cache Size and Database Pages Size

  1. Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2

3.1 Costing algorithm change between SQL Server 2000 and SQL Server 2005 RTM

3.2 Improvements made to Plan Cache behavior in SQL Server 2005 SP2

  1. Useful Queries on DMV’s to understand Plan Cache Behavior

If you have additional questions relating to the plan cache please post comments on this blog and we will try to answer them as best as possible. Also comments/suggestions on what other plan cache related topics you would like us to cover would be useful.

 

- Sangeetha Shekar

Comments

  • Anonymous
    January 16, 2007
    and how its managed, and how its different in RTM, SP1 and SP2? Start here , it took me 2 nights to read

  • Anonymous
    February 14, 2007
    It would probably a good idea to update this entry with URL's of all the points. That way I won't need to do the work of taking this post, adding all URL's and putting on my team's blog :) Great work, Gaurav