sys.dm_os_memory_cache_clock_hands - Caches and memory pressure

Caches and memory pressure

An alternative way to look at external and internal memory pressure is to look at the behavior of memory caches.

One of the differences of internal implementation of SQL Server  2005  compared to SQL Server 2000 is uniform caching framework. In order to remove the least recently used entries from caches, the framework implements a clock algorithm. Currently it uses two clock hands—an internal clock hand and an external clock hand.

The internal clock hand controls the size of a cache relative to other caches. It starts moving when the framework predicts that the cache is about to reach its cap.

the external clock hand starts to move when SQL Server as a whole gets into memory pressure. Movement of the external clock hand can be due external as well as internal memory pressure. Do not confuse movement of the internal and external clock hands with internal and external memory pressure.

Information about clock hands movements is exposed through the sys.dm_os_memory_cache_clock_hands DMV as shown in the following code. Each cache entry has a separate row for the internal and the external clock hand. If you see increasing rounds_count and removed_all_rounds_count, then the server is under the internal/external memory pressure.

select *
from
    sys.dm_os_memory_cache_clock_hands
where
    rounds_count > 0
    and removed_all_rounds_count > 0

http://msdn.microsoft.com/en-us/library/cc966540.aspx

http://msdn.microsoft.com/en-us/library/ms173786.aspx

SQL Server stores information in memory in a structure called a
memory cache. The information in the cache can be data, index entries,
compiled procedure plans, and a variety of other types of SQL Server
information. To avoid re-creating the information, it is retained the
memory cache as long as possible and is ordinarily removed from the
cache when it is too old to be useful, or when the memory space is
needed for new information. The process that removes old information is
called a memory sweep. The memory sweep is a frequent activity, but is
not continuous. A clock algorithm controls the sweep of the memory
cache. Each clock can control several memory sweeps, which are called
hands. The memory-cache clock hand is the current location of one of
the hands of a memory sweep.