Forum Discussion
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!
3 Replies
I would first make sure that your MAXDOP is not set to 0 and your Cost Threshold for Parallelism is set to a reasonable high number so you're not using multiple parallel processes unnecessarily. Setting these is more of an art form and not an exact science since it's affected by your hardware config, data structures, indexing, statistics etc. etc. The settings are dynamic so if you make a change that affects things adversely you can set it back quickly.
Here's an MS article that may help: https://learn.microsoft.com/en-us/answers/questions/2103761/need-recommendations-for-maxdop-and-cost-threshold
Paul Randal wrote an article related to 'SOS_SCHEDULER_YIELD' waits that may be usefull to you: https://sqlperformance.com/2014/02/sql-performance/knee-jerk-waits-sos-scheduler-yield
When is NUMA node assigned? > At connection time
Metric and monitoring:
sys.dm_os_schedulers > View per-scheduler runnable_tasks_count, current_tasks_count, and cpu_id
sys.dm_os_nodes > Get NUMA node detailsWhat SQL Server version and edition are you on, as there are some limitations in editions/versions.
https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver15&preserve-view=true#Cross-BoxScaleLimits
How is the MAXDOP setting configured?
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16#recommendations
