dm_os_memory_cache_clock_hands understanding

Question

As I mentioned in this question I'm trying to understand the working of clock hands. Simply put clock hands move when there is memory pressure so that cache entries can be removed or the cost can be reduced.

With what I've learned I now try to interpret dm_os_memory_cache_clock_hands. When I check dm_os_memory_cache_clock_hands on one of my servers (SQL Server 2016 SP1, 32 RAM) I get a little confused about what I see.

The round_start_time for CACHESTORE_SQLCP is approx 31 hours, so I would think this cache isn't suffering a lot of memory pressure because it takes a while to complete one round. On the other hand I see that last_tick_time is changing with each refresh, so the hand is moving. Another strang fact is that removed_all_rounds_count is also changing. So entries are removed from the cache. The clock_status is always suspended. The removed_last_round_count is about 68000.

So my conclusion is that there is pressure on CACHESTORE_SQLCP because the hand is moving and removed_all_rounds_count is changing and removed_last_round_count is high.
Is this a correct interpretation of the dmv?

What I don't understand is why round_start_time is so high on a 32GB server and the hand is moving all the time.

I used following script (that I found in the tigertoolbox) to convert last_tick_time to datetime:

    declare @ticks_per_ms bigint,@now DATETIME
    select @ticks_per_ms=ms_ticks from sys.dm_os_sys_info
    set @now=getdate()

    CASE WHEN last_tick_time BETWEEN -2147483648 AND 2147483647 AND 
         @ticks_per_ms BETWEEN -2147483648 AND 2147483647 
    THEN DATEADD(ms, last_tick_time - @ticks_per_ms, @now) 
    WHEN last_tick_time/1000 BETWEEN -2147483648 AND 2147483647 AND 
         @ticks_per_ms/1000 BETWEEN -2147483648 AND 2147483647 
    THEN DATEADD(s, (last_tick_time/1000) - (@ticks_per_ms/1000), @now) 
    ELSE NULL 
    END AS last_clock_hand_move

I restarted the SQL Server Service so that the DMV is cleared. Then I monitored the local pressure limit and the DMV to see what actually happens.

I don't know if my findings are correct but this is what I discovered:

• According to BOL the cache pressure limit of CACHESTORE_SQLCP is 5GB (the visible target memory of the server is 24GB). So the internal clock hand should be triggered at 62.5% of 5GB (3200MB).
• I noticed the internal hand got the status running around approx 3142MB (a little earlier then calculated). This doesn't match with the info on BOL or I did a miscalculation.
• removed_all_rounds_count increased when last_tick_time changed
• The hand didn't move anymore untill the threshold was reached and again entries where removed. This was repeated untill a round was completed and then it started all over.
  

Answer 1

The dm_os_memory_cache_clock_hands dynamic management view (DMV) in SQL Server is used to monitor the status of the clock hands for the various memory caches in the system. The clock hands are used to manage the eviction of cache entries when memory pressure occurs.

The round_start_time column in the DMV shows the time when the current round of the clock hand started. A round is defined as the time it takes for the clock hand to complete a full iteration of the cache. The longer it takes for a round to complete, the less memory pressure there is on the cache.

The last_tick_time column shows the time when the clock hand was last moved. If the clock hand is moving frequently, it indicates that there is more memory pressure on the cache and that more cache entries are being evicted.

The removed_all_rounds_count column shows the total number of cache entries that have been removed from the cache since the cache was created. The removed_last_round_count column shows the number of cache entries that were removed during the current round of the clock hand.

Based on the information you provided, it seems that there may be some memory pressure on the CACHESTORE_SQLCP cache, as indicated by the fact that the clock hand is moving frequently and a large number of cache entries are being removed. It is not uncommon for the round_start_time to be relatively high on a server with 32GB of RAM, as this may indicate that the cache is not under a lot of memory pressure and is able to keep most of its entries in memory.

It is important to note that the clock hand status may be suspended for various reasons, such as when the cache is being resized or when the system is under heavy load. This does not necessarily indicate a problem with the cache or the system.

To better understand the reasons for the memory pressure on the CACHESTORE_SQLCP cache and the behavior of the clock hands, it may be helpful to look at other performance metrics and system activity, such as the amount of available memory, the workload on the server, and the overall cache hit ratio. It may also be helpful to review any recent changes or issues that may have affected the cache or the system.