Post reply

Prod Server ... High CPU with Lastwaitype "SOS_SCHEDULER_YIELD"

  • July 16, 2008 at 7:21 am

    #154534

    Having High CPU Usages in 64 bit SQL Server 2005 sp2 cluster server.

    Checked the sysprocesses output :

    dbid spid lastwaittype cpu

    --------------------------------------------------

    8 210 SOS_SCHEDULER_YIELD 689906

    8 638 SOS_SCHEDULER_YIELD 408157

    How to troubleshoot ?

    Thanks for the help..

    "More Green More Oxygen !! Plant a tree today"

  • July 16, 2008 at 7:51 am

    #843773

    This looks like a good link:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=790196&SiteID=1

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 16, 2008 at 8:13 am

    #843796

    I'd start by fnding out what those queries are running.

    You can get the query/proc using the sys.dm_exec_requests and sys.dm_exec_query_text dmvs.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 16, 2008 at 8:25 am

    #843804

    What is the Max Degree of Parallelism on your instance?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 21, 2008 at 4:19 am

    #845630

    These are the information I have with me now. Do let me what do think about the counters and how should I proceed further.

    System info:

    Hardware: Intel Xeon MP @ 3.00GHz (4 CPU )

    RAM size: 11.9GB

    OS Version: Windows 2003 Server R2 with SP2 (64 Bit)

    SQL Server Version:SQL Server 2005 with SP2 (One Instance) Clustered environment

    "More Green More Oxygen !! Plant a tree today"

  • July 22, 2008 at 8:06 am

    #846423

    IMHO, last wait type is not nearly so useful as trending all waittypes over time. Search microsoft.com for a word document on SQL Server 2005 Waits and Queues.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 22, 2008 at 8:15 am

    #846429

    shalu (7/21/2008)

    These are the information I have with me now. Do let me what do think about the counters and how should I proceed further.

    System info:

    Hardware: Intel Xeon MP @ 3.00GHz (4 CPU )

    RAM size: 11.9GB

    OS Version: Windows 2003 Server R2 with SP2 (64 Bit)

    SQL Server Version:SQL Server 2005 with SP2 (One Instance) Clustered environment

    It's hard (and misleading) to attempt to determine whether a certain counter value is "good" or "bad". Collect your data over time and look at trends, not individual values. In other words, establish a baseline for your data.

    Having said that, here is an excellent link to get you started on whether a certain permon counter value is in a reasonable range or not:

    SQL Server 2005 Performance Tuning using Waits and Queues:

    http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • July 23, 2008 at 3:54 am

    #846887

    I would also look at context switches if this is a extremely busy server with lots of connections and small queries. If it is in say the millions you may want to look at fibre mode instead of thread mode to ease thread switching and keep things flowing to the cpu.

    Cheers,

    Wes

  • July 26, 2008 at 10:20 pm

    #848842

    Thanking all of you for the valued responce. !! I have asked them to apply the patch 7 and then test it out as there are lots of CPU utilization issues with SP2.

    "More Green More Oxygen !! Plant a tree today"

  • July 27, 2008 at 5:03 am

    #848890

    I have the same wait coming out in my wait stats.

    So to make sure if CPU bound i am looking at the following .

    Im not sure if i need to schedule these at the same time as the WAIT STATS or not...still investigating.

    --Need more CPU - only if number of tasks greater than 1

    select AVG (runnable_tasks_count) from sys.dm_os_schedulers where status = 'VISIBLE ONLINE'

    --affinity of my schedules to CPU

    select scheduler_id, CAST (cpu_id as varbinary) AS scheduler_affinity_mask

    from sys.dm_os_schedulers

    --Need more threads see if work queue length

    select AVG (work_queue_count) from sys.dm_os_schedulers where status = 'VISIBLE ONLINE'

    --System IO Bound

    select pending_disk_io_count from sys.dm_os_schedulers

    --

    SELECT scheduler_id, current_tasks_count, runnable_tasks_count

    FROM sys.dm_os_schedulers WHERE scheduler_id < 255

    Any ideas anyone if the above is good to schedule at same time as above to be sure that it wasn't just a particular process that was run that caused high SOS_ Waits

    My waits show about 2677105 requests an hour at 7:00 am then by end of day around 5313996

    averaging signal waits up to 57%.

    Parallelism is set to 1.

Viewing 10 posts - 1 through 9 (of 9 total)

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