Growing and Shrinking the Buffer Pool Under NUMA
This topic describes how pages of memory from the buffer pool are assigned when using non-uniform memory access (NUMA). Use this information to understand how SQL Server uses NUMA and to understand how to interpret the counters of the buffer node object.
Distribution of Memory
There is one SQL Server memory node for each physical NUMA node. The memory nodes grow independently of each other, but divide the memory evenly. To show the local vs. foreign memory distribution in SQL Server, this topic uses an example that assumes the computer has 16 gigabytes (GB) of memory. Other applications including Windows have consumed some of the memory from each node, SQL Server has assigned some memory for its processes outside of the buffer pool, and SQL Server has 10 GB of memory to assign to the buffer pool. The buffer pool memory is divided among four physical NUMA nodes, N0, N1, N2, and N3, each with the following local memory available:
N0 – 1 GB
N1 – 3 GB
N2 – 3 GB
N3 – 3 GB
In the above configuration, all nodes will eventually allocate and use 2.5 GB of memory; however, node N0 will end up with 1.0 GB of its own memory, and 1.5 GB of memory from other nodes.
Memory Assignment at Startup
When using NUMA, SQL Server receives memory from the operating system at a rate comparable to a non-NUMA system, even if initial free memory is unevenly distributed among nodes. The buffer pool attempts to get as much local memory for each node as possible; however this is difficult because Windows does not currently have an API to allocate memory from a specific node.
As memory is assigned to SQL Server, you may observe that some nodes are given many pages from other NUMA nodes (called foreign pages). However, these pages are not used during ramp-up because they can frequently be transferred to the owning node and become local to that node. When the value of max server memory is reached, some nodes may have foreign memory, but once the memory target is achieved, the buffer pool will treat local and foreign memory identically. For example, under memory pressure, the buffer pool will not make any effort to free up foreign memory pages before local memory pages.
Limiting Memory to Specific Nodes
If SQL Server has been configured to run on a subset of the available NUMA nodes, the buffer pool will not automatically be limited to the memory on those nodes. In this case, use the max server memory option to limit the buffer pool. For information on max server memory, see Server Memory Options.
Releasing Memory from a Node
When using NUMA, the max server memory and min server memory values are divided evenly among NUMA nodes. For example, if you set max server memory to be 16 GB on the system with four nodes, the buffer pool allocates 4 GB of memory on each node. If you take one of the nodes offline by changing the affinity mask setting, the max server memory setting will be redistributed among the remaining nodes. For example in the previous four node example, if you take two nodes offline, the released eight GB of memory will be evenly distributed amongst remaining nodes. Since the buffer pool is capable of using foreign pages, remote memory will be utilized if there not enough memory on the remaining nodes. If you want SQL Server not to use memory from the nodes it is no longer running on, you must decrease the max server memory setting after moving the nodes offline.
Foreign Pages
Nodes function largely independently of each other. All memory allocations and deallocations of memory for the node are done using the memory associated with the node. However, if a worker running on node N1 needs to access a database page that is already in node N2's memory, it accesses the non-local memory.
Observing Local vs. Foreign Memory in the Buffer Pool
The buffer pool can be observed by viewing the Buffer Node object. Total memory in the buffer pool for SQL Server is displayed as the Target pages counter of the Buffer Manager object. The memory in the buffer pool for each node is displayed as the Target pages counter of the Buffer Node object. Memory from other nodes is displayed as the Foreign pages counter. For more information, see SQL Server, Buffer Node Object and SQL Server, Buffer Manager Object.
Each Node Checkpoints Its Own Memory
Each memory node has its own lazy writer thread. This thread is called both for implicit and explicit checkpoints. Since a symmetric multiprocessing (SMP) computer has only one checkpoint thread, the multiple threads when using NUMA result in an increase of the speed of the checkpoint.
Table Scan Behavior
A table scan running on Node N1 will only fill the memory associated with node N1 unless the scan is being run in parallel on CPUs from multiple nodes. If the scan is running exclusively on a single node, only the buffer pages from that node will be used. This helps partition the workload for an application.