Worker threads with multiple instances

Question

I'm trying to understand the whole subject of worker threads for multiple instances, there's lots of advice on configuring workers but nothing I can find which directly addresses when the server has multiple instances.

Each instance will/can auto configure workers based upon the cpu count, if I have 4 instances for example the theoretical worker count will be 4 times that of a single instance, obviously, but is that actually good .. Am I likely to experience thread starvation? Assume a low cpu count.

I can't publicly discuss the finer details behind the question, however as an example a 4 cpu box would auto configure 512 workers - fine, but with 4 instances the auto config would be 2048 workers ( max ) assuming every instance is busy and gets close to its worker count ( including background workers ) potentially might I expect to have issues?

I have two instances currently installed, not a full database population and a combined worker count of around 400, hopefully that gives the background to my question.

Answer 1

Am I likely to experience thread starvation? Assume a low cpu count.

Thread starvation doesn't actually have to do with the physical CPU count. If you have lots of worker threads, and a low CPU count, then it will just be slower to schedule all of those threads on the limited number of CPUs. See Erik Darling's blog post Max Worker Threads: Don’t Touch That:

The thing is, all changing that setting does is help you not run out of worker threads. It doesn’t make queries run any better or faster. In fact, under load, performance will most likely be downgraded to Absolute Rubbish© either way.

What’s worse? Running out of worker threads and queries having to wait for them to free up, or having way more queries trying to run on the same CPUs? Six of one, half dozen of punching yourself squarely in the nose.

This becomes even more complex in your scenario with multiple instances on the same box ("instance stacking"). Within a single instance, SQL Server uses cooperative scheduling to (try to) make sure that queries share CPU resources in an optimal fashion.

Unfortunately, one instance doesn't know anything about what the other instances are doing. So each instance will compete for processor time "un-cooperatively" (generally called preemptive scheduling) due to queries running on the other instances.

Is this likely to be a problem for you? It really depends on how busy the servers are. But hopefully this answer helps to clarify the situation with regards to worker threads.