SQL Server NUMA Configuration

Configuring Non-Uniform Memory Access (NUMA) in SQL Server is a critical aspect of optimizing performance on modern multi-processor systems. NUMA architectures provide each processor with its own local memory, reducing latency and increasing throughput. However, to fully leverage NUMA’s benefits, SQL Server must be properly configured to align with the underlying hardware.

This comprehensive guide delves into the intricacies of SQL Server NUMA configuration, covering essential concepts, best practices, and step-by-step instructions to ensure optimal performance.

Understanding NUMA Architecture
- What is NUMA?
- How NUMA Works
- NUMA vs. SMP (Symmetric Multi-Processing)
SQL Server and NUMA
- SQL Server’s NUMA Awareness
- NUMA Node Configuration in SQL Server
- Soft NUMA vs. Hard NUMA
Configuring NUMA in SQL Server
- Enabling NUMA in SQL Server
- Configuring MAXDOP (Maximum Degree of Parallelism)
- Setting Affinity Masks
- Configuring NUMA in Virtualized Environments
Best Practices for NUMA Configuration
- Memory Allocation Strategies
- CPU Affinity and NUMA Node Assignment
- Managing Hyper-Threading
- Monitoring NUMA Performance
Advanced NUMA Configuration
- Automatic Soft NUMA
- Using Trace Flags for NUMA Optimization
- NUMA Configuration in Always On Availability Groups
Troubleshooting NUMA Issues
- Identifying NUMA-Related Performance Bottlenecks
- Resolving NUMA Node Imbalance
- Handling NUMA-Related Wait Types
NUMA Configuration in Virtualized Environments
- NUMA Considerations in VMware
- NUMA Configuration in Hyper-V
- Best Practices for Virtual Machines
Case Studies and Real-World Examples
- NUMA Configuration in High-Performance OLTP Systems
- NUMA Optimization in Data Warehousing Environments
Conclusion
- Summary of Key Takeaways
- Future Trends in NUMA and SQL Server

1. Understanding NUMA Architecture

What is NUMA?

Non-Uniform Memory Access (NUMA) is a memory design used in multiprocessor systems where each processor has its own local memory. Access to local memory is faster than access to memory local to other processors, leading to performance improvements in multi-threaded applications like SQL Server.

How NUMA Works

In a NUMA system, processors are grouped into nodes, each with its own local memory. Communication between processors in different nodes (remote memory access) is slower than communication within the same node (local memory access). SQL Server can be configured to optimize memory and CPU usage based on this architecture.

NUMA vs. SMP

Symmetric Multi-Processing (SMP) systems have a single memory space shared by all processors, leading to potential bottlenecks as the number of processors increases. NUMA addresses this by providing each processor with its own local memory, reducing contention and improving scalability.

2. SQL Server and NUMA

SQL Server’s NUMA Awareness

SQL Server 2005 and later versions are NUMA-aware, meaning they can detect and utilize NUMA architectures to optimize performance. SQL Server can bind schedulers to specific NUMA nodes and allocate memory accordingly.

NUMA Node Configuration in SQL Server

SQL Server detects NUMA nodes during startup and configures its schedulers and memory allocations based on the underlying hardware. The number of NUMA nodes can be determined using the following query:

SELECT node_id, node_state_desc, memory_node_id
FROM sys.dm_os_nodes;

Soft NUMA vs. Hard NUMA

Hard NUMA: Refers to the physical NUMA nodes defined by the hardware. SQL Server can bind schedulers and allocate memory to these nodes.
Soft NUMA: Introduced in SQL Server 2005, Soft NUMA allows SQL Server to create virtual NUMA nodes within a physical NUMA node, improving scalability on systems with many processors.

3. Configuring NUMA in SQL Server

Enabling NUMA in SQL Server

NUMA is enabled by default in SQL Server on supported hardware. To check if NUMA is enabled:

SELECT value_in_use
FROM sys.configurations
WHERE name = 'numa';

Configuring MAXDOP (Maximum Degree of Parallelism)

MAXDOP controls the number of processors SQL Server uses for parallel query execution. For NUMA systems:

Single NUMA node: Set MAXDOP to the number of cores per NUMA node.
Multiple NUMA nodes: Set MAXDOP to the number of cores per NUMA node or a value that balances performance.

EXEC sp_configure 'max degree of parallelism', <value>;
RECONFIGURE;

Setting Affinity Masks

Affinity masks bind SQL Server schedulers to specific CPUs. To configure affinity masks:

EXEC sp_configure 'affinity mask', <value>;
RECONFIGURE;

Configuring NUMA in Virtualized Environments

In virtualized environments, ensure that the virtual machine’s NUMA configuration aligns with the host’s NUMA topology. Tools like VMware vSphere and Hyper-V provide settings to configure NUMA for virtual machines.

4. Best Practices for NUMA Configuration

Memory Allocation Strategies

Lock Pages in Memory: Prevents SQL Server memory from being paged to disk.
Max and Min Server Memory: Set these values to ensure SQL Server uses a fixed amount of memory, reducing paging and NUMA node imbalance.

EXEC sp_configure 'max server memory', <value>;
EXEC sp_configure 'min server memory', <value>;
RECONFIGURE;

CPU Affinity and NUMA Node Assignment

Assign SQL Server schedulers to specific NUMA nodes to ensure that threads are executed on local CPUs, reducing latency.

Managing Hyper-Threading

Hyper-Threading can cause SQL Server to distribute threads across logical processors, potentially leading to NUMA node imbalance. Consider disabling Hyper-Threading in the BIOS or using the following registry setting:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\NumaAware=0

Monitoring NUMA Performance

Use Dynamic Management Views (DMVs) to monitor NUMA performance:

SELECT * FROM sys.dm_os_nodes;
SELECT * FROM sys.dm_os_schedulers;

5. Advanced NUMA Configuration

Automatic Soft NUMA

Starting with SQL Server 2014 SP2, Soft NUMA is enabled by default. To disable it:

EXEC sp_configure 'automatic soft-NUMA disabled', 1;
RECONFIGURE;

Using Trace Flags for NUMA Optimization

Trace Flag 8048: Changes partitioning from node-based to CPU-based, beneficial for systems with more than 8 CPUs per NUMA node.

DBCC TRACEON(8048, -1);

NUMA Configuration in Always On Availability Groups

Ensure that each replica in an Always On Availability Group has its own NUMA node configuration to prevent cross-node

SQL SERVER ON VIRTUAL MACHINE RESOURCE ...

Certainly! Let’s continue exploring advanced topics in SQL Server NUMA configuration, focusing on performance optimization, troubleshooting, and considerations for virtualized environments.

6. Troubleshooting NUMA Issues

Effective troubleshooting is essential to maintain optimal performance in SQL Server environments utilizing NUMA architectures. Here’s how to identify and resolve common NUMA-related issues:

Identifying NUMA-Related Performance Bottlenecks

Monitor Wait Types: Use the following query to identify NUMA-related wait types: SELECT wait_type, wait_time_ms, waiting_tasks_count FROM sys.dm_exec_requests WHERE wait_type LIKE 'NUMA%'; High wait times on NUMA-related wait types may indicate memory access contention or scheduler imbalances.
Analyze Scheduler States: Check the state of schedulers to detect any that are disabled or underutilized: SELECT scheduler_id, status, cpu_id FROM sys.dm_os_schedulers WHERE status <> 'VISIBLE ONLINE'; Schedulers with statuses like ‘VISIBLE OFFLINE’ or ‘VISIBLE ONLINE (INACTIVE)’ may point to issues in CPU affinity or NUMA node assignment.

Resolving NUMA Node Imbalance

Reconfigure Affinity Masks: Ensure that SQL Server schedulers are evenly distributed across NUMA nodes. Adjust affinity masks using: EXEC sp_configure 'affinity mask', <value>; RECONFIGURE; Replace <value> with the appropriate mask to bind SQL Server to specific CPUs.
Adjust MAXDOP Settings: Misconfigured MAXDOP can lead to uneven workload distribution. Set MAXDOP based on the number of cores per NUMA node: EXEC sp_configure 'max degree of parallelism', <value>; RECONFIGURE; Replace <value> with the desired number of processors.

Handling NUMA-Related Wait Types

NUMA_NODE_NOT_FOUND: Indicates that a requested NUMA node is unavailable. This can occur if SQL Server is trying to allocate memory on a NUMA node that doesn’t exist or is offline.
NUMA_MEMORY_LIMIT_EXCEEDED: Occurs when SQL Server exceeds the memory limit for a NUMA node. This can be addressed by adjusting memory allocations or redistributing workloads.

7. NUMA Configuration in Virtualized Environments

Virtualization adds complexity to NUMA configurations. Proper alignment between virtual and physical NUMA nodes is crucial for performance.

NUMA Considerations in VMware

vNUMA Configuration: Ensure that the virtual machine’s vNUMA configuration matches the underlying physical NUMA topology. This allows the guest OS and applications to be NUMA-aware.
Resource Allocation: Allocate CPU and memory resources to virtual machines in a way that reflects the physical NUMA nodes. Avoid overcommitting resources to prevent NUMA node imbalance.

NUMA Configuration in Hyper-V

NUMA Node Assignment: In Hyper-V, configure virtual NUMA nodes to match the physical NUMA topology. This can be done through the VM’s settings under the NUMA configuration section.
Dynamic Memory: Be cautious when using Dynamic Memory in NUMA configurations, as it can lead to memory allocation issues across NUMA nodes.

Best Practices for Virtual Machines

Avoid Over-Provisioning: Assign virtual CPUs and memory to virtual machines based on the physical NUMA nodes to prevent NUMA node imbalance.
Monitor Performance: Regularly monitor performance metrics such as CPU usage, memory allocation, and wait times to detect and address NUMA-related issues promptly.

8. Case Studies and Real-World Examples

NUMA Configuration in High-Performance OLTP Systems

In high-performance Online Transaction Processing (OLTP) systems, optimizing NUMA configurations is critical:

MAXDOP Settings: Set MAXDOP to the number of cores per NUMA node to ensure efficient parallel query execution.
Memory Allocation: Use the max server memory setting to allocate memory evenly across NUMA nodes, preventing memory contention.
Affinity Masks: Configure affinity masks to bind SQL Server schedulers to specific NUMA nodes, reducing cross-node communication latency.

NUMA Optimization in Data Warehousing Environments

Data warehousing environments often involve large-scale queries and data processing:

Soft NUMA: Enable Soft NUMA to partition large NUMA nodes into smaller units, improving parallelism and scalability.
Cost Threshold for Parallelism: Adjust the cost threshold for parallelism to ensure that only expensive queries are executed in parallel, optimizing resource utilization.
Monitoring Tools: Utilize tools like SQL Server Management Studio and Dynamic Management Views to monitor NUMA node performance and identify potential bottlenecks.

Configuring NUMA in SQL Server is essential for optimizing performance on multi-processor systems. By understanding NUMA architecture, configuring settings like MAXDOP and affinity masks, and following best practices, you can ensure that SQL Server operates efficiently in NUMA environments.

Remember to regularly monitor performance metrics and adjust configurations as needed to address any NUMA-related issues promptly. With proper NUMA configuration, SQL Server can fully leverage the capabilities of modern multi-processor systems, leading to improved performance and scalability.