CXPACKET and SOS_Scheduler_yeild waits

Question

Post reply

CXPACKET and SOS_Scheduler_yeild waits

vsamantha35

SSChampion

Points: 11529
More actions
February 12, 2024 at 6:43 pm

#4377483

Hi All,
One of the app query is running slow. When I saw the spid's waitype, it is CXPACKET. After digging further one of the thread is waiting for SOS_SCHEDULER_YIELD. question I have is, except this query nothing else is running at that moment. only small other queries coming and exiting. When I observed the windows task manager, Server CPU usage is under 15% utilization. VM has 32 cpus. Why it is had to wait for SOS_SCHEDULER_YIELD.when it is not a high cpu condition on the server? How to troubleshoot and fix such queries?
Regards,
Sam

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

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

Jo Pattyn SSC-Dedicated Points: 32070 More actions · Answer 1

Looks that SOS_SCHEDULER_YIELD is a benign wait. The cpu was fully utilized

What is the parallelism setting for this query?

If maxdop= 1 results in 1 vcpu /32 vcpu = 3% of total cpu usage

https://www.sqlskills.com/help/waits/sos_scheduler_yield/

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 2

Does the virtual host have at least one physical core per virtual processor provisioned to all guest VMs on the host?

You can of course oversubscribe processor resources to a degree, but SQL can be a workload that does not like to be oversubscribed.

What is your maxdop? I hope it isn't set to 0?

vsamantha35 SSChampion Points: 11529 More actions · Answer 3

maxdop is set to 1 at instance level and at database level it is set to 4. looks like each operator within plan is taking 4 threads

Capture

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 4

I do believe that the database level setting overrides the instance level setting.

Is this a purely/high majority OLTP application? The problem could be excessive parallelism.

High SOS scheduler yield waits with low CPU is a pretty classic sign of oversubscribed virtualization hosts.

CX waits are really tough to make a determination from on their own without other context

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 5

Have you looked at the execution plan for the query in question? That's going to tell you a whole lot about how it's being executed, what resources are being used, etc. Estimated plan will give you most of what you need, so you can pluck it from Query Store or the cache. If you can execute the query and capture a plan with runtime metrics you'll get more info to help with determining what is happening and why.

"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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 6

vsamantha35 wrote:

maxdop is set to 1 at instance level and at database level it is set to 4. looks like each operator within plan is taking 4 threads

Is the "query" an UPDATE by any chance?

Either way, can you post the query, please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24692 More actions · Answer 7

Another thing - is there a query hint to put the MAXDOP to another value? I know you can set that at the instance level, database level, and query (or stored procedure) level.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

vsamantha35 SSChampion Points: 11529 More actions · Answer 8

Jeff Moden wrote:

vsamantha35 wrote:
maxdop is set to 1 at instance level and at database level it is set to 4. looks like each operator within plan is taking 4 threads
Is the "query" an UPDATE by any chance?
Either way, can you post the query, please.

Yes Sir. it is bunch of updates. its running over 6 hours, I feel something wrong with their joins and filters. I ll post the query and actual plan if i can collect.

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 9

Estimated plan can tell us a lot. Sure, actual plan tells you more, but just plucking the plan out of cache goes a long way.