다음을 통해 공유

Memory management components in SQL Server

  • 아티클

The following article provides only a general idea of the memory management structure. Keep in mind that Microsoft's products are delivered with closed-source and detailed information is not available from generally accessible sources (as far as I know, if you have more, please add).

Memory manager

Memory manager is the main component that controls the memory allocation in SQL Server. This component automatically allocates the available SQL Server memory, reducing the need for manual configuration. After downloading SQL Server MM specifies the initial amount of shared memory and then, as the load changes, dynamically allocates or frees memory. Thus, the Memory manager manages the interaction of SQL Server with the operating system in the context of memory management. The memory manager is part of the SQLOS. You can find more detailed information here.

Memory Manager Structure

Memory manager consists of the following components:

  • memory nodes
  • memory clerks
  • memory caches
  • memory objects

You can find more detailed information here and here. 
The MM also provides several counters that allow estimating memory usage in SQL Server. You can find more detailed information here and here.
The following image demonstrates Memory manager components.


Memory manager objects are used for memory allocations within the SQL Server instance. Memory nodes provide the interface to the OS and the memory allocation on the lower level. Inside SQL Server, Memory clerks only have access to the interface.  Memory nodes are used to allocate memory. Each SQL Sever component, which consumes a substantial amount of memory must create its own clerk and allocate memory precisely through its interface.

Memory management implementation varies from version to version, but the main functional components are preserved. The following image shows the differences in implementation of Memory manager in SQL2008 and SQL2012. You can find more detailed information here.


As you can see from the picture, the page allocator components were replaced with a single any-size page allocator.

Memory Nodes

Memory Nodes are inner SQLOS objects. They represent a logical memory objects. The following picture demonstrates the SQLOS hierarchy. The SMP implementation on the left side and the NUMA implementation on the right side. For more detailed information, read this article.


Memory node is completely invisible for consumers of memory. The main goal of this component is to define the area of memory allocation. Memory node consists of several memory allocators. The following picture demonstrates memory users that utilize the memory node. You can find more detailed information here and here.


Memory allocators are procedures that determine the type of Windows API used to allocate memory. Allocators contain programming code used for memory allocation, for example, for pages or shared memory.

Memory clerks

Memory nodes provide the interface to the operating system and implement the memory allocation at the Windows level. Inside SQL Server, the only Memory clerks have access to the Memory nodes to allocate memory. Each SQL Server component, which consumes a significant amount of memory must create its own clerk and then allocate resources through its interface. Thus, the clerks perform the following functions (within the Memory manager):

  • Display the memory usage by specific SQL Server components.
  • Receive notifications of memory states and change its size according to the circumstances.
  • Use Memory nodes to allocate memory to server components.

There are four Memory clerks categories. You can find more detailed information here.

Memory Caches

The "cache" term refers to various types of data caching mechanisms, taking into account the cost of object storage. The cached data can be used simultaneously by multiple users. Beside caches SQL Server also uses memory pools. The memory pools (unlike caches) are used to store consistent data without any additional control. There are several key caching mechanisms:

  • Cache Store
  • User Store
  • Object Store

The Object Store is the only pool, Cache and User Store are caches. The User Store and Cache mechanisms are quite similar. However, developers can use their own control mechanisms to manage User Store, while the Cache Store parameters are controlled entirely by SQLOS. The documentation also uses the notion of Cache User Store and Store with the "separate memory space" value. Each Cache Store is mapped to a hash table. It is allowable to use more than one hash table. The hash table is an in-memory structure that contains an array of pointers to the buffer pages. You can find more detailed information here, here and here.

Buffer pool (buffer cache) and buffer pool extension

Buffer pool (buffer cache) is a memory area that is used to cache pages (page size is 8 KB), data tables and their indexes. The use of the Buffer pool reduces the I/O to the database file, and thus increases the performance of the server. The Buffer Cache is the main memory consumer in SQL Server. The following picture shows buffer management system components.

The buffer pool in SQL Server 2014 can be expanded in a non-volatile memory (e.g. an SSD disk). This expansion is called the Buffer Pool Extension. You can find more detailed information here.