Q and A: Clock Hands - what are they for?

Q1 I read in your blog about the new algorithm with two clock hands to control memory consumption, but I really would like to understand exactly what the hands are doing and if they relate to the lazywriter.

A1. In SQL Server 2005 we have separated database cache, Buffer Pool, and the rest of the caches. As in SQL Server 2000, in the SQL Server 2005 lazywriter continues to be responsible for controlling memory consumption by database page's. However it does no longer control memory consumption by procedure cache. In SQL Server 2005 we introduced new common caching framework. All caches except for Buffer Pool leverage it. The framework consist of set of stores and ResourceMonitor. There are three types of stores: cache store, user store (the name here is related to internal usage of the store - it does not have to do anything with a user) and object store. Both cache and user stores share common LRU mechanism and leverage common costing. Procedure cache is an example of a cache store and metadata cache is an example of a user store. Object stores are just pools of memory blocks and don't require either LRU nor costing. Network library leverage object store for pooling network buffers.

Store's LRU mechanism is a rather simple - we use variation of thoroughly discussed in operating system's literature clock algorithm. As you remember clock algorithm has a hand. When hand moves it decreases a cost of an entry. Once cost of an entry reaches 0, the entry can be discarded from the cache. Cost gets reset whenever an entry is reused. When dealing with multiple caches a designer has to take into account global and local eviction policies. Global policies consider global picture and enable running of LRU, clock algorithm, across all the caches. In its turn, local policies look at the local resource consumption only and run LRU for a given cache only.

In order to satisfy global and local policies SQL Server's stores implement two hands: external and internal (please don't confuse a clock hand names with types of memory pressure. There is no connection!). External clock hand implements global policy and internal clock hand implements local policy. Even though every cache has its own external hand, external hands move "synchronously" - one at a time. Their task is to simulate global hand. ResourceMonitor is in charge of moving external hands whenever it notices a memory pressure (Don't forget there are different types of memory pressures, you can find more information here https://blogs.msdn.com/slavao/archive/2005/02/01/364523.aspx). Internal clock is moved whenever a single cache needs to be trimmed. SQL Server attempts to keep each cache "reasonably" sized with respect to other caches. A task, thread, checks local policies and runs internal clock hand if an it needs to be run whenever the task accesses the store.

Q2 There is no documentation on the dmv sys.dm_os_memory_cache_clock_hands, so I would like to understand what the values mean in that view.

A2. Once you know what the clock hands are and what they are for suddenly this dmv makes sense :-). So here is the brief explanation:

cache_address - an address of the store in memory. You can use it to join with the rest of the cache/clerks related dmvs

name,type - name and a type of a store the same as in other dmvs.

clock_hand - type of  a clock hand: external or internal. Remember, there will be two clock hands for every store.

clock_status -  status of  a clock hand: suspended or running. A clock hand runs whenever a corresponding policy kicks in.

rounds_count - a number of rounds a clock hand made. External clock hands should have this value almost the same.

removed_all_rounds_count - a number of entries removed by a clock hand in all rounds

removed_last_round_count - a number of entries removed by a clock hand in a last round

last_tick_time - time when a clock hand moved last

round_start_time - time when a clock hand started current round

last_round_start_time - overall time of the last, previous, round

In this dmv the most interesting columns are status and count of rounds. For example, if external clock hands constantly running, SQL Server is permanently under memory pressure. If you remove this condition chances are the server will perform better. Depending on a store you can improve overall server behavior as well. For example if you have plenty of adhoc queries, procedure cache's local policy will force run of its internal clock hand. It means that every access to the store will be "penalized". You can remove this condition by rewriting your application to either use parameterized queries or stored procedures.

Let me know if you have more questions!