Managing Plan-Cache Size

Since version 7.0, SQL Server has long provided the ability to cache procedure plans and other types of plans in what’s known as plan cache. Plan caching lets SQL Server cache and reuse various types of plans to avoid recompilation—and its associated performance hit on SQL Server—wherever possible. (For some background about plan caching, see the sidebar “A Brief History of Plan Caching in SQL Server,” page 28.) SQL Server 2000 and 7.0 have an upper limit on plan-cache size. SQL Server 2005 doesn’t have a specific upper limit on plan-cache size but provides some controls to keep plan cache from getting overly large, so that it doesn’t use too much memory and thus negatively affect performance. Let’s take a closer look at these controls, to help you pay more attention to plan-cache size so that you can consider it in your SQL Server performance-tuning efforts. I’ll base my discussion about plan-cache limits in part on information in Inside Microsoft SQL Server 2005: Query Tuning and Optimization (Microsoft Press, 2007).

Memory and Plan-Cache Limits

SQL Server 2005’s lack of an upper limit on plan-cache size could potentially be detrimental, especially since SQL Server can cache many small, ad hoc plans that might never be reused. Using a large amount of memory for many hundreds or thousands of ad hoc plans is called plan-cache bloat. In addition to using an enormous amount of memory, cache bloat can affect you in another way. Having hundreds or thousands of similar plans, SQL Server might spend an inordinate amount of CPU time simply trying to determine whether there’s a plan in cache for your current query. For these reasons, I strongly recommend that whenever possible you don’t base your applications on ad hoc queries. Creating stored procedures gives you the most control over plan reuse, but you might not be able to use stored procedures for everything. The second-best option is to use prepared queries, either through your application (using prepare and execute methods) or using T-SQL’s sp_executesql interface. Prepared queries let SQL Server store one plan for many queries that differ only in the actual values used for qualifying data in the query’s WHERE clause and greatly reduce the cache space needed.

SQL Server 7.0 limited SQL Server’s total memory to 2GB, which in effect limited SQL Server’s plan-cache size. The 2GB limit exists because on 32-bit systems, a 32-bit address space can directly address only 4GB of memory, and 2GB is normally reserved for the OS’s own uses, with the other 2GB available for applications, such as SQL Server. SQL Server 2000 added the ability to configure the use of Address Windowing Extensions (AWE) memory, which lets SQL Server use more than 4GB if the OS has more than 4GB of memory. However, since plan cache can’t use AWE memory, you’re limited to only 2GB of memory for plan cache, or 3GB if you used the /3GB switch. (For more information about controlling the total amount of memory that SQL Server 2000 and 7.0 can use, see the Microsoft article “How to configure SQL Server to use more than 2 GB of physical memory” at support.microsoft.com/?kbid=274750.) A 64-bit system doesn’t have any of these limitations because it can directly address far more memory than SQL Server or Windows can support.

In many cases, limiting memory to only the non-AWE memory meant that SQL Server 2000 didn’t have enough memory to store all the plans that should be in cache. SQL Server 2005 tried to improve this situation by allowing far more memory to be used for plans. However, it appears that Microsoft went too far and ended up allowing too much memory to be used for plans—and so in SQL Server 2005 SP2, Microsoft changed the sizing algorithm again. Let’s look at the SQL Server 2005 algorithms to limit the size of plan cache.

Plan-Cache Controls in SQL Server 2005

Although plan cache’s size isn’t directly limited in SQL Server 2005, SQL Server does determine a limit at which it considers the system to be under memory pressure. When it detects memory pressure, SQL Server will start removing plans from cache to free up memory. When discussing memory pressure, we refer to the term visible memory—that is, the directly addressable physical memory available to the SQL Server buffer pool. On a 32-bit SQL Server instance, the maximum value for visible memory is either 2GB or 3GB, depending on whether you have the /3GB flag set in your boot.ini file. Memory with addresses greater than 2GB or 3GB is available only indirectly, through AWE-mapped memory. On a 64-bit SQL Server instance, “visible” memory has no special meaning, as all the memory is directly addressable. If I refer to visible memory greater than 3GB, keep in mind that this is possible only on a 64-bit SQL Server system.

The term target memory means the maximum amount of memory that can be committed to the SQL Server process. Target memory refers to the physical memory committed to the buffer pool and is the lesser of the values you’ve configured for “max server memory” and the total amount of physical memory available to the OS. Thus, visible target memory is the visible portion of the target memory. Query plans can be stored only in the non–AWE-mapped memory, which is why the concept of visible memory is important.

Table 1 shows how to determine the plan-cache pressure limit in SQL Server 2005 and SQL Server 2000 and indicates the change in SQL Server 2005 SP2, which reduced the pressure limit percentage when larger amounts of memory are available. Be aware that these formulas are subject to change again in future service packs.

The details of exactly what SQL Server will remove from cache when cache pressure is reached are beyond the scope of this article. You can find more information about this topic, including details about different types of memory pressure, in Chapter 5 of Inside Microsoft SQL Server 2005: Query Tuning and Optimization. However, the bottom line is that pressure is signaled a lot sooner in SQL Server 2005 SP2 than in the initial release or in SP1. Let’s consider an example.

Assume you’re on SQL Server 2005 SP1, on a 64-bit SQL Server instance with 28GB of target memory. Per the formula in Table 1, the plan-cache pressure limit would be 75 percent of 8GB plus 50 percent of the target memory over 8GB (in this case, 50 percent of 20GB)—that is, (.75 × 8GB) + (.5 × 20GB) = 6GB + 10GB = 16GB. On SQL Server 2005 SP2, on the 64-bit SQL Server instance with 28GB of target memory, the plan-cache pressure limit would be 75 percent of 4GB plus 10 percent of the target memory over 4GB (or 10 percent of 24GB)—that is, (.75 × 4GB) + (.10 × 24GB) = 3GB + 2.4GB = 5.4GB.

Ways to Trim Down Plan Cache

So what can you do when plan cache gets bloated? You can remove all your plans from cache by using the DBCC FREEPROCCACHE command, but often this method can hurt overall system performance as much as help it. Another option is to clear only the plans from one database by using the command DBCC FLUSHPROCINDB (database_id).

SQL Server 2008 will provide an option that lets you clear a single plan from cache. The details of exactly how this will be accomplished, and how you can specify which plan to remove, aren’t yet available. However, you can use an existing feature in SQL Server 2005 to remove a single plan for plans that aren’t ad hoc. (You can easily remove stored procedure plans by using sp_recompile, or by altering the stored procedure definition, even if the ALTER doesn’t change anything.) Prepared plans, whether they’re created through autoparameterization, by using sp_executesql, or by using prepare and execute methods in your application, can be removed by creating a plan guide that matches the parameterized query, then dropping the plan guide. (For more information about plan guides, see “Controlling Parameterization,” July 2007, InstantDoc ID 96349.)

Let’s look at an example that shows how to clear a single plan from cache in SQL Server 2005. First I’ll create a copy of the Sales.SalesOrderHeader table in the AdventureWorks database and build a nonclustered, nonunique index on the new table, as Listing 1 shows. Next, I’ll create a prepared query, which selects a very restrictive value that returns only a few rows, as Listing 2 shows. Then, I’ll create a second query, which uses a much less restrictive value, but because it is a prepared query will use the same plan, as you can see in Listing 3. To verify that SQL Server uses the same plan for both queries, you can inspect the plan cache, by running the code in Listing 4. When I run this code, I get the results that Figure 1 shows.

If you realize that you don’t want this plan in cache because you don’t want to always use the plan for the selective value, you can create the plan guide that Listing 5 shows. As soon as the plan guide is created, it will remove the plan for this query from cache, as you can verify by running the code in Listing 4 again.

If you leave the plan guide in the database, the RECOMPILE hint will force SQL Server to always come up with a new plan for queries that have the same form as the query in the @stmt variable. If you only want to remove the existing plan, but not keep the plan guide around, you can remove the plan guide by using the following statement:

EXEC sp_control_plan_guide
N’drop’, N’RemovePlan’

Keep an Eye on Cache

Caching and reusing query plans can have both costs and benefits. If your plans aren’t cached and reused, SQL Server can spend far too much time optimizing your queries—so you need to make sure you have enough memory available for all your useful plans. If you cache and reuse plans inappropriately, you might not have the best plan for your queries, and their performance could suffer. Having too many plans in cache can waste both memory and CPU resources. Understanding how plan-cache size is determined and how to make sure plans are reused only when appropriate can help you better control your SQL Server system and make optimum use of all its resources.