Buffer pool memory that isn’t used for the data
cache is used for other types of caches, primarily the plan cache. The page
replacement policy, as well as the mechanism by which freeable pages are
searched for, are quite a bit different than for the data cache.
SQL Server 2008 uses a common caching framework
that is used by all caches except the data cache. The framework consists of a
set of stores and the Resource Monitor. There are three types of stores: cache
stores, user stores (which don’t actually have anything to do with users), and
object stores. The plan cache is the main example of a cache store, and the
metadata cache is the prime example of a user store. Both cache stores and user
stores use the same LRU mechanism and the same costing algorithm to determine
which pages can stay and which can be freed. Object stores, on the other hand,
are just pools of memory blocks and don’t require LRU or costing. One example
of the use of an object store is the SNI, which uses the object store for
pooling network buffers.
The
LRU mechanism used by the stores is a straightforward variation of the clock
algorithm. Imagine a clock hand sweeping through the store, looking at every
entry; as it touches each entry, it decreases the cost. Once the cost of an
entry reaches 0, the entry can be removed from the cache. The cost is reset
whenever an entry is reused.
Memory management in the stores takes into
account both global and local memory management policies. Global policies
consider the total memory on the system and enable the running of the clock
algorithm across all the caches. Local policies involve looking at one store or
cache in isolation and making sure it is not using a disproportionate amount of
memory.
To satisfy global and local
policies, the SQL Server stores implement two hands: external and internal.
Each store has two clock hands, and you can observe these by examining the DMV sys.dm_os_memory_cache_clock_hands. This view contains one internal and one
external clock hand for each cache store or user store. The external clock
hands implement the global policy, and the internal clock hands implement the
local policy. The Resource Monitor is in charge of moving the external hands
whenever it notices memory pressure. if you take a look at the DMV sys.dm_os_memory_cache_clock_hands, speciļ¬ cally at the removed_last_round_count column, you can look for a value that is very
large compared to other values. If you notice that value increasing
dramatically, that is a strong indication of memory pressure.
