For Day 6 of this series, we start out with Query #11, which is SQL Server NUMA Info. This query retrieves information from the sys.dm_os_nodes dynamic management view about the NUMA node structure that has been created by the SQLOS. By default, this will mimic the hardware processor locality (whether it is the actual hardware of the host, or a virtual machine), but this structure can be altered by using soft-NUMA. Query #11 is shown in Figure 1.
1: -- SQL Server NUMA Node information (Query 11) (SQL Server NUMA Info)
2: SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count,
3: active_worker_count, avg_load_balance, resource_monitor_state
4: FROM sys.dm_os_nodes WITH (NOLOCK)
5: WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
6:
7: -- Gives you some useful information about the composition and relative load on your NUMA nodes
8: -- You want to see an equal number of schedulers on each NUMA node
9: -- Watch out if SQL Server 2016 Standard Edition has been installed on a machine with more than 16 physical cores
10:
11: -- Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes
12: -- https://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/
Figure 1: Query #11 SQL Server NUMA Info
This query tells you how many NUMA nodes are being used by SQLOS. In a modern, NUMA-capable server, you should see one NUMA node for each physical processor socket (unless someone has disabled NUMA in the UEFI/BIOS). So a two-socket server should have two NUMA nodes. If you are using virtualization, you might see a VM with only one NUMA node and many virtual processor cores, or whoever created the VM might have used multiple NUMA nodes with fewer virtual processor cores per NUMA node. You also want to confirm that you are seeing the same online_scheduler_count on each NUMA node.
Query #12 is System Memory. This query retrieves information from the sys.dm_os_sys_memory dynamic management view about the memory usage by the operating system. Query #12 is shown in Figure 2.
1: -- Good basic information about OS memory amounts and state (Query 12) (System Memory)
2: SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)],
3: available_physical_memory_kb/1024 AS [Available Memory (MB)],
4: total_page_file_kb/1024 AS [Total Page File (MB)],
5: available_page_file_kb/1024 AS [Available Page File (MB)],
6: system_cache_kb/1024 AS [System Cache (MB)],
7: system_memory_state_desc AS [System Memory State]
8: FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
9:
10: -- You want to see "Available physical memory is high" for System Memory State
11: -- This indicates that you are not under external memory pressure
Figure 2: Query #12 System Memory
This query tells you how much memory is visible to the operating system (whether it is the host OS or the guest OS for a virtual machine). It also tells you how much memory is available to the operating system, which is normally a relatively low amount when SQL Server has seen a normal workload. It is very important to look at the system_memory_state_desc column result, which is the result of two separate flags about the system memory state.
What you want to see is “Available physical memory is high”, which means that the OS has plenty of available memory, and you are not under external memory pressure. What you might see in some cases is “Available physical memory is steady”, or “Available physical memory is low”, which means that you are under external memory pressure, which is something you don’t want to see.