NUMA Scenarios
On computers that have many CPUs, nonuniform memory access (NUMA) hardware can significantly improve performance by pairing dedicated memory with CPUs. This topic presents some configurations of NUMA, CPU affinity, and connection affinity that can further improve performance for specific scenarios. The following settings affect these configurations:
NUMA hardware is provided by the computer manufacturer.
Soft-NUMA is set using the registry. For information on configuring soft-NUMA, see How to: Configure SQL Server to Use Soft-NUMA.
CPU affinity is set using the affinity mask option.
Port to NUMA affinity is configured as a server setting using SQL Server Configuration Manager. For more information, see How to: Map TCP/IP Ports to NUMA Nodes.
Useful Scenarios
When using NUMA, the following scenarios are frequently encountered.
A. No port to NUMA affinity
This is the default condition on a computer with hardware NUMA and a single instance of SQL Server. All traffic enters through a single port and is distributed on a round-robin basis to any available NUMA node. NUMA improves the locality of memory and CPU access and increases the number of I/O and lazy writer threads. Connections, once established, are scoped to that node. This provides automatic load balancing among the NUMA nodes. Client applications can connect to a single port, and are easy to deploy.
.gif "A connection uses any NUMA node.")
B. Associating a single port to multiple nodes to provide improved performance for a priority application
Affinitize one port to several hardware NUMA nodes to serve a major priority application. Affinitize a second port to another hardware NUMA node to serve a second minor application. The memory and CPU resources for the two applications are fixed in the unbalanced manner, providing the major application with three times the local memory and three times the CPU resources of the minor application. The minor application can be a second instance of the Database Engine, a less important function in the same Database Engine instance, or even the same database. This can provide a form of priority thread execution by providing extra resources to the favored connection.
.gif "One port connects to multiple NUMA nodes.")
C. Associating multiple ports to multiple nodes
More than one port can be mapped to the same NUMA nodes. This permits you to configure differing permissions to various ports. For example, you can tightly restrict the access provided through a port by controlling permissions on the corresponding TCP endpoint. In this example, port 1450 is widely available to the intranet. Port 1433 is open to the Internet through a firewall, but access is tightly restricted. Both ports take full and equal advantage of NUMA.
.gif "Multiple ports connect to all available NUMA nodes")