How It Works: SQL Server 2008 NUMA and Foreign Pages

I have received numerous questions about 'foreign pages' so I have put this post together to answer the questions.

The SQL Server buffer pool goes through three (3) distinct states.

Initial / Ramp-up	This state is established during buffer pool initialization and only transitions once committed buffers reaches the target. Once Committed >= Target the state is transitioned to Release Away Buffers.
Release Away Buffers	All the away lists in the buffer pool nodes are returned (decommitted) to the operating system. Once all buffers are returned the state transitions to steady.
Steady	The final, running state for the buffer pool. In this state the away pages are not longer tracked. New committals are assigned to the buffer pool node and the physical locality is no longer tracked.

Syntax Specifics

Foreign	SQL Server counter showing the number of 8K buffers that are associated with the buffer pool node that are NOT from the same physical NUMA node association. Locality of these pages are deemed remote.
Away	The subset of foreign buffers that will be released to the operation system during the buffer pool state of 'Release Away Buffers.'
Remote	A physical allocation from a NUMA node that is not the current node.

Initial Ramp-up

The following is an example of a 2-physical node NUMA system. During the initial state the buffer pool is growing towards the commit target. SQL Server commits memory and sorts it during this state. If the physical memory node id assignment of the memory is the same as the buffer pool id the memory is placed on the free list for immediate use. If the memory is from a remote node it is added to the away list and is not available for use. This does mean that the RAM is ‘set-aside’ until the committed target is reached. SQL Server does this to improve performance by keeping memory local to the buffer pool node and reduce remote memory accesses.

 

Notice that the committed and foreign counts are always incremented but the away pages is only incremented when the buffer pool is in initial state. When looking at output such as dbcc memorystatus you can compare the total committed value to the Database, Stolen and Free counts to get an estimation of the possible foreign buffer count. The committal increment is needed so the buffer pool accounts for those buffers that are local to the node and away and SQL Server does not over commit.  

Release Away Buffers  

Once the buffer pool committed >= target the state transitions to release away buffers and all lists are immediately drained. This occurs within a than a few seconds, it is a tight loop to decommit the memory on the away lists, and quickly transition to the steady state.

During this small window SQL Server is not allowed Growth. SQL Server has to reach the target to transition to the release away buffers state and as we release the buffers we don’t want the physical memory high notification to kick off additional growth until we are sure the buffer pool as achieved a steady state.  

Steady State  

When steady state is achieved the foreign counter indicates those buffer pages assigned to the buffer pool node that are physically remote to the buffer pool node. The away lists are no longer used as SQL Server has attempted a valid sort of memory to the local buffer pool nodes. Any growth that takes place during steady state is assigned to the requesting node and if remote to the buffer pool node the foreign counter will reflects the fact.

What does all this mean to me?  

1. If you see foreign pages (more than a handful) the operating system has been handing out an imbalance of local vs physical memory. SQL Server is trying to sort the memory in a reasonable way but remote access is possible.

2. Once target is reached no more tracking of away buffers occur so future allocations could be from foreign, physical nodes and reflected in the foreign counter(s).

3. The DMV select * from sys.dm_os_buffer_descriptors contains the numa_node columns but does not show you the internal, foreign buffer status.

This does solidify the advice that you may want to consider setting min and max server memory to the same value on a NUMA system to limit foreign page possibilities as the buffer pool grows and shrinks. Allowing shrink and grow activity once reaching steady state might cause additional foreign memory assignments that might lead to foreign memory access performance changes.  

Other Performance Counters

The following counters are incremented when the 'get' call is made to the buffer pool to satisfy access to a specific database page.

Counter	Description
Buffer Node: Local node page lookups/sec	Database page reads and writes that are satisfied from the local buffer pool node.   Note: This does not indicate if the memory for the page is local or remote to the node. It is possible the physical memory allocation is remote/away/foreign but assigned to the node in a steady state so the server is still doing a remote memory access.
Buffer Node: Remote node page lookups/sec	Database page reads and writes that are satisfied from the remote buffer pool node.   This is normally a physical, remote memory access because of the sorting that SQL Server performs.

 Bob Dorr - Principal SQL Server Escalation Engineer