CXPACKET and SOS_Scheduler_yeild waits

Sam 1,476

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

1 answer

Erland Sommarskog 107.2K Reputation points

2024-02-12T22:24:56.5866667+00:00

Exactly what are you looking at? The wait time or numbers of waits? It's quite normal to have a high number of SOS_SCHEDULER_YIELD waits when you have a CPU-intensive process. This is because all code in SQL Server voluntary yields after around 4 ms to permit other tasks to run. But if there is free CPU, the wait should be very short.
Please sign in to rate this answer.

1 person found this answer helpful.
Sam 1,476 Reputation points

2024-02-13T17:09:13.0566667+00:00

Just wanted to know if that's normal or unusual. So, SQLOS uses co-operative scheduling. Thank you.

Sam 1,476 Reputation points

2024-02-13T17:15:12.66+00:00

Erland Sommarskog 107.2K Reputation points

2024-02-13T21:55:41.2566667+00:00

So that screenshot tells us that things could be better.

That you have a process with last wait that is SOS_SCHEDULER_YIELD is perfectly normal. That follows from the concept of co-operative multitasking, as I explained above.

But what is not so good is that you only have one process with that as the last wait type. This could be an indication of parallelism gone wrong. The idea is that the work should be split evenly between the threads, but sometimes almost all work end up with the same thread. And in that case, you get all the overhead with parallelism and none of the benefit.

As for why this happens, it's the usual blues: estimates. It could be cause by outdated statistics, but there could be other reasons. In any case, the place to start troubleshooting is, as often when it comes to query tuning, to look at the actual query plan.

(Then again, having seen more than one example of code from your site, maybe it's better to investigate whether code is actually doing the right thing...)

Sam 1,476 Reputation points

2024-02-14T07:18:59.53+00:00

Thank you sir. To add, there is something wrong with the stored procedure and queries inside it for sure but i need to go with the facts and numbers. There are no proper filters , some indexing issues, something wrong with the joins but i couldn't comment anything for now sir. its a long running process. I am trying to get the actual plan from the sub-prod env. Hoping I can get it this week. I ll share. I needs some guidance over those joins. When this query runs, everything gets slow and I see the execution counts are very high. Same with logical reads.

Erland Sommarskog 107.2K Reputation points

2024-02-14T21:59:37.53+00:00

"something wrong with the joins"? Well, before you spend too much time on performance, verify that the query is returning the correct result. Else, you are only wasting your time.
Sign in to comment

Share via

CXPACKET and SOS_Scheduler_yeild waits

1 answer