Max Worker Threads - Lower and Upper Bounds - The Correct Formula

  • Article

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.

Lower boundQ: 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'.

Upper boundQ: 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:

            Configure the max worker threads Server Configuration Option

            Server Configuration Options (SQL Server - MAX WORKER THREADS)

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