Overview of memory management components in SQL Server

The material is devoted to the description of the use of the memory subsystem in MS SQL server. This review gives only a general idea of ​​the management structure. It should be remembered that Microsoft products are delivered with closed codes and detailed information is not available in public sources (as far as we were able to find out, if you were able to do more, please let us know). A general overview is needed to understand the possible SQL server problems described below and the testing and performance measuring tools used.

Memory manager


Memory manager (MM) is the main element that controls memory allocation in SQL Server. This component automatically allocates memory available to SQL server, reducing the need for manual configuration. After loading SQL, MM determines the initial amount of distributed memory and then, as the load changes, it dynamically reserves or frees up RAM. Thus, MM manages the interaction of the SQL server with the operating system in the context of memory management. Memory manager is part of SQLOS. More details can be found here .

Composition of Memory Manager


Information about the composition of this component is very limited, but the following components of MM can be distinguished: memory nodes, memory clerks (memory clerks), memory caches (caches) and memory objects (objects). More details can be read here and here .

MM also provides several counters that allow you to evaluate the use of RAM in SQL. More details can be read here and here .

The generalized composition of MM is presented in Figure 1.1.

image
Figure 1.1. Memory Manager components

MM objects are used to allocate memory within an instance of SQL Server. Memory nodes provide an interface to the OS and low-level memory allocation. Inside SQL Server, only Memory clerks have access to the interface. Memory nodes for memory allocation. Each SQL Sever component that consumes a substantial amount of memory must create its own clerk and allocate memory through its interface.

The implementation of memory management varies from version to version of SQL, however, the main functional components are retained. Figure 2.2 shows the differences in the MM implementation in SQL2008 and SQL2012; more information can be found here .

image
Figure 1.2 Changes in the Memory Manager structure for SQL2008 and SQL 2012

From the figures, the separation of the page allocator has completely disappeared. These components have been replaced by a single Any-size page allocator.

Memory nodes


Memory Nodes is an internal SQLOS object. Represents a logical memory object that corresponds to a processor in the case of an SMP implementation or a group of processors in the case of a NUMA implementation. More details can be found here .

image
Figure 1.3 The SQLOS hierarchy in the case of the implementation of SMP (A - figure) and NUMA (B - figure)

Memory node is absolutely transparent for memory consumers. The main task of this component is to determine the memory allocation area. The memory node consists of several memory allocators. Figure 2.4 shows the memory consumers using the memory node. More details can be found here and here .

image
Figure 1.4 Memory nodes

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

Memory clerks


Memory nodes provide an interface to the OS and implementation of memory allocation at the Windows level. Inside SQL Server, only Memory clerks have access to the Memory nodes interface for memory allocation. Each SQL Sever component that consumes a significant amount of memory must create its own clerk and then allocate resources through its interface.

Thus, clerks perform the following functions within the framework of the Memory manager:
• Reflect memory usage by specific server components
• Receive notifications about changes in memory states and change its size according to circumstances.
• Use Memory nodes to allocate memory to server components.

There are four categories of clerks. The list of categories is given in table 1. More details can be found here..

Memory caches


The term “cache” refers to the caching mechanism of various types of data, taking into account the cost of storing objects. The cache provides: control of storage time, visibility and analysis of statistics on access to cached data. Cached data can be used simultaneously by several consumers. In addition to caches, SQL Server uses memory pools. Pools, unlike caches, are used to store uniform data without additional control. Several caching mechanisms are used and the main ones are:
• Cache Store
• User Store
• Object Store

Only the Object Store is a pool, Cache and User Store are caches. The mechanisms of Cache and the User Store are very similar, but if the parameters of the Cache Store are controlled entirely by SQLOS, then for the User Store developers can use their own control algorithms. The documentation also uses the concepts of Cache Store and User Store with the meaning “separate memory areas”. Each Cache Store has a hash table repository associated. It is possible to use not one but several hash tables. A hash table is an in-memory structure that contains an array of pointers to buffer pages. Hashing is a technique that uniformly maps the key value to the corresponding hash bucket.
You can read more here (about the hash) and here and here andhere .

Cache Store are used, for example, to store the execution plan cache, xml cache, full-text search cache, Procedure Cache, System Rowset Cache. In the User Store, in particular, a cache of metadata of user and system databases, security tokens, and scheme data are stored. A complete list can be found in the dynamic view of dm_os_memory_cache_counters.

Buffer pool (buffer cache) as well as buffer pool extension


Buffer pool (second name buffer cache) is an area in memory that is used to cache pages, table data and their indexes, page size is 8Kb. Usage Buffer pool reduces input / output to the database file and thus increases server performance. At the same time, Buffer Cach is the main memory consumer in SQL Server.

image
Figure 1.5 Components of a buffer management system

In SQL Server 2014, a buffer pool can be expanded into non-volatile memory, such as an SSD. This extension is called the Buffer Pool Extension. Read more here .

You can read more about buffer cache management here , here and here .

The buffer cache has its own memory clerk and will allocate memory through the page allocator. For the buffer cache, a Generic type clerk is used and is called memoryclerk_sqlbufferpool.

Prior to SQL 2012, Buffer Cache used only Single Page Allocator (distributing individual 8Kb pages). If the server component needed to allocate a buffer larger than 8Kb in size, the Multi Page Allocator was used (see Figure 2.4) and, accordingly, this memory was located outside the Buffer Pool. C SQL2012 Single and Multi Page allocators have been combined into Any-size page allocators. In fig. 2.2 you can see these changes.

Max server memory and min server memory


Although the buffer cache is managed automatically inside SQL Server, administrators can adjust the maximum and minimum size of the shared memory for this buffer.

image
Figure 1.6 Changes in the memory range reserved by the Max server memory parameter

As mentioned in SQL 2012, memory manager changes took place. As a result of such changes, the max server memory parameter regulates not only the buffer pool memory, but generally the entire memory allocation (except for Direct Memory Allocations made using VirtualAlloc).

The min server memory parameter indicates the boundary below which the Buffer Pool will not free up occupied memory on demand. At initial loading, the pool does not take up the memory specified in min server memory. The minimum required volume is used, which is calculated automatically. The size of the pool, if necessary, further increases.

More details can be read here and here .

Stolen pages


Stolen pages are buffer cache pages that are used for various purposes in the server. For example, for the procedural cache, sort operations (i.e., the workplace memory of the request). Also, these pages are necessary for storing data structures that require a memory allocation of less than 8Kb, for example, locks, transaction context, and connection information.

More details can be found in the following sources:

simple-talk.com ;
technet.microsoft.com ;
sqlserver-dba.com .

Object store


The Object Store is a memory pool. It is used to store uniform data types without additional control over the cost of storage. This data can be easily cleaned in case of out of memory. By their structure, pools are memory clerks (i.e., they are one of its types). Additionally you can see here and here .

Memory Objects (MO)


Memory Objects are a bunch of memory that uses the memory clerks interface to access the page allocator to allocate pages. Memory Objects do not use virtual or shared memory interfaces; this element uses only the page allocation mechanism. Many SQL Server components access MO directly, bypassing memory clerks. MOs provide the ability to allocate memory ranges of arbitrary size.

More details:
http://blogs.msdn.com/b/slavao/archive/2005/02/11/371063.aspx ;
http://support.microsoft.com/kb/907877/en-us ;
http://msdn.microsoft.com/en-us/library/ms179875.aspx .

Memory Broker (MV)


Memory broker is a component of SQLOS. A memory broker is responsible for allocating memory between various SQL Server components according to their requests. More details can be found on the manufacturer's website .

image
Figure 1.7. Memory Broker memory allocation.

Mechanism description: MB monitors memory requests from SQL components and compares its current usage indicators with current ones. Based on the information received, the broker calculates the “optimal” size of memory that can be distributed between the components. The broker notifies the components of its calculations, after which each component uses this information for further use of memory.

MV can be seen in the following dynamic views “sys.dm_os_ring_buffers”, where Ring_buffer_type = “RING_BUFFER_MEMORY_BROKER”. More details can be read here , here and here .

Resource semaphore


Resource Semaphore are responsible for allocating memory to the component and maintaining the total size of the used memory within the set limit. See blogs.msdn.com and
mssqltips.com for more details .

Also popular now: