sys.dm_os_memory_cache_clock_hands – Day 27 – One DMV a Day

Hello Geeks and welcome to the Day 27 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

So far we have been talking about memory caches, clerks, brokers and everything else related to memory in SQL Server. Today I will talk about a DMV which actually keeps track of how the flushing is happening in each cache store. Sys.dm_os_memory_cache_clock_hands shows this information. For every cache there will be two clock hands, one External and one Internal.

We know that memory cache is maintained to enable reuse of information already built like plans, data, index entries, etc. They will be removed from cache when they are relatively old or due to memory pressure. A process called Memory Sweeps cleans up the memory. It happens frequently. This is implemented by a clock algorithm. Multiple clock hands (sweeps) are controlled by each clock.

Sys.dm_os_memory_cache_clock_hands provides the information like number of sweeps, number of entries updated, removed in last sweep and overall removed count, time taken for last sweep. Let’s first understand how the clocks algorithm work.

As I have mentioned above there are two types of clock hands. External clock hand responds to external pressure. This includes hitting the target memory, external pressure, large memory grants, etc. Internal memory pressure includes overall cap limit (hard limit), plan cache cap limit (soft limit) – remember cache wars?

The clock hand sweeps the cache store. It updates the count based on last usage. Eventually, under pressure, the cache entry reaching 0 first will be cleared from the cache store.

A simple query on sys.dm_os_memory_cache_clock_hands is as below. You can check which cache is under pressure from high counts under the removed_last_round_count. Also problems with high cache store size from last_round_start_time.

SELECT name,
	type,
	clock_hand,
	clock_status,
	rounds_count,
	removed_all_rounds_count,
	updated_last_round_count,
	removed_last_round_count,
	last_round_start_time
FROM sys.dm_os_memory_cache_clock_hands
ORDER BY removed_last_round_count DESC

So now you know how the cache flushing is achieved and how to use sys.dm_os_memory_cache_clock_hands. Tomorrow I will be covering another DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook