SQL Server Performance


Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by DylanFinney, Aug 7, 2009.

  1. DylanFinney New Member

    Hello All,
    I can't quite figure this one out and am hoping for some insights that I'm missing. I've got a batch statement that's seemingly waiting around for CPU resources when there are plenty available. The batch statement in question runs without issue if I execute it in SSMS, however when called through the application it will wait with SOS_SCHEDULER_YIELD for more than 30 minutes. There's not a lot of processing being done on the server at this time and it's on it's own dedicated connection. I just don't understand it! I can even execute the batch statements through SSMS while the other one is waiting! Help!!
    We recently moved this database from a 2000 environment to 2005. The 2000 environment did not see this same issue, but serviced the same application. The new environment is much better hardware as well, we've basically doubled the available CPU resources and increased memory by > 4x.
  2. Luis Martin Moderator

    Welcome to the forums!
    Shoot in the dark: Did you full update statistics after moving to 2005?
  3. DylanFinney New Member

    I did indeed. I've gone digging into sys.dm_os_schedulers ans sys.dm_os_workers to try and find what process was getting priority over this query too.
  4. JAdams New Member

    Have you found a solution for this? I'm having the exact same issue with a batch job on one of our SQL 2005 SP3 servers.
  5. DylanFinney New Member

    Kind of sort of. The original query was written using old join syntax. So I re-ordered everything, created some new indexes and eventually got the query to return in a decent amount of time. The clue that helped me realize it was just poor performance was watching the CPU and Physical IO counters in Activity Monitor. Even though the Wait Type was still "SOS_SCHEDULER_YIELD", the counters were going up. Turns out that on the LAST wait type is displayed, so your process may not be waiting at all. Check your counters and see if they're going up, it may just be poor query performance.

Share This Page