none
SOS_SCHEDULER_YIELD

    Question

  • Can anyone shed any light on the wait type SOS_SCHEDULER_YIELD in SQL 2005?  The following is the description from BOL...

    • Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

    Why would a task "voluntarily" yield the scheduler?  What type of tasks would do so?

    If this is one of your higher wait types in terms of count and time, is that a pretty good sign that you need additional or faster CPUs?

    Thanks!

    Wednesday, October 04, 2006 8:09 PM

Answers

  • If you are seeing lots of SOS_SCHEDULER_YIELD in your Wait States, that is a very stong indicator of CPU pressure.

    You can run the DMV query to confirm that:

    -- Check SQL Server Schedulers to see if they are waiting on CPU
    SELECT scheduler_id, current_tasks_count, runnable_tasks_count
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255

    If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern!

    Sunday, October 08, 2006 2:53 AM

All replies

  • The SQL Database engine uses a non-preemptive scheduler internally to increase throuhput; Most threads are expected to yield often. Much more information on this topic can be found on http://blogs.msdn.com/slavao.

     

    Thursday, October 05, 2006 3:19 PM
  • If you are seeing lots of SOS_SCHEDULER_YIELD in your Wait States, that is a very stong indicator of CPU pressure.

    You can run the DMV query to confirm that:

    -- Check SQL Server Schedulers to see if they are waiting on CPU
    SELECT scheduler_id, current_tasks_count, runnable_tasks_count
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255

    If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern!

    Sunday, October 08, 2006 2:53 AM
  • SOS_SCHEDULER_YIELD points to CPU Pressure.

     

    Procedure:
    When concurrent users apply simultaneous DSS/OLAP like CPU intensive workload, there could be CPU pressure. We can conclude presence of CPU pressure when at any given moment during this time period at least one or more user tasks waited for CPU resource.

    In this case one can run the below query to find out how many CPU on an average will help to scale the workload better. It might be more informative to collect the below information in short time intervals (many samples) than just once to understand during which time of the workload application the CPU pressure was the most. Single sample will lead to average additional CPUs necessary for the entire workload duration.

     

    1. Reset Wait Stats
    dbcc sqlperf('sys.dm_os_wait_stats', clear) --example provided by www.sqlworkshops.com

     

    2. Apply workload (you can find sample workload query at the end of this article, you need to execute the sample workload query simultaneously in many sessions to simulate concurrent user tasks).

     

    3. Run the below query to find Additional CPUs Necessary – it is important to run the query right after the workload completes to get reliable information.


    select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) / si.cpu_ticks_in_ms) * cpu_count), 2) as Additional_CPUs_Necessary,
    round((((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) / si.cpu_ticks_in_ms) * cpu_count) / hyperthread_ratio), 2) as Additional_Sockets_Necessary
    from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type = 'SOS_SCHEDULER_YIELD' --example provided by
    www.sqlworkshops.com

     

     

     

    The complete article is available at http://www.sqlworkshops.com/cpupressure

     

    sqlworkshops

    www.sqlworkshops.com

     

     

     

    Sunday, November 25, 2007 7:01 PM