Alex Rosa & SQL Server & Microsoft Azure

DECLARE dba AS human … SET dba = family + rdbms + friends + career + day_by_day + certifications + theory + real_life_experience

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

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 bound

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’.

Upper bound

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:

            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