Post reply

SOS_SCHEDULER wait type is causing proc to run forever!!! PLEASE HELP!

  • July 19, 2011 at 9:14 am

    #241099

    All,

    A proc that runs often all the sudden is not running and the wait type for the SPID is showing a wait type of "SOS_SCHEDULER_YIELD". Everything I'm reading on forums is stating that this happens when there is CPU pressure, but the CPU is rarely high. Typically the CPU is between 20% and 45%.

    Any ideas?

    Your help is greatly appreciated!

    Thanks,

    David

  • July 19, 2011 at 9:47 am

    #1354918

    How did you check the CPU usage?

    One of the links I checked http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/e2ddf26f-f86c-4368-b4a7-17b2b7c64aa5/ mentioned:

    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!

    Have you run that query?



    My blog

  • July 19, 2011 at 10:40 am

    #1354995

    Ignacio,

    Yes, I have run that query previously but I wasn't sure what to look for. We actually fixed the issue. It had to with how the Select statement was structured within another Select statement. So we're good now.

    Thanks for your help,

    -David

  • July 19, 2011 at 10:49 am

    #1355001

    davidsalazar01 (7/19/2011)

    Ignacio,

    Yes, I have run that query previously but I wasn't sure what to look for. We actually fixed the issue. It had to with how the Select statement was structured within another Select statement. So we're good now.

    Thanks for your help,

    -David

    Care to post more details so it may help the next guy hitting this page from google?

  • July 19, 2011 at 11:09 am

    #1355026

    They were using TOP 1 clause in sub-select and it caused the issue. Hope this helps.

  • July 19, 2011 at 11:13 am

    #1355032

    davidsalazar01 (7/19/2011)

    They were using TOP 1 clause in sub-select and it caused the issue. Hope this helps.

    With or without order by? Doing table scan or some sort of seek?

    What did you change it to?

    Can you post the before and after execution plans?

  • July 20, 2011 at 3:36 am

    #1355442

    David, if you have time and the answers, it will be very nice if you can answer Ninja's questions, so that other people with the same problem can solve it faster. Thanks in advance!



    My blog

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply