Unable to understand correlation between MaxWorkers created and CPU utilization

Question

I am having a strange issue which i am unable to understand:

On my server with 64 Logical processors having maxworkers 1472, there are peak periods in day where workers created for an hour lets say goes beyond maxworkers available. Ranges somewhere 1800-1900 in that duration.

From what I can understand and saw is there a burst of blocking happening in that period. Lots of update commands blockings its own process while that UPDATE will block other inserts and selects over that range of couple of minutes.

However CPU does not tend to increase and stays steady at 30% on avg. Should the CPU not spike to 100% or atleast much higher than 30% when workers available are exhausted ? OR those waiting threads are not even on schedulers?

Please help me understand , thanks

Answer 1

Correlation between MaxWorkers created and CPU utilization

I strongly recommend you to read the Thread and Task Architecture Guide doc. I'll quote some relevant parts:

About the higher number of workers and the relation between maxworkers and CPU:

On a very busy SQL Server Database Engine, it's possible to see a number of active tasks that's over the limit set by reserved threads. These tasks can belong to a branch that is not being used anymore and are in a transient state, waiting for cleanup.

About the blocks you're experiencing:

If you suspect that there is a performance problem, it is probably not the availability of worker threads. The cause is more likely something like I/O that is causing the worker threads to wait.

About the CPU consumption not going beyond 30% while having requests blocking each other:

When a worker's quantum expires and is no longer active, the respective task is placed in a FIFO queue in a RUNNABLE state, until it moves to a RUNNING state again, assuming the task won't require access to resources that are not available at the moment, such as a latch or lock, in which case the task would be placed in a SUSPENDED state instead of RUNNABLE, until such time those resources are available.

And here's the list of states a worker can be (from sys.dm_os_workers). Unless it's on the RUNNING state, it's not using the CPU, therefore, you could have way more requests existing at the same time than the 1472 you expected to see if they're not in fact using the CPU.

Worker state. Can be one of the following values:

INIT = Worker is currently being initialized.

RUNNING = Worker is currently running either nonpreemptively or preemptively.

RUNNABLE = The worker is ready to run on the scheduler.

SUSPENDED = The worker is currently suspended, waiting for an event to send it a signal.

How to diagnostic your situation

With that in mind, you should check if you don't have a bottleneck caused by other resources (e.g., disk, network) required by the requests that are blocking the others. Paul Randal's article SQL Server Wait Statistics (or please tell me where it hurts…) has a terrific query to retrieve info about what could be causing trouble on your server. Before you assume the problem is the number of workers, I advise you to run that diagnostic on your server.

Answer 2

Not sure I follow what you mean by “workers created for an hour”, it’s not clear how long these workers stay alive so we don’t know how many were existing at one time.

A thread is either waiting or working. If it is working, it is using cpu, if it is waiting (for a physical IO, for a lock to be released...) then it is suspended and will put on the runnable queue when the required resource is available. The runnable queue is scheduled to use CPU when it’s available. Think of it like a waiting process is asleep and will be woken up by it’s wait completing. You will usually have lots of waiting threads as you will have lots of idle connections, these will not be in the runnable queue, so will not be using CPU.

Some resources are expected to be much quicker. Instead of going to sleep when a thread can’t obtain this resource, it will do a spinlock, this means it is actively doing something with the cpu and then checking for the resource again. This sort of wait will be spending time on the CPU.

Have a read of https://www.sqlshack.com/sql-server-spinlocks/ for further details.