Plan Cache Internals
Knowing when and how plans are reused or recompiled can help you design well-performing applications. The more you understand about optimal query plans, and how different actual values and cardinalities require different plans, the more you can determine when recompilation is a useful thing. When you are getting unnecessary recompiles, or when SQL Server is not recompiling when you think it should, your troubleshooting efforts will be easier the more you know about how plans are managed internally. In this section, we’ll explore the internal organization of the plan cache, the metadata available, how SQL Server finds a plan in cache, plan cache sizing, and SQL Server’s plan eviction policy.
Cache Stores
SQL Server’s plan cache is made up of four separate memory areas, called cache stores. There are actually other stores in SQL Server’s memory, which can be seen in the Dynamic Management View (DMV) called sys.dm_os_memory_cache_counters, but there are only four that contain query plans. The names in parentheses below are the values that can be seen in the type column of sys.dm_os_memory_cache_counters:
Object Plans (CACHESTORE_OBJCP)
Object Plans include plans for stored procedures, functions, and triggers
SQL Plans (CACHESTORE_SQLCP)
SQL Plans include the plans for adhoc cached plans, autoparameterized plans, and prepared plans.
Bound Trees (CACHESTORE_PHDR)
Bound Trees are the structures produced by SQL Server’s algebrizer for views, constraints, and defaults.
Extended Stored Procedures (CACHESTORE_XPROC)
Extended Procs (Xprocs) are predefined system procedures, like sp_executeSql and sp_tracecreate, that are defined using a DLL, not using Transact-SQL statements. The cached structure contains only the function name and the DLL name in which the procedure is implemented.
Each plan cache store contains a hash table to keep track of all the plans in that particular store. Each bucket in the hash table contains zero, one, or more cached plans. When determining which bucket to use, SQL Server uses a very straightforward hash algorithm. The hash key is computed as (object_id * database_id) mod (hash table size). For plans that are associated with adhoc or prepared plans, the object_id is an internal hash of the batch text. The DMV sys.dm_os_memory_cache_hash_tables contains information about each hash table, including its size. You can query this view to retrieve the number of buckets for each of the plan cache stores using the following query:
SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP',
'CACHESTORE_PHDR', 'CACHESTORE_XPROC');
You should notice that the Bound Trees store is about 10 percent of the size of the stores for Object Plans and SQL Plans. The size of the store for Extended Stored Procedures is always set to 127 entries. We will not be discussing Bound Trees and Extended Stored Procedures further. The rest of the chapter dealing with caching of plans will be concerned only with Object Plans and SQL Plans.
Finding a plan in cache is a two-step process. The hash key described previously leads SQL Server to the bucket in which a plan might be found, but if there are multiple entries in the bucket, SQL Server needs more information to determine if the exact plan it is looking for can be found. For this second step, it needs a cache key, which is a combination of several attributes of the plan. Earlier, we looked at the DMF sys.dm_exec_plan_attributes, to which we could pass a plan_handle. The results obtained were a list of attributes for a particular plan, and a Boolean value indicating whether that particular value was a cache key. Table 5-1 included 11 attributes that comprise the cache key, and SQL Server needs to make sure all 11 values match before determining that it has found a matching plan in cache.
Compiled Plans
There are two main types of plans in the Object and SQL plan cache stores: compiled plans and execution plans. Compiled plans are the type of object we have been looking at up to this point, when examining the sys.dm_exec_cached_plans view. We have already discussed the three main objtype values that can correspond to a compiled plan: adhoc, prepared, and proc. Compiled plans can be stored in either the Object store or the SQL store depending on which of those three objtype values they have. The compiled plans are considered valuable memory objects, since they can be costly to re-create. SQL Server will attempt to keep them in cache. When SQL Server experiences heavy memory pressure, the policies used to remove cache objects ensure that our compiled plans are not the first objects to be removed.
A compiled plan is generated for an entire batch, not just for a single statement. For a multi-statement batch, you can think of the compiled plan as an array of plans, with each element of the array containing a query plan for an individual statement. Compiled plans can be shared between multiple sessions or users. However, you should be aware that not every user executing the same plan will get the same results, even if there is no change to the underlying data. Unless the compiled plan is an adhoc plan, each user will have their own parameters, their own local variables, and the batch may build temporary tables or worktables specific to that user. The information specific to one particular execution of a compiled plan is stored in another structure called the executable plan.
Execution Plans
Executable plans, or execution contexts, are considered to be dependent on compiled plans and do not show up in the sys.dm_exec_cached_plans view. Executable plans are runtime objects created when a compiled plan is executed. Just as for compiled plans, executable plans can be object plans stored in the object store, or SQL plans, stored in the SQL store. Each executable plan exists in the same cache store as the compiled plan to which it is dependent. Executable plans contain the particular runtime information for one execution of a compiled plan, and include the actual runtime parameters, any local variable information, object ids for objects created at run time, the user ID, and information about the currently executing statement in the batch.
When SQL Server starts executing a compiled plan, it generates an executable plan from that compiled plan. Each individual statement in a compiled plan gets its own executable plan, which you can think of as a runtime query plan. Unlike compiled plans, executable plans are for a single session. For example, if there are 100 users simultaneously executing the same batch, there will be 100 executable plans for the same compiled plan. Executable plans can be regenerated from their associated compiled plan, and they are relatively inexpensive to create. Later in this section, we’ll look at the sys.dm_exec_cached_plan_dependent_objects view, which contains information about your executable plans.
Plan Cache Metadata
We have already looked at some of the information in the DMV sys.dm_exec_cached_plans when we looked at usecount information to determine whether or not our plans were being reused. In this section, we’ll look at some of the other metadata objects and discuss the meaning of some of the data contained in the metadata.
Handles
The sys.dm_exec_cached_plans view contains a value called a plan_handle for every compiled plan. The plan_handle is a hash value that SQL Server derives from the compiled plan of the entire batch, and it is guaranteed to be unique for every currently existing compiled plan. (The plan_handle values can be reused over time.) The plan_handle can be used as an identifier for a compiled plan. The plan_handle remains the same even if individual statements in the batch are recompiled because of the correctness or optimality reasons discussed earlier.
As mentioned, the compiled plans are stored in the two cache stores, depending on whether the plan is an object plan or a SQL plan. The actual SQL Text of the batch or object is stored in another cache called the SQL Manager Cache (SQLMGR). The Transact-SQL Text associated with each batch is stored in its entirety, including all the comments. The Transact-SQL Text cached in the SQLMGR cache can be retrieved using a data value called the sql_handle. The sql_handle contains a hash of the entire batch text, and because it is unique for every batch, the sql_handle can serve as an identifier for the batch text in the SQLMGR cache.
Any specific Transact-SQL batch will always have the same sql_handle, but it may not always have the same plan_handle. If the cache keys change, we’ll get a new plan_handle in plan cache. Refer back to Table 5-1 to see which plan attributes make up the cache keys. The relationship between sql_handle and plan_handle is thus 1:N.
We’ve seen that plan_handle values can be easily obtained from the view sys.dm_exec_cached_ plans. We can get the sql_handle value that corresponds to a particular plan_handle from the sys.dm_exec_plan_attributes function that we looked at earlier. Here is the same query we discussed earlier to return attribute information and pivot it so that three of the attributes are returned in the same row as the plan_handle value.
SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan'
) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute
IN (“set_options”, “object_id”, “sql_handle”)) AS pvt;
The view sys.dm_exec_query_stats contains both plan_handle and sql_handle values, as well as information about how often each plan was executed and how much work was involved in the execution. However, because plan_handle is a very cryptic value, it’s difficult to determine which of our queries each sql_handle corresponds to. To get that information, we can use another function.
sys.dm_exec_sql_text
The function sys.dm_exec_sql_text can take either a sql_handle or a plan_handle as a parameter, and it will return the SQL Text that corresponds to the handle. Any sensitive information like passwords, that might be contained in the SQL Text, are blocked when the SQL is returned. The text column in the functions output contains the entire SQL batch text for adhoc, prepared, and autoparameterized queries, and for objects like triggers, procedures, and functions, it gives the full object definition.
Viewing the SQL Text from sys.dm_exec_sql_text is useful in quickly identifying identical batches that may have different compiled plans because of several factors, like SET option differences. As an example, consider the code below, which executes two identical batches. The only difference between the two consecutive executions is that the value of the SET option QUOTED _IDENTIFIER has changed. It is OFF in the first execution and ON in the second. After executing both batches, we examine the sys.dm_exec_query_stats view.
USE Northwind2;
DBCC FREEPROCCACHE;
SET QUOTED_IDENTIFIER OFF;
GO
--- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SET QUOTED_IDENTIFIER ON;
GO
--- this is an example of the relationship between
-- sql_handle and plan_handle
SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SELECT st.text, qs. sql_handle, qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st;
GO
You should see two rows with the same text string and sql_handle, but with different plan_handle values, as shown below. (In our output, the difference between the two plan_handle values is only a single digit so it may be hard to see, but in other cases the difference may be more obvious.)
.jpg "Cc293624.table_C05621961_f(en-us,TechNet.10).jpg")
We can see that we have two plans corresponding to the same batch text, and this example should make clear the importance of making sure that all the SET options that affect plan caching should be the same when the same queries are executed repeatedly. You should verify whatever changes your programming interface makes to your SET options to make sure you don’t end up with different plans unintentionally. Not all interfaces use the same defaults for the SET option values. For example, the OSQL interface uses the ODBC driver, which sets QUOTED_IDENTIFIER to OFF for every connection, while SQL Server Management Studio uses ADO.NET, which sets QUOTED_IDENTIFIER to ON. Executing the same batches from these two different clients will result in multiple plans in cache.
sys.dm_exec_cached_plans
The sys.dm_exec_cached_plans view is the one we use most often for troubleshooting query plan recompilation issues. It’s the one we used in the first section to illustrate the plan reuse behavior of adhoc plans compared to autoparameterized and prepared plans. This view has one row per cached plan, and in addition to the plan_handle and usecounts, which we’ve looked at already, this DMV has other useful information about the cached plans, including:
- size_in_bytes: number of bytes consumed by this cache object.
- cacheobjtype: type of the cache object, that is, if it’s a compiled plan, or a Parse Tree or an Extended Proc.
- memory_object_address: memory address of the cache object, which can be used to get the memory breakdown of the cache object.
Although this DMV does not have the SQL Text associated with each compiled plan, we’ve seen that we can find it by passing the plan_handle to the sys.dm_exec_sql_text function. We can use the query below to retrieve the text, usecounts, and size_in_bytes of the compiled plan and cacheobjtype for all the plans in cache. The results will be returned in order of frequency, with the batch having the most use showing up first:
SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes,
cp.cacheobjtype, cp.objtype
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY cp.usecounts DESC
sys.dm_exec_cached_plan_dependent_objects
This DMF returns one row for every dependent object of a compiled plan when you pass a valid plan_handle in as a parameter. If the plan_handle is not that of a compiled plan, the function will return NULL. Dependent objects include executable plans, as discussed previously, as well as plans for cursors used by the compiled plan. The example below uses sys.dm_exec_cached_plan_dependent_objects, as well as sys.dm_exec_cached_plans, to retrieve the dependent objects for all compiled plans, the plan_handle, and their usecounts. It also calls the sys.dm_exec_sql_text function to return the associated Transact-SQL batch.
SELECT text, plan_handle, d.usecounts, d.cacheobjtype
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY
sys.dm_exec_cached_plan_dependent_objects(plan_handle) d;
sys.dm_exec_requests
The sys.dm_exec_requests view returns one row for every currently executing request within your SQL Server instance and is useful for many purposes in addition to tracking down plan cache information. This DMV contains the sql_handle and the plan_handle for the current statement, as well as resource usage information for each request. For troubleshooting purposes you can use this view to help identify long-running queries. Keep in mind that the sql_handle points to the Transact-SQL for the entire batch. However, the sys.dm_exec_requests view contains columns statement_start_offset and statement_end_offset that indicate the position within the entire batch where the currently executing statement can be found. The offsets start at 0 and an offset of -1 indicates the end of the batch. The statement offsets can be used in combination with the sql_handle passed to sys.dm_exec_sql_text to extract the query text from the entire batch text as demonstrated next. This query returns the 10 longest-running queries currently executing:
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_elapsed_time DESC
Note that including the ‘*’ in the SELECT list indicates that this query should return all the columns from sys.dm_exec_requests view. You should replace the ‘*’ with the columns that you are particularly interested in, such as start_time, blocking_session_id, etc.
sys.dm_exec_query_stats
Just as the text returned from the sql_handle is the text for the entire batch, the compiled plans that are returned are for the entire batch. For optimum troubleshooting, we can use sys.dm_exec_query_stats to return performance information for individual queries within a batch. This view returns performance statistics for queries, aggregated across all executions of the same query. This view also returns both a sql_handle and a plan_handle, as well as the start and end offsets like we saw in sys.dm_exec_requests. The following query will return the top-10 queries by total CPU time, to help you identify the most expensive queries running on your SQL Server.
SELECT TOP 10 SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS query_text, *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
ORDER BY total_elapsed_time/execution_count DESC;
The sys.dm_exec_query_stats returns a tremendous amount of performance information for each query, including the number of times it was executed, and the cumulative I/O, CPU, and duration information. Keep in mind that this view is only updated when a query is completed, so you might need to retrieve information multiple times if there is currently a large workload on your server.
Cache Size Management
We’ve already talked about plan reuse and how SQL Server finds a plan in cache. In this section, we’ll look at how SQL Server manages the size of plan cache, and how it determines which plans to remove if there is no room left in cache. Earlier, I discussed a few situations in which plans would be removed from cache. These situations included global operations like running DBCC FREEPROCCACHE to clear all plans from cache, as well as changes to a single procedure, such as ALTER PROCEDURE, which would drop all plans for that procedure from cache. In most other situations, plans will only be removed from cache when memory pressure is detected. The algorithm that SQL Server uses to determine when and how plans should be removed from cache is called the eviction policy. Each cache store can have its own eviction policy, and we will only be discussing the policy for the object plan store and the SQL plan store.
Determining which plans to evict is based on the cost of the plan, which will be discussed in the next section. When eviction starts is based on memory pressure. When SQL Server detects memory pressure, zero-cost plans will be removed from cache and the cost of all other plans is reduced by half. As discussed in Chapter 2 of Inside SQL Server 2005: The Storage Engine, there are two types of memory pressure that can lead to removal of plans from cache. These are referred to as local and global memory pressure.
When discussing memory pressure, we refer to the term “visible” memory. Visible memory is the directly addressable physical memory available to SQL Server buffer pool. On a 32-bit SQL Server instance, the maximum value for the visible memory is either 2 GB or 3 GB, depending on whether you have the /3 GB flag set in your boot.ini file. Memory with addresses greater than 2 GB or 3 GB is only available 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. In any of the discussion below, if we refer to visible target memory greater than 3 GB, keep in mind that is only possible on a 64-bit SQL Server. The term “target” memory refers to 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 value you have configured for “max server memory” and the total amount of physical memory available to the operating system. So “visible target memory” is the visible portion of the target memory. Query plans can only be stored in the non-AWE-mapped memory, which is why the concept of visible memory is important. You can see a value for visible memory, specified as the number of 8-KB buffers, when you run the DBCC MEMORYSTATUS command. The section called Buffer Counts displays values for Visible memory as well as Target memory.
SQL Server defines a cachestore pressure limit value, which varies depending on the version you’re running and the amount of visible target memory. We’ll explain shortly how this value is used. The formula for determining the plan cache pressure limit changed in SQL Server 2005, Service Pack 2. Table 5-3 shows how to determine the plan cache pressure limit in SQL Server 2000 and 2005, and indicates the change in Service Pack 2, which reduced the pressure limit with higher amounts of memory. Be aware that these formulas are subject to change again in future service packs.
As an example, assume we are on SQL Server 2005, Service Pack 1, on a 64-bit SQL Server instance with 28 GB of target memory. The plan cache pressure limit would be 75 percent of 8 GB plus 50 percent of the target memory over 8 GB (or 50 percent of 20 GB), which is 6 GB + 10 GB or 16 GB.
Table 5-3 Determining the Plan Cache Pressure Limit
.jpg "Cc293624.table_C05621961_3(en-us,TechNet.10).jpg")
On SQL Server 2005, Service Pack 2, on the 64-bit SQL Server instance with 28 GB of target memory, the plan cache pressure limit would be 75 percent of 4 GB plus 10 percent of the target memory over 4 GB (or 10 percent of 24 GB), which is 3 GB + 2.4 GB, or 5.4 GB.
Local Memory Pressure
If any single cache store grows too big, it indicates local memory pressure and SQL Server will start removing entries from that store only. This behavior prevents one store from using too much of the total system memory.
If a cache store reaches 75 percent of the cache plan pressure limit, described in Table 5-3, in single-page allocations or 50 percent of the cache plan pressure limit in multipage allocations, internal memory pressure is triggered and plans will be removed from cache. For example, in the situation above we computed the cache plan pressure limit to be 5.4 GB. If any cache store exceeds 75 percent of that value, or 4.05 GB in single-page allocations, internal memory is triggered. If adding a particular plan to cache causes the cache store to exceed the limit, the removal of other plans from cache will happen on the same thread as the one adding the new plan, which can cause the response time of the new query to be increased.
In addition to memory pressure occurring when the total amount of memory reaches a particular limit, SQL Server also indicates memory pressure when the number of plans in a store exceeds four times the hash table size for that store, regardless of the actual size of the plans. The queries below can be used to determine the number of buckets in the hash tables for the object store and the SQL store, and the number of entries in each of those stores.
SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP');
SELECT type, count(*) total_entries
FROM sys.dm_os_memory_cache_entries
WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
GROUP BY type;
Global Memory Pressure
Global memory pressure applies to memory used by all the cache stores together, and can be either external or internal. External global pressure occurs when the operating system determines that the SQL Server process needs to reduce its physical memory consumption because of competing needs from other processes on the server. All cache stores will be reduced in size when this occurs.
Internal global memory pressure can occur when virtual address space is low. Internal global memory pressure can also occur when the memory broker predicts that all cache stores combined will use more than 80 percent of the cache plan pressure limit. Again, all cache stores will have entries removed when this occurs.
As mentioned, when SQL Server detects memory pressure, all zero-cost plans will be removed from cache and the cost of all other plans is reduced by half. Any particular cycle will update the cost of at most 16 entries for every cache store. When an updated entry has a zero-cost value, it can be removed. There is no mechanism to free entries that are currently in use. However, unused dependent objects for an in-use compiled plan can be removed. Dependent objects include the executable plans and cursors, and up to half of the memory for these objects can be removed when memory pressure exists. Remember that dependent objects are inexpensive to re-create, especially compared to compiled plans.
For more information on how memory pressure is detected, please refer to Chapter 1, “A Performance Troubleshooting Methodology,” and Chapter 2 of Inside SQL Server 2005: The Storage Engine.
Costing of Cache Entries
Evicting plans from cache is based on their cost. For adhoc plans, the cost is considered to be zero, but it is increased by one every time the plan is reused. For other types of plans, the cost is a measure of the resources required to produce the plan. When one of these plans is reused, the cost is reset to the original cost. For non–adhoc queries, the cost is measured in units called ticks, with a maximum of 31. The cost is based on three factors: I/O, context switches, and memory. Each has its own maximum within the 31-tick total.
- I/O: each I/O costs 1 tick, with a maximum of 19.
- Context switches: 1 tick each with a maximum of 8.
- Memory: 1 tick per 16 pages, with a maximum of 4.
When not under memory pressure, costs are not decreased until the total size of all plans cached reaches 50 percent of the buffer pool size. At that point, the next plan access will decrement the cost in ticks of all plans by 1. Once memory pressure is encountered, then SQL Server will start a dedicated resource monitor thread to decrement the cost of either plan objects in one particular cache (for local pressure) or all plan cache objects (for global pressure).
The DMV sys.dm_os_memory_cache_entries can show you the current and original cost of any cache entry, as well as the components that make up that cost.
SELECT text, objtype, refcounts, usecounts, size_in_bytes,
disk_ios_count, context_switches_count,
pages_allocated_count, original_cost, current_cost
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_os_memory_cache_entries e
ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan'
AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
ORDER BY objtype desc, usecounts DESC;
Note that we can find the specific entry in sys.dm_os_memory_cache_entries that corresponds to a particular plan in sys.dm_exec_cached_plans by joining on the memory_object_address column.
© Microsoft. All Rights Reserved.