Database Reference
In-Depth Information
Local memory pressure is triggered when one of the cache stores starts to use too much memory. In SQL
Server 2005-2008R2, where single-page and multi-page allocations are treated separately, memory pressure occurs
when a cache store reaches 75 percent of the plan cache pressure limit in a single-page or 50 percent in multi-page
allocations. In SQL Server 2012-2014, there is only one memory allocator, called the
any-size page allocator
, and
memory pressure is triggered when a cache store grows to 62.5 percent of the plan cache pressure limit.
Local memory pressure can also be triggered based on the number of plans in the SQL and Object plan stores.
That number is about four times the hash table size, which is 40,000 or 160,000 plans on 32-bit and 64-bit instances
respectively.
Both local and global memory pressure remove plans from the cache using an algorithm called
eviction policy
,
which is based on plan cost. For ad-hoc plans, the cost starts with zero and increments by one with every plan reuse.
Other types of plans measure the cost of resources required to produce them. It is based on I/O, memory, and context
switches in the units, called
ticks
, as shown below:
I/O
: Each I/O operation costs 1 tick with a maximum of 19
Memory
: Each 16 pages of memory costs 1 tick with a maximum of 4
Context Switches
: Each switch costs 1 tick with a maximum of 8.
When not under memory pressure, costs are not decreased until the total size of all cached plans reaches
50 percent of the buffer pool size. At that point, the
Lazy Writer
process starts periodically scanning plan caches,
decrementing the cost of each plan by one on each scan, removing plans with zero cost. Alternatively, each plan reuse
increments its cost by one for ad-hoc queries, or by the original plan generation cost for other types of plans.
Listing 26-30 shows you how to examine the current and original cost of cached entries in SQL and Object plan
cache stores.
Listing 26-30.
Examining original and current cost of cache entries
select
q.Text as [SQL], p.objtype, p.usecounts, p.size_in_bytes
,mce.Type as [Cache Store]
,mce.original_cost, mce.current_cost, mce.disk_ios_count
,mce.pages_kb /* Use pages_allocation_count in SQL Server prior 2012 */
,mce.context_switches_count
,qp.query_plan
from
sys.dm_exec_cached_plans p with (nolock) join
sys.dm_os_memory_cache_entries mce with (nolock) on
p.memory_object_address = mce.memory_object_address
cross apply
sys.dm_exec_sql_text(p.plan_handle) q
cross apply
sys.dm_exec_query_plan(p.plan_handle) qp
where
p.cacheobjtype = 'Compiled plan' and
mce.type in (N'CACHESTORE_SQLCP',N'CACHESTORE_OBJCP')
order by
p.usecounts desc
Examining Plan Cache
There are several data management views that provide plan cache related information. Let's look at some of them in
depth.
