共用方式為

As The World Turns: SQL Server NUMA Memory Node and the Operating System Proximity

  • 發行項

It felt a bit like ‘As The World Turns’ as I unraveled how the following worked so the title is a bit of a tribute to my grandmother.  She could not miss here ‘stories’ in the afternoon.

Proximity

Before I dive into the details I would like to talk about NUMA node proximity.    The idea of proximity is how close is one NUMA Node to another based on the memory layout of the system.

For example Node 00 is 0 steps from itself.   However, it is 3 steps from Node 03.   Node 01 is 2 steps from Node 3 and so forth.

Node 00 01 02 03
00 00 01 02 03
01 01 00 01 02
02 02 01 00 01
03 03 02 01 00

K-Group (Processor Group)

Windows attempts to gather the nodes, based on proximity, into the same K-Group

Examples

Here is a snippet from the SQL Server error log, 128 CPU, 8 Node system.  

Notice nodes 0,1,2 and 3 are aligned to K-Group = 0.

image

Notice the change in node to group alignment on this same system.image

So What Does This Mean?

For this specific system the second configuration, aligning nodes 0,1,4 and 5 is not optimal.   This system’s true (SRAT) proximity is the layout from the first example.   Nodes 4,5,6 and 7 are in a separate blade, using NUMA node glue based architecture.    Memory accesses between the hardware blades can be slower then local blade access.

The SRAT table provided by the hardware level, BIOS contains the proximity information.   Prior to Windows 2012 Server the Windows memory manager performs memory access tests, attempting to determine optimal proximity responsiveness and can override the SRAT information.

The second example is after the machine had a hardware failure on nodes 2 and 3.   The nodes were taken offline and Windows adjusted.  However, once the problem was corrected the system maintained the adjusted proximity layout.

The hardware manufactures are aware of this behavior and have specific tuning instructions per system, CPU type, memory layout, etc… that establishes the appropriate override of the Windows Server behavior using the Group Affinity registry key: https://support.microsoft.com/kb/2506384

These advanced configurations can involve additional processor groups to further sub-divide the current SRAT layout in order to obtain optimal performance.

For SQL Server installations, running on NUMA hardware I recommend you contact your hardware manufacture and obtain the optimal proximity settings.

Reference: CoreInfo.exe  https://technet.microsoft.com/en-us/sysinternals/cc835722.aspx 

Bob Dorr - Principal SQL Server Escalation Engineer