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
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.
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.
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_SQLCPis 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.
- 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.