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.1 Types of Cache Objects
1.2 Understanding Memory Layout of Compiled Plans using DMV’s
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
3.1 Cache Look Up Mechanisms
3.2 Cache Keys
4.1 Client Side Parameterization
4.2 Server Side Parameterization: Simple Parameterization
4.3 Server Side Parameterization: Forced Parameterization
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
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
9.1 Local Memory Pressure
9.2 Global Memory Pressure
11.1 Temporary Tables versus Table Variables
11.2 Recompiles Based on Temporary Tables
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.1 Understanding Machine Configuration
1.2 SQL Server Configuration Options
1.3 Database Options
1.4 Workload Characteristics
2.1 How to Analyze Wait Types
2.2 How to Analyze Spins/Collisions data
2.3 Plan Cache Size and Database Pages Size
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.