SQL Server - SOS_SCHEDULER_YIELD and Unbalanced NUMA Node Usage
Hi everyone, we've faced an unusual behavior on our SQL Server and would like to understand the cause and how to manage it properly. Our DB Server is a physical server with 64 cores (128 logical CPUs) and 4 NUMA nodes. During a peak in application requests/session, we noticed a significant increase in SOS_SCHEDULER_YIELD waits, with an unusual distribution of CPU load: only 2 NUMA nodes were heavily saturated, while the others remained underutilized. Our main questions are: At what point does SQL Server assign the execution NUMA node to a session/process? During connection establishment? When the statement starts executing? If processor affinity is left at its default setting (not manually configured at the instance level), what metrics can we monitor to better understand and manage this behavior? Any insights or experiences with similar cases would be greatly appreciated. Thanks in advance!