The following queries can be used to understand plan cache behavior. It should be noted that for systems with large plan caches, selecting all rows and joining with sys.dm_exec_sql_text is not recommended. To find the size of the plan cache use: select (sum(single_pages_kb) + sum(multi_pages_kb...

3.1 Costing algorithm change between SQL Server 2000 and SQL Server 2005 RTM In SQL Server 2000 and 2005, the cost of a query depends on 3 factors: number of disk IO requests, number of context switches during query compilation (in 4 ms quantums) and the number of memory pages, both from single...

Plan cache related performance problems usually manifest in decrease in throughput (or increase in query response time), and some times out of memory errors, connection time outs. None of these symptoms however point to plan cache related performance problems specifically. In order to determine and further...

If after upgrading from SQL 2000 to SQL 2005 RTM and/or SP1, you notice decreased OLTP database application throughput, bloated plan cache or out of memory errors, you will need to gather machine configuration information and system performance data in order to perform a detailed analysis. Below, we...

12.1 Trace Events 12.1.1 Performance Statistics Trace Event The performance statistics trace event introduced in SQL Server 2005, gives persisted plan and runtime statistics information. With the information part of the trace event data when combined with the information available through...

11.1 Temporary Tables versus Table Variables In order to determine if table variables or temporary tables is the best fit for your application, let us first examine some characteristics of table variables and temporary tables: 1. Table variables have a scope associated with them. If a table...

In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc freeproccache” or “dbcc freesystemcache” will clear the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and cause a sudden, temporary...

The procedure cache responds to memory pressure like all other cache stores built using the common caching framework. The eviction of cache entries under memory pressure is based on the cost of the entry (plan). When memory pressure conditions are reached, in one clock round zero cost entries are removed...

During batch compilation we make a determination regarding the cache-ability of the batch. Each statement in the batch is evaluated either as not-cacheable, cacheable, cache even though zero cost, set cost on re-use or not-cacheable due to sensitive information. A batch is marked cacheable if at least...

A uniform costing scheme has been implemented for all cache stores in SQL Server 2005. The cost of a query depends on 3 factors: number of disk IO requests, number of context switches during query compilation (in 4 ms quantums) and the number of memory pages, both from single and multi-page allocations...

In this section we will outline some programming practices for efficient plan cache usage: 6.1 Client Side Parameterization of Queries If your application has repeated execution of the same query with only parameter values changing from query to another, then parameterizing the query in the...

The compiled plan is generated for the entire batch (batch level) while individual statements in the batch have query plans (statement level) associated with them. We demonstrated earlier how to retrieve the compiled plan, and in this section we will examine to retrieve the query plans from the plan...

Using parameters or parameter markers in queries increases the ability of SQL Server to reuse compiled plans. There are two places where parameterization of queries can be done: on the client side application (or mid tier) or on the server side. 4.1 Client Side Parameterization Client side...

Each cache store is implemented as a hash table. A hash value and cache key pair is used in order to check if the compiled plan is cached for a given query in the cache stores. The hash value of all cache objects is computed as (database_id * object_id) mod (hash table size), and this specifies the hash...

2.1 What is a Plan_Handle Cached compiled plans are stored in the SQLCP or the OBJCP cache stores. A cached compiled plan can be retrieved from either of these cache stores using the plan_handle of the compiled plan. The plan_handle is a hash value derived from the compiled plan of the entire batch...

The plan cache is comprised of 4 separate cache stores: Object Plans (CACHESTORE_OBJCP), SQL Plans (CACHESTORE_SQLCP), Bound Trees (CACHESTORE_PHDR), and Extended Stored Procedures (CACHESTORE_XPROC). Each of these four cache stores conform to a uniform caching policy with respect to costing and removal...

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...

Procedure cache which is also called as plan cache is primarily a cache of the query plans to improve performance of query execution. By caching the compiled and execution plans we don’t have compile query each time they are executed. This is a huge performance boost and is therefore a very critical...

Plan guides are used to optimize the performace of a query without modifying it. They are intended for advanced users and help in situations where a query submitted through an application can not be changed. For more details on this feature please visit http://msdn2.microsoft.com/en-us/library/ms190417...