SQL Caching

There are several goals of this white paper. This paper explains how SQL Server 2005 and SQL Server 2008 allocate memory for plan caching, how query batches are cached and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations and for minimizing plan cache pollution. The white paper explains SQL Server's "statement-level recompilation" feature (first introduced in SQL Server 2005) and many tools and utilities that are useful as observation tools in the processes of query compilation, query recompilation, plan caching, and plan reuse.

All material in this white paper applies to both SQL Server 2005 and SQL Server 2008 apart from those areas that are explicitly pointed out as applying to one or the other. This white paper is an update of the white paper “Batch compilation, Recompilation and Plan Caching Issues in SQL Server 2005” by Arun Marathe. For comparisons with SQL Server 2000, readers should refer to that white paper which can be found at 

This paper targets these audiences:

• Users: Persons who use, maintain, and develop applications for SQL Server. Users who are new to SQL Server 2008 and those who are migrating from SQL Server 2005 will find useful information here.
• Developers: SQL Server developers will find useful background information here.

Recompilations: Definition

Before a query, batch, stored procedure, trigger, prepared statement, or dynamic SQL statement (henceforth, "batch") begins execution on a SQL Server, the batch gets compiled into a plan. The plan is then executed for its effects or to produce results. A batch can contain one or more SELECT, INSERT, UPDATE, DELETE and MERGE statements; and stored procedure calls possibly interleaved by Transact-SQL "glue" or control structures such as SET, IF, WHILE, DECLARE; DDL statements such as CREATE, DROP; and permission-related statements such as GRANT, DENY, and REVOKE. A batch can include definition and use of CLR constructs such as user-defined types, functions, procedures, and aggregates.

Compiled plans are stored into a part of SQL Server's memory called plan cache. Plan cache is searched for possible plan reuse opportunities. If a plan reuse for a batch happens, its compilation costs are avoided. Note that in the SQL Server literature, the word "procedure cache" has been used to describe what is called "plan cache" in this paper. "Plan cache" is more accurate because the plan cache stores query plans of more than just the stored procedures.

In SQL Server parlance, the compilation process mentioned in the previous paragraph is sometimes confusingly referred to as a "recompilation" although the process is simply a "compilation."

Definition of Recompilation: Suppose that a batch has been compiled into a collection of one or more query plans. Before SQL Server begins executing any of the individual query plans, the server checks for validity (correctness) and optimality of that query plan. If one of the checks fails, the statement corresponding to the query plan or the entire batch is compiled again, and a possibly different query plan produced. Such compilations are known as "recompilations." Note in particular that the query plans for the batch need not have been cached. Indeed, some types of batches are never cached, but can still cause recompilations. Take, for example, a batch that contains a literal larger than 8 KB. Suppose that this batch creates a temporary table, and then inserts 20 rows in that table. The insertion of the seventh row will cause a recompilation, but because of the large literal, the batch is not cached.

Most recompilations in SQL Server are performed for good reasons. Some of them are necessary to ensure statement correctness; others are performed to obtain potentially better query execution plans as data in a SQL Server database changes. Sometimes, however, recompilations can slow down batch executions considerably, and then, it becomes necessary to reduce occurrences of recompilations.

Memory Allocated To Plan Caching

Most memory used by SQL Server is allocated to the Buffer Pool, which is used to store data pages. SQL Server steals a proportion of this memory for use in caching query plans. The overall amount of memory available to SQL Server depends upon the amount of memory installed on the server, the architecture of the server, the version and edition of SQL Server and the amount of memory pressure being experienced by SQL Server. This pressure can be internal (SQL Server resources need memory) or external (operating system needs memory). SQL Server is designed to respond to memory pressure when necessary.

Four types of object are stored in the Plan Cache: Object Plans, SQL Plans, Bound Trees and Extended Stored Procedures. SQL Server decides the appropriate allocation of memory to the Plan Cache from the Buffer Pool. The algorithm used for this has been improved in successive service packs since SQL Server 2005 was introduced.

SQL Server Version	Cache Pressure Limit
SQL Server 2008 and SQL Server 2005 SP2	75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB
SQL Server 2005 RTM and SQL Server 2005 SP1	75% of visible target memory from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25%  of visible target memory > 64GB
SQL Server 2000	SQL Server 2000 4GB upper cap on the plan cache

Table 1: Plan cache memory allocation by SQL Server version

While 32-bit systems may use AWE (Address Window Extensions) memory to extend the available memory beyond the 4G virtual address space limit of the 32-bit architecture, this additional memory can only be used for data pages in the Buffer Pool, not by pages in the Plan Cache. It is not considered visible memory. No such limitation applies to 64-bit systems.