SQL SERVER – Best Value for Max Worker Count

I have been working as a SQL Server Performance Tuning Consultant for over 10 years. During the last 10 years, I have worked with over 100s of clients helping them with Comprehensive Database Performance Health Check. My experience says that most of the performance problems occur because the user accidentally configures the values which they do not understand. Recently, one of my client who works for the large bank accidentally set incorrect max worker count, which lead to big performance problem for their applications.

History

While going through various configuration settings of the server, my client ended on the following screen of Processors, where the Maximum Worker Threads are listed. He got alarmed when he noticed the value of Maximum Worker Threads to zero (0). He felt 0 worker threads can be big performance issue and he immediately set this to the much higher value of 10000 (yeah you read it correctly – ten thousand). After the setting up the higher value, he got busy with other task and soon, their clients started to call in not responding site. It was indeed a surprise to the entire organization, why suddenly their application started to crawl.

Resolution – Max Worker Count

As I have a maintenance contract with them, they immediately called me up and we started to work together to identify what has changed. I ran the following query which is part of my standard health check to get very interesting results.

SELECT max_workers_count, cpu_count
FROM sys.dm_os_sys_info

Above query returned 10000 worker threads for the 8 CPU. I quickly asked them if they have an audit trail of what has changed in the server and we realized from that DBA had changed the max_workers_count from zero to 10000, thinking it will give better performance.

However, the reality is that when max_workers_count is set to zero that means it is set to the default recommended value and there is no need to change that to any other value unless there is a very specific condition you are facing.

Here is the algorithm of the default value for max worker threads:

For x64 (64-bit) up to 4 logical processors  max worker threads = 512
For x64 (64-bit) more than 4 logical processors  max worker threads = 512+ ((# Procs – 4) * 16)

As their system had 8 CPU counts, when you keep the value of the max worker threads to zero it means 512 + (8-4)*16) = 576.

We immediately changed the max worker threads to 0 and within 3 minutes entire system came to normal speed.

In my career of 10 years and with over 100s clients for Comprehensive Database Performance Health Check, I have changed the value of max worker count only 4 times to gain performance. Rest of the time, it is best to leave Max Worker Count to default values.

UPDATE: As many of the readers asked about in what situation, I would increase the Max Worker Thread Count, I have written a blog post explaining my experience with the same: SQL SERVER – Changing Max Worker Count for Performance.

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