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.

Reference: Pinal Dave (https://blog.sqlauthority.com)