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.

sys.dm_os_memory_cache_clock_hands

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

sys.dm_os_memory_cache_clock_hands

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

2 Comments on “sys.dm_os_memory_cache_clock_hands – Day 27 – One DMV a Day”

  1. My two cents to the excellent article
    1. All external clock hands move synchronously
    2. this clock hand depicts to caching mechanism and this is for cachestore other than buffer pool 🙂

    Thanks a lot Manu

Leave a Reply

Your email address will not be published. Required fields are marked *