SQL SERVER – Optimal Value Max Worker Threads

During Comprehensive Database Performance Health Check, one of the DBas asked what is the most optimal value for Max Worker Threads. The question was indeed asked when my customer looked at the value of Max Worker Threads in the Processor sections of the Server Property, they were really worried. The default value of the Worker Threads is always set to zero.

Looking at the zero value, DBAs were really really worried as they believe it is not good to have zero value for their worker thread. They all believed that they should set this value to as many as processors they have in their system. During the conversation, they were very much in panic and blaming each other for the value zero in the max worker threads settings.

Default Max Worker Threads

I really wished that Microsoft would have done a better job explaining these settings on the same page as well. Actually, the default value of the max worker thread is zero but it does not mean your system is using zero thread. If you really want to know how many threads are active in your system, you can run the following DMV.

SELECT max_workers_count
FROM sys.dm_os_sys_info

Once you run the script, you will see what is the current value of the threads in your system.

When the value is set to zero, SQL Server uses default algorithm, which is as follows:

For x86 (32-bit) upto 4 logical processors  max worker threads = 256
For x86 (32-bit) more than 4 logical processors  max worker threads = 256 + ((# Procs – 4) * 8)
For x64 (64-bit) upto 4 logical processors  max worker threads = 512
For x64 (64-bit) more than 4 logical processors  max worker threads = 512+ ((# Procs – 4) * 16)

Here is the table which indicates the default values of the max worker threads for different CPUs

Optimal Value of Max Worker Threads

Well, the optimal value of this particular settings is the default value. Yes, you read it correctly. Leave these settings as it is. There is no need to change this setting to any other value at all. If you increase the max worker threads when your system is running out of the worker threads, it is quite possible that your system may be not able to handle the increase and you will notice more and more waiting threads. When your system starts running out of the worker thread, the best possible solution you have is to look at your other resources and start tuning your system.

So yes, the optimal value of the max worker threads is 0 (ZERO).

Do leave a comment or reach out to me if you need further help to fix your system’s performance.

How to Tell If Max Worker Threads Is Really the Problem

To see the number SQL Server actually calculated for your machine, run SELECT max_workers_count FROM sys.dm_os_sys_info. That number, not the zero on the properties page, is your real limit, and on most modern servers it is larger than people expect.

If you suspect you are running out of workers, look for THREADPOOL waits in sys.dm_os_wait_stats. When they show up, the cause is usually long blocking chains or too much parallelism holding many workers at once. Fix those first, with better indexes, shorter transactions and sensible parallelism settings, and the thread shortage usually goes away without touching this setting. Raising the limit only lets more threads wait in the same queue.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.