NUMA Scenarios

New: 5 December 2005

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 2005. 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.

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.

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.

See Also

Concepts

Understanding Non-uniform Memory Access
How SQL Server 2005 Supports NUMA

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Changed content: Added improved graphics to illustrate the scenarios. Clarified scenario B. Removed unlikely scenario D.
5 December 2005	Changed content: All NUMA topics have been reorganized for this release. Substantial new information was added. This topic is new, though much of the content was moved from another topic.