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