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)