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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Many applications have a functional requirement for the underlying database to have a case-sensitive sort-order, implying that all character data related operations are case-sensitive. With SQL Server, such databases are created with either the ‘binary...