Last week I was discussing with some friends, how SQL Server works with threads: Max Worker Threads – Lower and Upper bounds, Thread Pooling, SQLOS Scheduling, Parallelism, Resource Governor – CPU limits, Call Stack, Thread Starvation, Dedicated/System and User Threads…so, all the topics related to Threads.
If you don’t know exactly how Process and Threads works in Windows, I strongly recommend you to read Mark Russinovich’s blog post: Windows Pushing the limits: Process and Threads.
Also, Denzil Ribeiro’s post, is very useful to understand how Max Worker Threads works.
Q: Is it possible to set Max Worker Threads lower than Minimum Value?
A: No, it’s not possible.
128 threads is the minimum value for this configuration, if you try to set it to a value lower than 128 the following error will be raised:
sp_configure ‘max worker threads’, 127
Msg 15129, Level 16, State 1, Procedure sp_configure, Line 161
‘127’ is not a valid value for configuration option ‘max worker threads’.
Q: Is it possible to set Max Worker Threads higher than Formula Value (table below)?
A: Yes, it’s possible.
Manually, you can set the value higher than the calculated upper bound.
If you use the Default Value, 0 (zero), the formula below will calculate the upper bound.
In Books Online we have some important references, but the formula for computers with more than 64 processors is missing:
In the table below, you can see the formula for each architecture and numbers of processors:
In the formula we have the numbers:
256 and 512, these numbers are the initial maximum workers that can be created
8, 16 and 32, these numbers are the additional numbers of threads per processor