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.

Syntax Specifics

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.

 Bob Dorr - Principal SQL Server Escalation Engineer