The SQL Server SOS_SCHEDULER_YIELD wait type is an unusual wait type because it isn't solved in the same manner as most of the other wait types. This is because adding more hard drives or memory will not resolve the SOS_SCHEDULER_YIELD wait type. Basically, this wait type means that the SQL Server instance has determined the code that you are attempting to run is such a large query that SQL Server will not dedicate the resources needed to run it. Instead, it is pausing the query so that smaller queries can run.
Begin troubleshooting the SQL Server SOS_SCHEDULER_YIELD wait type by examining the execution plan for that query. Identify which indexes need to added to the plan and which tables need those new indexes. The query should no longer report as being stopped via the SOS_SCHEDULER_YIELD wait type once the new indexes have been added.
Within your execution plan you may see table scans or index scans. These queries are likely to be paused and shown as waiting for SOS_SCHEDULER_YIELD, this is especially true as those tables or indexes get more and more rows within them.
If the query has functions within it then the execution of the functions, especially table functions, are likely to be paused as a SOS_SCHEDULER_YIELD wait type. The CREATE INDEX and ALTER INDEX commands put additional demand on the system that is already under heavy load read by reading so much data from the disk working with indexes and can actually cause the CREATE INDEX or ALTER INDEX statements to be descheduled. This is problematic as you may need to create indexes in order to solve the SOS_SCHEDULER_YIELD problems of your queries, but you may not be able to create new indexes without having the CREATE INDEX statements descheduled.
Only some threads within the query may be paused with the SOS_SCHEDULER_YIELD wait type when working with queries in SQL Server that use parallelism. This is because the execution of that specific thread is so large, that other processes are going to be allowed to use the scheduler instead of letting the larger query run.
Confio Ignite for SQL Server
Confio Ignite for SQL Server helps identify the impact of the SOS_SCHEDULER_YIELD wait type on SQL Server database instances. Ignite for SQL Server helps DBAs maintain performance and availability within the SQL Server database. Ignite for SQL Server is the one SQL Server performance monitoring solution that identifies, analyzes and resolves all of the performance problems affecting applications and users depending on a Microsoft SQL Server instance.
Follow Us