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) 
    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.
  • The round_start_time is giving cumulative value and I believe it is not recording correctly or may be doing something totally different BOL has no proper definition, the same is story for last_tick_time. I am doing some testing and will update you.
    – Shanky
    Nov 13, 2018 at 15:13
  • @shanky: I updated my question with a script to convert last_tick_time to datetime Nov 14, 2018 at 7:03


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.