February 5, 2015 at 12:10 pm
We have a server with very high SOS_SCHEDULER_YIELD wait time and counts. It accounts for about 35% wait time on the server. waiting_tasks_count is 360327738and wait_time_ms is 458906562 with a server uptime of just over 48h. This is SQL Server 2012, Windows Server 2008 R2, 20 logical cores, MAXDOP 8, Cost Threshold for Parallelism is 30. Max server mem in 121GB on a 128GB total. IT is an OLTP server as well as some ETL at night.
I have encountered CXPACKET a lot and know how to troubleshoot/resolve it. SOS_SCHEDULER_YIELD, I am having a real problem with. I am suspecting it may have to do with unpredictable CPU spikes that cause the server to be unresponsive and therefore increase connections from our web servers as they all backup waiting for threads. I believe this because, although SOS_SCHEDULER_YIELD is baseline high, but I saw an slow and exponential increase when we experience the issue mentioned.
Any thoughts?
Jared
CE - Microsoft
February 5, 2015 at 2:51 pm
1) I think I would give the OS and other stuff more than 7GB of memory.
2) I would run a differential analysis of wait stats. capture them into a temp object, waitfor some few minutes, capture again and diff. When you see spikes, start tracing or XEs or whatever to see what is hammering the box. I highly recommend sp_whoisactive too, which can also do time-delay info as well as show what is hammering the box right now. Tune problem things.
3) There are a LOT of things that could be at play here. I don't know that you will be able to do much from forum posts. 🙁
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 8, 2015 at 10:51 pm
Jared, I definitely agree with Kevin's point #2. I think it would be helpful to see a full set of Wait stats results. I don't know what code you are using to get your results, but I use the code from Paul Randal's blog entry on sqlskills called "wait-statistics-or-please-tell-me-where-it-hurts."
February 9, 2015 at 1:23 am
SQLKnowItAll (2/5/2015)
We have a server with very high SOS_SCHEDULER_YIELD wait time and counts. It accounts for about 35% wait time on the server. waiting_tasks_count is 360327738and wait_time_ms is 458906562 with a server uptime of just over 48h.
Too vague to be of any use. With 48 hours aggregated into a single value you don't know if most of that was a single over-night job, a steady increase during business hours or a single bad query.
Set up a job to capture the wait stats no less often than an hour. I use 30 minutes for high-level wait analysis. See when you get the waits and work out from that what's running at the time and focus on that.
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
February 9, 2015 at 10:57 am
The SOS_SCHEDULER_YIELD wait is usually due the a thread running longer than 4ms and a giving up it's spot in the running queue. This can be due to large table or index scans.
there is a good explination of the wait in this post (http://sqlperformance.com/2014/02/sql-performance/knee-jerk-waits-sos-scheduler-yield)
February 16, 2015 at 7:11 am
Thanks everyone! I got some great ideas from SQLCruise to integrate with the suggestions here. This way I can nail down the culprits as well as get a better handle on the actual wait time over a linger period of time.
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply