0

What is the correlation between blocking and CPU? Because I am reaching the conclusion that the blocking is causing my sos_scheduler_yield wait type to increase, but I would like to understand the reasoning, i.e. why is blocking (in particular waiting for LCK_M_SCH_S) causing high sos_scheduler_yield wait type.

Is it the background tasks? Also, the blocking is in tempdb. I see a python connection sleeping with statement drop table #temp and other sessions are blocked, even some of my DMV queries get blocked by this.

I think the developer has drop if exists to be able to rerun the code in the same session (at least I always do that). It is ad hoc queries in a session in SSMS.

I first would like to confirm that my conclusion seems reasonable and then understand the connection.

I am not convinced from the answer below. If queries time-out because of the blocking and then restart, it seems reasonable that sos_scheduler_yield wait type goes up.

1 Answer 1

4

There is no direct relationship. sos_scheduler_yield generally indicates CPU is a bottleneck, as sessions yielding the worker (cpu) are having to wait to get scheduled on a worker again.

Blocking on LCK_M_SCH_S means that a session is performing DDL and causing other sessions to wait.

The sessions performing DDL may also be driving high CPU utilization. For example if you're rebuilding an index offline you would hold an Sch-M lock while performing a long-running CPU-intensive operation.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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