Summary: Use the sys.dm_os_nodes DMV to inspect NUMA node health, verify balanced CPU scheduler distribution, and monitor active worker loads across your hardware architecture.
Monitoring NUMA Node Configuration and Load Balance
This query provides valuable insights into your NUMA (Non-Uniform Memory Access) nodes, showing their composition and how much load they are currently handling. Understanding the distribution of resources and activity across your system's memory architecture is critical for high-performance database environments.
NUMA Node and Scheduler Audit Script
-- Check NUMA node health and scheduler distribution
SELECT
node_id,
node_state_desc,
memory_node_id,
processor_group,
cpu_count,
online_scheduler_count,
idle_scheduler_count,
active_worker_count,
avg_load_balance,
resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC'
OPTION (RECOMPILE);
Why This Matters for Performance
After running this, you should verify that an equal number of online schedulers are assigned to each NUMA node. This is especially important for physical or virtual machines with more than four sockets or over 24 physical cores.
- Balanced Load: An uneven distribution of schedulers can lead to "hot" NUMA nodes where specific CPUs are overworked while others remain idle.
- License Alignment: On standard edition servers with core limits, ensure your limited cores are spread evenly across nodes to maximize memory bandwidth.
- Resource Monitor State: This indicates if the internal thread responsible for memory management is healthy on that specific node.
For more detailed information on NUMA tuning and hardware alignment, explore these resources:
Troubleshooting performance bottlenecks? Proper NUMA alignment is often the "hidden" fix for scaling issues on large multi-processor servers!
