March 10, 2017 at 7:40 am
Hello, the MDOP and threshold setting in SQL have been a frequent discussion in our business. I recently read that a simple way to look at is to try and balance the two wait types SOS_SCHEDULER_YIELD and CXPACKET, having CXPACKET happen slightly more often then SOS_SCHEDULER_YIELD. I have gathered some of these counts on our servers and found several cases where SOS_SCHEDULER_YIELD far outweighs the count of CXPACKET. I'm trying to think this through and was hoping someone could agree or disagree, with good reason.
If I raise the cost of parallelism, less queries will break out and use multiple processors and therefore in theory leaving more available for other work. However to counter that if queries are taking too long to run using one processors then they will get backed up and you will see SOS_SCHEDULER_YIELD. If I lower the threshold cost a thread will more likely use parallelism and possibly run faster. However more parallelism will tie up more processors so it could be argued that lowering it will cause more SOS_SCHEDULER_YIELD. If one these arguments is more sound then other it would really help us setup a baseline for this setting and the mdop setting. Thanks a mill.
March 10, 2017 at 9:33 am
I'm not sure where you got the concept of balancing CXPACKET and SOS_SCHEDULER_YIELD. That's not a valid approach that I've ever heard of.
CXPACKETS just indicates that parallelism is occurring. They don't mean anything good or bad in and of themselves, just that there is threading and waits between those threads. Here's a good discussion about exactly what CXPACKETS are and how to deal with them on your system. If you follow the link to the article called Tell Me Where It Hurts you can get some more information.
If you're looking to set the Cost Threshold for Parallelism, which is a very good idea, read this post on how to evaluate a value for your own servers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2017 at 10:14 am
Grant Fritchey - Friday, March 10, 2017 9:33 AMI'm not sure where you got the concept of balancing CXPACKET and SOS_SCHEDULER_YIELD. That's not a valid approach that I've ever heard of.CXPACKETS just indicates that parallelism is occurring. They don't mean anything good or bad in and of themselves, just that there is threading and waits between those threads. Here's a good discussion about exactly what CXPACKETS are and how to deal with them on your system. If you follow the link to the article called Tell Me Where It Hurts you can get some more information.
If you're looking to set the Cost Threshold for Parallelism, which is a very good idea, read this post on how to evaluate a value for your own servers.
I believe I got that balancing idea from this site. http://adventuresinsql.com/2010/04/whats-a-good-rule-for-max-degree-of-parallelism/
My understanding was CXPACKET was the thread was waiting for a parallel process to complete, or catch up. So many of these waits could mean over paralyzing, while SOS_SCHEDULER_YIELD was a thread waiting to obtain a processor thread to begin the work. However we are clear that seeing CXPACKET is normal or even a good sign. Thank you for the links, I will check them out!
March 10, 2017 at 10:31 am
Interesting. I've never seen that approach before. I'm not sure I'm crazy about it at all. In general I wouldn't really bother counting, or worrying about the count, of CXPACKETS. They don't give you a positive or negative indication on if the waits you're experiencing are problematic or not. I'd follow the advice from SQLSkills (Paul, Kim,Jonathon, Erin, and the rest, some of the single smartest and most capable people I know).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2017 at 1:36 am
Having SOS_Scheduler_Yield waits happening doesn't mean that things are waiting for the processor. Having them with long signal waits may do, but the simple presence of the wait does not.
A scheduler yield wait occurs when a query that is executing exhausts its quanta (4ms) without incurring any other waits. It voluntarily yields the CPU. Because it's not waiting for a resource, it doesn't go into the waiter list, but rather goes back onto the runnable queue immediately. If the queue is otherwise empty, the query goes straight back onto the CPU without waiting, but it still posted a SOS_Schedule_Yield wait
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply