Database Reference
In-Depth Information
Listing 26-29.
Checking a cache store's size
select
type as [Cache Store]
,sum(pages_in_bytes) / 1024.0 as [Size in KB]
from sys.dm_os_memory_objects
where type in
('MEMOBJ_CACHESTORESQLCP','MEMOBJ_CACHESTOREOBJCP'
,'MEMOBJ_CACHESTOREXPROC','MEMOBJ_SQLMGR')
group by
type
Each cache store uses a hash table in which hash buckets keep zero or more plans. There are about 40,000
buckets in the Object plan and the SQL plan stores in 64-bit instances, and about 10,000 buckets in 32-bit instances of
SQL Server. The size of the bound trees cache store is about 1/10th of that number, and the number of buckets in the
extended stored procedures store is always 127.
SQL Server uses a very simple algorithm to calculate the hash value for the plan based on the following formula:
(object_id * database_id) mod hash_table_size
For parameterized and ad-hoc queries,
object_id
is the internal hash of the query or batch. It is entirely
possible that one bucket stores multiple plans for the same object or query. As we have already discussed, different
SET options, database users, and quite a few other factors prevent plan reuse. SQL Server compares multiple plan
attributes in looking for the right plan in the cache. We will discuss how to analyze plan attributes later in this chapter.
Compiled plans cached for multi-statement batches are, basically, the arrays of individual statement-level plans.
When a statement from a batch needs to be recompiled, SQL Server recompiles the individual statement rather than
the entire batch.
SQL Server treats a cached batch plan as a single unit. The entire batch must be a character-for-character match
with original batch that produced the cached plan in order for that plan to be reused. SQL Server generates an
execution plan from the compiled plan for the entire batch.
The amount of memory that can be used by Plan Cache depends on the version of SQL Server (see Table
26-1
).
Table 26-1.
Plan Cache Pressure Limit calculation formula
SQL Server Version
Cache Pressure Limit
SQL Server 2005 RTM, 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 2005 SP2+, SQL Server 2008/2008R2,
SQL Server 2012, SQL Server 2014
75% of visible target memory from 0-4GB + 10% of visible target
memory from 8GB-64GB + 5% of visible target memory >64GB
Visible memory
is different in 32-bit and 64-bit instances of SQL Server. 32-bit instances of SQL Server have at
most 2GB or 3GB of visible memory, depending on the presence of a /3GB switch in the
boot.ini
file. Even when
AWE (Address Windows Extension) memory is in use, memory above 4GB can be used for the buffer pool only. No
such limitation exists on 64-bit instances of SQL Server.
SQL Server starts to remove plans from the cache in cases of memory pressure. There are two kinds of memory
pressure:
local
and
global
. Local memory pressure happens when one of the cache stores grows too big and starts
using too much SQL Server process memory. Global memory pressure happens when Windows forces SQL Server to
reduce its physical memory usage, or when the size of all cache stores combined reaches 80 percent of the plan cache
pressure limit.
