I am just a medium, SQL Server the Goal

Category Archives: Memory

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.

SQL Server Memory–The Evolution

Previous Posts in this series

SQL Server Memory– Underground

In my Previous post, I had talked briefly about the windows Memory Management and how SQL calculates its Buffer Pool and MTL. In this post, I would be talking mainly about the workings of the SQL Buffer Pool and the MTL, and how to figure out the memory usage by SQL and its components. This post would be specific to SQL 2000 unless specified otherwise.

SQL Buffer Pool

SQL Memory Management is Dynamic in nature, meaning SQL can allocate or de-allocate memory in the Buffer Pool as per requirement. In order for the SQL Server to effectively manage its memory allocations SQL Server 7, introduced a layer of Memory Manager that manage various types of memory requests in the SQL Buffer pool.

The important ones are

  1. General – This memory manager handles general allocation (not handled by other managers). Includes things like locks etc..
  2. Query Plan – As the name implies, this is mainly used for query plans.
  3. Optimization – Handles memory requests during optimization.
  4. Utility – Manages Memory allocations for different utilities, like tracing, log shipping etc.
  5. Connection – Manages memory allocations for Connections. This includes memory for the input and output buffers.

The idea behind using these managers is very simple. Consider an Analogy. In a company with 1000 employees, If there is only one manager, management becomes really difficult. One the second hand if we have 10 managers managing 100 employees each, it would be easier. With multiple managers it becomes easier to manage and report the usage.

The amount of memory being consumed (out of the 2 GB user mode VAS), can be easily tracked using either of the following options.

  • Windows Performance Monitor Counters


The KB article describes the output of the DBCC MEMORYSTATUS command for SQL 2000 in great detail. The following section of the DBCC MEMORYSTATUS output can be used to track the usage per Memory Manager.


The OS reserved/OS committed/OS in Use sections are for all allocations which have been made in the SQL VAS  by the memory managers (more than 8 KB allocations) and also for all external allocations coming from external components loaded in the SQL Space. In short, this can be used to track all the allocations made in the Mem-To-Leave area.

Apart from the output for the Memory Managers, another important section in the output is the first section of the output, which can be used to see how are the Database Pages allocated in the BPool.

Windows Performance Monitor Counters

SQL Server exposes two Performance Monitor Objects for monitoring SQL Server Memory. These performance Monitor counters are also applicable to next releases of SQL Server (SQL 2005/2008/2008 R2) with slight differences, which I would be pointing out in the next posts.

  • Memory Manager: Provides information about SQL Server memory as whole.
  • Buffer Manager: Provides information about the SQL Buffer Pool

Memory Manager

The important counters under this object are

  1. Connection Memory: Indicates the amount of memory being consumed under the Connection Memory Manager.
  2. Granted Workspace Memory: Memory allocated/reserved for operations during Query Execution. All memory for sorting, hashing come from these.
  3. Memory Grants Pending: Number of memory requests which have not been completed. A non-zero value for a sustained period would indicate a memory pressure in SQL Server.
  4. Optimizer Memory: Memory being consumed under the Optimizer Memory Manager.
  5. Total Server Memory: Total memory consumed my SQL. This included both allocation in BPool and Allocations in Mem-To-Leave. This value should be equal to the sum of the “OS in Use” under dynamic memory manager, and “Committed” under Buffer Count sections of the Memory Status values. Remember these values in Memory Status output are in number of 8 KB pages.

Buffer Manager

  1. Checkpoint Pages/Sec: Number of pages being flushed by the checkpoint thread.
  2. Database Pages: Number of BPool buffers being used for Database pages.
  3. Free Pages: Number of pages free in the buffer pool. This values should always be greater than 0.
  4. Lazy Writes/Sec: Number of lazy write operations per sec.
  5. Stolen Pages/Sec: Committed pages which are not being as a database page are termed as stolen.
  6. Total Pages: Number of pages currently being used in the Buffer Pool.

Memory discussions for SQL Server is not complete without the mention of two very important operations, Checkpoint and Lazy Writer.

Checkpoint: Checkpoint is the process wherein Dirty Buffer (database pages which have been modified by a DML operation) back to the disk.

Lazy Writer: The sole purpose of Lazy Writer is to maintain some free buffers in the SQL Server Buffer Pool. Lazy writer runs periodically and check which buffers can be flushed and returned to the free pool.

SQL Server Memory– Underground

With SQL Server 2012 there has been considerable changes in the way Memory is allocated and utilized. In Part 1 of the SQL Server Memory series I would talk about memory configurations on windows Servers and then discuss how memory works for SQL Server 2000.

All windows Application (32/64 bit) are designed to work with the Virtual Memory concept in Windows. Each process is provided with a fixed size virtual memory, regardless of the amount of RAM on the machine. The VM limits (a.k.a the maximum addressable memory limit) for an application is as follows

  1. 32 bit Applications: 4 GB
  2. 64 Bit Applications: 16 TB

Each application running on Windows has to comply with the Virtual memory Limits as imposed by the architecture.

This Virtual Memory(hence forth referred as Virtual Address space) is divided into 2 regions “User Mode” and “Kernel Mode”. An Application works with only the user mode. In other words they can only play around with the User Mode VAS, and do not have any control on the Kernel Mode.


On a 32 bit(x86) environment the User Mode is 2GB (8 TB in 64 bit environment) and can be increased to 3GB (reducing the kernel mode to 1 GB) using the /3GB switch in the Boot.ini file. With windows 2003 servers a new boot.ini switch was available which allows the user mode to be configured to a value between 2 GB and 3 GB, depending on the user requirements.

On a 64 bit environment, the VAS is practically limitless (8 TB) and hence the application can potentially access all the memory on the Machine.

Physical Memory allocations and usage is directly controlled by some components which runs in the Kernel Mode (Windows Kernel Code). A User Application has no control on the Physical RAM. In order for the Application to access Physical Memory, it provides the windows Memory Management Unit(MMU) with a Virtual address (4 Byte – 32 bit address in the VAS) which is then translated (address translation) by the MMU into a physical address. Windows then reads the content at the physical address and returns it back to the application.

An Application running with default configurations (without AWE in case of 32 bit applications) cannot access more memory than allowed by the size of the User Mode VAS.

SQL Server like all other windows application follows this Virtual Memory concept. Hence a 32 bit SQL Server by default cannot access more than 2 GB of RAM.

SQL Server divides its User Mode VAS into two section. The Buffer Pool and Memory to Leave (MTL region). The MTL is a free memory region inside the SQL VAS, and is used for the following

  • Large allocations from SQL Server: These are allocations that request more than 8 KB.
  • CLR allocations: These allocations include the SQL CLR heaps and its global allocations that are created during CLR initialization.  (SQL 2005 Onwards)
  • Memory allocations for thread stacks in the SQL Server process.
  • Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL Server process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.


The Buffer Pool is a pool of 8K buffers (memory pages) which are used by SQL Server for its operations. Any allocation in SQL Server which requires 8 KB or less memory comes from BPool. SQL Server Data Cache is created in the BPool.

The high level steps performed by SQL (32 bit) during startup is are follows

  1. Calculate the amount of RAM on the machine, this sets the upper limit of the SQL Buffer Pool (aka BPool).
  2. Reserves the MTL as a single block of memory. In SQL 7/SQL 2000, this value is set to 256 MB by default can be configured using the –g startup parameter. Along with the 256 MB, SQL also reserves space for the thread stack. In SQL 7/SQL 2000, the max number of worker threads (controlled by the Max Worker Threads Configuration option) is set to 256 threads by default. Each thread requires 512 KB of memory. Which Means

    Size of MTL = 256 + (.5 MB * 256) = 384 MB

  3. Once the MTL is reserved, SQL tries to reserve the Buffer Pool. The size of the SQL Buffer Pool is calculated as per the below equation
    Size of BPool = Min ( Min(SizeOfRAM, MaxServerMemorySetting), (VAS-MTL-BufArray-Overhead Memory) )


    SizeOfRAM - Amount of physical memory

    MaxServerMemorySetting - value Max Server memory configuration option

    VAS - Size of User Mode VAS

    MTL - Size of MTL

    BufArray - An array of Buf structures requires to manage the BPool. Approximately 9MB/GB of RAM

    Overhead Memory: Memory used in loading the DLL's and other executable.

  4. If BPool reservation fails for some reason, SQL would not start.
  5. Once BPool is reserved, SQL Marks the MTL area as free. This is to ensure that external components can access this memory area.

On a 32 bit environment, the VAS is only 2 GB and thus forms the limiting factor while calculating the size of the buffer Pool. adding more RAM on a 32 bit environment does not help SQL because as the RAM increases, so would the size of the BufArray (~9MB/GB of RAM), which in turn would reduce the size of the BPool. For example on a machine with 64 GB of RAM and Max Server memory set to 50 GB,  the BufArray would be approximately about 576 MB. Which means the BPool Calculation would be

BPool = Min ( Min (64 GB, 50 GB) ,  (2048 MB – 576 MB – 384 MB – ~10 MB)) = 1100 MB.

As the RAM on the machine increases the BPool would continue to become smaller and can cause a lot of performance issues.

For the 64 bit environment the following changes are applicable.

    1. Each thread requires 2 MB for the thread stack.
    2. The size of the BPool would always be limited by the amount of RAM or the max server memory setting.
    3. The VAS is 8TB and not 2GB as in the case of a 32 bit SQL.
    4. If Max Server Memory is not set, SQL can consume almost entire memory on the machine.

As mentioned, a 32 bit SQL running on a 32 bit Windows Server by default can only use 2 GB of memory. In order to allow SQL to use more than 2 GB of memory, you would need to use one of the following techniques

  1. Use /3GB setting or /USERVA switch. DO NOT use this setting on a machine with more than 16 GB of RAM.
  2. Use AWE with SQL Server.

In the next post, I would be talking about how memory operations works in the BPool and MTL.


  1. How to configure SQL Server to use more than 2 GB of physical memory
  2. Address Windowing Extensions
  3. Managing AWE Memory