I am just a medium, SQL Server the Goal

Monthly Archives: June 2012

SQL Server Memory–Rise of the Clerks – Part 2


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,

  1. Lifetime control: As the name suggests, it provides time management for the entries.
  2. 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.


SQL Server Memory–Rise of the Clerks – Part 1

Pervious Posts in the Series

SQL Server Memory – Underground

SQL Server Memory – The Evolution

SQL Server 2005 introduced many changes to the Memory Architecture. Majority of these architectural changes continued till SQL Server 2008 R2. In this post, I will try to cover a high level overview of architectural changes introduced in SQL 2005.

The New Architecture

The new memory architecture consists of 4 major components

  1. Memory Nodes
  2. Memory Clerks
  3. Memory objects
  4. Memory Broker

Slava Oak’s has a blog post explaining these components in details. Most of what follows is an attempt at simplifying what Slava mentions his blog.

Memory Node: Memory nodes are internal SQLOS objects. The number of memory nodes depends on the NUMA configuration of the server. Memory nodes can be thought of as a large pool of Memory, which different components (Clerks) can allocate memory from. Memory Nodes expose a set of Allocators which are used by all memory Clerks to allocate the desired amount of memory from the Node.

  1. Page Allocators: There are 4 primary types of page allocators exposed by the memory node. These allocators are used to allocate a set of 8KB pages. Remember that SQL Server page granularity is 8KB and any memory allocated by these page allocators would always be in multiple of 8KB.
    • Single Page Allocator: As the name suggests, this allocator is used to allocate one 8KB page at a time.
    • Multi Page Allocator: Allocates Multiple contigous 8KB Pages
    • Large Page Allocators: These are used to allocate Large Pages. This feature is only present in IA64 or X64 editions of SQL, running on servers with more than 8GB of memory. Trace Flag 834 has to be enabled in order for SQL to use Large Pages. On a X64 server the Large Page granularity is 2MB, while on IA64 its 16MB.
    • Reserved Pages: Is special purpose allocator, which allocates a set of pages for emergencies. Typically the “Fail Safe” memory for SQL.
  2. Virtual Memory Allocator: Uses Windows VirtualAlloc API’s, when SQL needs to allocate contigous memory which does not necessarily fall in the 8K page boundary.
  3. Shared Memory Allocator: Uses windows file mapping API’s and provides LPC (shared memory) capabilities.

DBCC memory status gives some information about the Memory Nodes, but they are NOT exactly memory node but CPU nodes.


Locked Pages allocated would only show up when SQL Server has been configured to use Lock Pages in memory.

Memory Clerks: Clerks are consumers of memory in SQL. In general there are 4 kinds of clerks, generic, cache store, object store, user store. The clerks make use of the memory node allocators to allocate memory. SQL has a vast number of Clerks predefined, and each of these clerks corresponds to the type of allocation which needs to be done. SQL exposes the clerk information using the “sys.dm_os_memory_clerks” DMV. This information is also exposed through the DBCC MEMORYSTATUS. In the memory status output one can see the clerks being listed multiple times. This is because of the fact that the clerks are created per NUMA node. So if we have 4 NUMA nodes on the machine, we could see the clerks being reported 4 times.

Memory Objects: Memory objects are the memory allocations made through the clerks. There are 3 kinds of memory objects which are supported by SQL. Memory objects can be viewed using the “sys.dm_os_memory_objects” DMV.

  1. Variable Memory Object: Supports variable size memory allocations.
  2. Mark/Shrink Objects: This allocations are typically done in two stages. During the first stage the allocations grows in size (more and more memory are added as part of this allocation) and during the second phase the memory is de-allocated. As Slava points out, these type of allocations are very useful in scenarios like compilation or execution.
  3. Fixed Size Memory objects: Supports memory allocations which are fixed in size.

Memory Broker: Memory broker can be though of as the Ring Master in a Circus. The purpose of Memory Broker is to provide a centralized mechanism to distribute memory or control the allocations made by each component in SQL Server. Memory Broker monitors the demand consumption of memory by each component and then based on the information collected, it calculates and optimal value of memory for each of these components. This information is then broadcast to each of the components, which grow or shrink their usage as required. Memory broker is exposed through the DMV “sys.dm_os_ring_buffers” where the Ring_buffer_type is “RING_BUFFER_MEMORY_BROKER”.

To tie these pieces together. 

In order for the Clerks to allocated Memory, they have to create a memory object using one of the Allocators exposed by the Memory Node. These allocations (memory objects) could be of any type as mentioned above. This entire allocation and de-allocation is monitored by the memory broker, which redirects the clerks to grow/shrink their memory usage as required.


In the next post, I will talk about the memory clerks in more details.