Let's talk about SQL Internal Memory. OK, not your favorite subject but I have something interesting to share. I had an issue recently that required me to be digging quite heavily into the memory internals, specifically to leverage clock hands to identify memory pressure. This is a topic that has very little around its specifics but can be dead useful at times. To explain a little about why it’s dead useful, I’ll explain why the scenario that caused me to start digging.
Where Is The Value?
After some planned upgrades to a SQL Server AlwaysOn environment – including some changes to the max/min memory settings and other instance level configurations – I noticed extremely poor performance.
- I looked at the sqlservr.exe process, whose memory footprint was steady at around 10GB, which was where it should be.
- Since all other resource utilization statistics appeared to be in normal ranges, I decided to investigate if there were long-running queries. Nope!
- It was then I thought to myself, “Self! I wonder where the buffer pool is allocated… Maybe there is heavy data access in one database/table causing everything to be slow because it’s being flushed from the pool.” Here’s where it got interesting.
The buffer pool was a shocking 500 MB in size and stature, but the process had 10GB assigned to it. You can check the data page cache's memory allocation as follows:
select case when database_id = 32767 then 'ResourceDB' else db_name( database_id ) end as Database_Name, count( * ) * 8 / 1024 as MBUsed from sys.dm_os_buffer_descriptors group by database_id;
Either there were some internal caches taking up all that space, or there was external pressure in the environment. As far as I know, there is no way to inspect the internal cache control mechanisms for the buffer pool, so I generally rely on investigating the memory pressure for all of the other caches, controlled by the common caching framework. One of the ways I like to get a feel for memory pressure is by looking at these cache stores’ clock hands to see how actively they’re moving (the velocity of the delta), which translates, in a general sense, to the overall memory pressure on the cache and data page cache.
An Interesting Preface and Side Note
From SQLOS’s point of view, consider what the terms pool and cache mean. They are not interchangeable, as I thought they were. In SQLOS-land, a cache is for heterogeneous types of data (different sizes) that usually have a cost and a state associated with them. A cache implements its own control over an entry’s lifetime, visibility (locking/concurrency controls) and page replacement policies (implemented in the form of the LRU – least recently used – algorithm). A pool, on the other hand, is for homogeneous types of data (same sizes) that usually have neither cost nor state. They also have limited control over an entry’s lifetime and visibility. I will henceforth and forevermore refer to the buffer pool as the data page cache! The side note to this conversation is that the common caching framework is *NOT* implemented by the data page cache.
Before SQL Server 2005
The two had different costing policies, but the procedure cache relied on the buffer pool’s eviction mechanism. Because of the small cache size, there was no real need for a common caching framework to handle memory management. Starting with SQL Server 2005, that changed (more features that required caches), which meant more work making a singular mechanism work for everything. Or the more robust, and really the only feasible, option of creating a framework to provide common costing mechanisms and more granular methods for controlling the caches.
The common caching framework that Microsoft settled on is a variation of the clock algorithm – as specified earlier. A clock algorithm is an implementation that provides a way to give data age-based weighting for entries in the cache, for the purposes of optimal page replacement. A good description of the details can be found here:
Clock algorithms are essentially used for determining what to age out of memory when there is memory pressure in the environment.
Microsoft’s implementation for SQL Server leverages two clock hands per cache store, an internal and an external. The internal clock hand is controlled by SQLOS and is essentially a way to set max caps on individual caches so that you don’t get a scenario where you have a single cache pushing the entire process into cataclysmic memory pressure. The external clock hands are moved by the Windows Resource Monitor when the process and system as a whole are experiencing memory pressure. The clock hands are designed to not interfere with the usage of the cache, but as a clock hand is moved, it will divide the cost of not-in-use entries by 2. If the entry is not-in-use and its cost is 0, it will remove the entry’s visibility and then try to remove it from the cache. The removal can fail if another clock hand is operating on that entry simultaneously, but fear not! Removal will succeed when both clock hands have moved on to other entries. Upon access, an entry’s cost is reset. If a specific cache is growing too large internally, internal clock hands start moving and aging entries – these events are defined by local policies. If the system is under memory duress, external clock hands start moving and aging entries – these events are defined by global policies. In the current implementation, there is a
If the system is under memory duress, external clock hands start moving and aging entries – these events are defined by global policies. In the current implementation, there is a 1: many relationships for clocks: clock hands, a 2:1 correspondence for clocks: cache (internal and external clock with a clock hand per cache), and a 1:1 correspondence between memory sweeps: hands. The implication, then, is that each clock can control multiple memory sweeps.
Most of the significant changes to the SQLOS Memory Manager in 2012 appear to revolve around the data page cache and the addition of a separate cache optimized for column store indexes. The data page cache changes aren’t necessarily pertinent to this conversation, but of note – the cache for column store indexes is implemented separate from the buffer pool and leverages the common caching framework’s clock hand implementation for lifetime control of entries. This also carries the caveat that column store objects can pressure the buffer pool for memory allocation within the process virtual address space (VAS).
For my production issue, I was leveraging the results of the following query to take a look at memory pressure:
select type, clock_hand, sum( rounds_count ) as total_sweeps, sum( removed_all_rounds_count ) as total_removed from sys.dm_os_memory_cache_clock_hands group by type, clock_hand order by total_removed desc;
This presented me with some interesting findings. The delta on the objectstore_lock_manager cache’s external clock hand was absolutely insane (remember, the velocity of the delta is what we’re most interested in). The delta of the total entries removed was exponentially higher than any other cache’s entry removal over a 30 second period:
What made this even more interesting was that this was the only external clock hand that exhibited this behavior. Even though every cache maintains its own external clock hand, these should, more or less, move synchronously in order to simulate a global clock hand. At this point, I made a mild presumptive leap that the data page cache was likely experiencing the same insane, incorrect external pressure and was perpetually flushing entries from the cache. In this case, an instance reset during a maintenance window became the method of resolution and provided a fix (my working theory maintains a bug condition pertaining to AlwaysOn deployments and instance level memory settings).
To read more about the nitty-gritty on clock hands and SQLOS caching (albeit from 2005, but there really haven’t been huge changes to it that I’m aware of), I recommend the following:
Review our case studies and engagements where we helped companies just like yours solve a variety of business needs.
Since 1981, Oakwood has been helping companies of all sizes, across all industries, solve their business problems. We bring world-class consultants to architect, design and deploy technology solutions to move your company forward. Our proven approach guarantees better business outcomes. With flexible engagement options, your project is delivered on-time and on budget. 11,000 satisfied clients can’t be wrong.