Previous posts in this series.
SQL Server Memory – Underground
SQL Server Memory – The Evolution
SQL Server Memory – Rise of the Clerks – Part 1
In the last post, I had talked about the memory architecture in SQL Server 2005. In this post, we will talk more about the clerks.
As mentioned in my previous post, memory clerks are consumers of memory. The allocate memory from the memory nodes using one of the allocators as exposed by the memory node. In general there are 4 different types of clerks in SQL. Generic Clerks, Cache Store, Object Store, and User Store.
Before we talk about these stores and the clerks, its important to understand the difference between a Cache and a pool in SQL. Cache in SQL is a mechanism to Cache different types of data, which are typically controlled by a cost for each entry. Ideally, each of these entries in the cache has a state value associated with it. Caches typically implement a lifetime control on the entry. This is controlled by some sort of LRU policies. Depending on the type of data caches, the entries could be shared between multiple users. Pool on the other hand is a mechanism for caching homogenous data(same type of data). The data in these does not have have cost or state associated with it. There is no lifetime control implemented on the pool.
Buffer Pool, as the name suggest is NOT a pool, but a cache, as we will see later.
Cache Store/User Store: These are typical caches in SQL. The Cache store provides support for hashed tables, while the user store provides caching for user created objects. Caches implement two types of control on the entries,
- Lifetime control: As the name suggests, it provides time management for the entries.
- Visibility control: As the same suggests, it provides control over, whether the entry in the cache is visible to any look ups or not.
In both the case, the SQLOS’s caching framework provides visibility control and lifetime control (not in case of User store). Life Time of the entry is managed using the reference count in the Clock Entry info, once the reference count becomes zero, the entry is removed from the cache. Visibility is maintained by the Pin Count in the clock entry info. For an entry to be visible, its pin count needs to have a value grater than zero (along with being non-dirty and no marked for single usage).
A Cache Store, can support multiple types of Hash Tables, this design is very useful for lookups. For example the Procedure Cache, has hash tables for both name and id’s.
SQL implements lifetime/visibility control using the clock algorithm. The Clock algorithm implements two clock hands.
- External Hand: Moved by the Resource Monitor, when there is a memory pressure.
- Internal Hand: Used to control the size of a cache, relative to other caches in SQL. In other words, this hand puts a cap on the memory usage by a single cache.
So what does the clock hands really do?? Every time the clock hands sweeps through the cache, it divides the cost of the entry by 2. If the entry is not in use and its cost is zero, the clock hands first marks the entry as invisible and then removes it from the cache.
To read more on Clock hands, refer this post by Slava.
Object Store: Object store is a simple pool in SQL. it does not implement any costing for the entries. It controls the size, using a preconfigured max cap. In addition on every memory pressure notification from the resource monitor in SQL, it removes a preconfigured number of entries.
Generic Clerks: Provides mechanisms to allocate memory from the memory node using any of the allocators as exposed by the memory node.
In SQL Server 2005, Buffer Pool allocation is done in a similar way as with SQL 2000. We start of with checking how much RAM is installed on the machine, based on which we calculate the Buf array (array to monitor the buffers in the buffer pool). Once we have calculated the Buf array, we calculate the size of the BPool and allocate it. As with SQL 2000, the max server memory setting is only used to control the amount of memory used by buffer pool and not the entire SQL server. Buffer pool commits memory on demand and depending on the requirement, it calculates its Target Memory, the amount of memory it thinks it should commit before the server goes into memory pressure.
Unlike SQL 2000, Performance Monitor SQL Memory Manager:Total Server memory counter does not indicate the total SQL memory, but only the BPool usage.
Buffer Pool is a cache for Data Pages. Its primary purpose is to cache SQL Database pages, but in addition to caching data pages, other memory consumers(clerks) can borrow memory from the Buffer Pool. This memory is always in 8KB chunk and is not always contiguous. In other words, the buffer pool can be used as an underlying memory manager for other SQL Server memory clerks, as long as they allocate memory in the 8KB size.
In SQL 2005, during Startup, the SQL OS, configures the Buffer Pool to use single page allocators, which allows the Buffer Pool to provide for all Single page Allocations in SQL. In other words, all single page allocations come from the BPool.
To sum it up: During startup, the SQL OS allocates the Buffer Pool using the VM/AWE allocators (and not the page allocators, so in theory BPool is nothing but a large chunk of reserved memory) and configures it to use the Single Page Allocators. Once this is done, any of the memory clerks which need memory in the 8KB boundary, they would allocate the memory from the BPool (stolen pages). If the clerks require memory in large buffers (not in 8KB boundary), in those case, the Multi Pages Allocator or the virtual allocators and these would be allocated outside the buffer pool, area fondly called as the Mem-To-Leave.
In the next post in this series, I will talk about the NUMA support in SQL 2005 and how it affects SQL memory.
SQLUNINTERRUPTED 