1

I've got a process which about once a month at times unrelated (or as far as we can tell) goes into SOS_SCHEDULER_YIELD State. This process blocks our main data entry pipeline and brings the entire database to a screeching halt.

We put a queue table with service broker in front of this proc (as it was initially the data entry proc) and we were losing data as the systems ahead ran out of space in their queues. This has prevented us from losing data, but hasn't fixed the issue, its simply a band aid.

Is there any way in a procedure to say this proc must be given a higher priority on the CPU, or must be low on the list to take CPU time away from it, it took 7 minutes for the processor to get back around to finishing the task today (has been 12 minutes before, seems to be the higher load we're under the longer it takes as there is more data coming in so there is more processes fighting for CPU time)

  • Can you share detailed informationn about your SQL Server hardware? Physical/Virtual? How old? Number of Sockets, Cores? – Christoph Wegener Mar 14 '16 at 22:41
2

It's impossible to do this in the procedure. This wait type is a function of the scheduler. Each SPID gets a finite amount of time (4ms) on the processor. Once that time, or quantum, elapses, the SPID gets put back into the wait list until resources are available again. This wait type is indicating that the SPID was not able to complete its work on the CPU in the quantum and had to "yield" to another waiting process.

This cannot be changed with any SQL code. You can specify resources be prioritized for your load with Resource Governor, as Paul White suggested. Even so, unless, as Kin suggested, you are experiencing a lot of CPU pressure, Resource Governor won't help either.

Check for CPU intensive operations and minimize where you can. One that bites a lot of people is the Implicit Conversion, that will quickly be discovered by scanning the execution plan.

2

First check if

Now if it is the only wait type that is causing you problems,

Always test and make sure that you are on the Latest Service Pack and CU

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.