Max Worker Count: Why Typing 10000 Slowed a Server

Max worker count looks like a speed dial, but it behaves like a safety limit. One hand-typed 10000 on a small server shows what happens when it is treated as a dial.

The Accident

At one client, an administrator opened the Processors page of Server Properties. They saw a 0 next to Maximum worker threads. They read it as no worker threads and typed 10000 on a server with 8 processors. The default for that server was 576. Soon after, the application began to crawl, and users called in.

The health check query showed a limit of 10000, and the audit trail showed the change from 0. Setting the value back to 0 returned the system to normal speed in about three minutes. The old health check recorded the fix, not the exact mechanism, so the cause on that server is not known.

What the 0 Means

A 0 in this setting does not mean zero threads. It means SQL Server chooses the cap from the number of logical processors. On 64-bit SQL Server, the rule gives 512 workers for up to four processors. Each processor above four adds 16 more, up to 64. Optimal Value for Max Worker Threads in SQL Server has the full table, so this post does not repeat it.

This query reads what your server uses. The column value holds the configured number, and value_in_use holds the number in effect. The query changes nothing.

SELECT name, value, value_in_use, minimum, maximum
FROM sys.configurations
WHERE name = N'max worker threads';

A 0 in both columns means SQL Server decides. Anything else is a number someone chose, and the range runs from 128 to 65,535. A value of 10000 sits inside that range, which is why SQL Server accepts it without a word of warning.

Is the Limit Your Real Problem?

Before you change anything, find out whether workers run short at all. This query shows the workers SQL Server has created next to the limit. It reads the current moment, so the count changes between runs.

SELECT (SELECT COUNT(*) FROM sys.dm_os_workers) AS WorkersCreated,
       i.max_workers_count AS WorkerLimit
FROM sys.dm_os_sys_info AS i;

A count far below the limit means the limit is not your problem. A count that climbs toward the limit is a reason to look closer, and the proof is waiting tasks. THREADPOOL Waits: When Raising Max Worker Count Helps shows how to measure them.

A hand-set 10000 makes the same check meaningless, because the limit is out of reach. No workload on 8 processors comes near that count, so the limit can never be the bottleneck. A higher cap lets SQL Server create more threads under load. Each thread needs stack memory, and more threads on the same processors mean more switching between them.

When a Fixed Value Hurts

A fixed value hurts in three ways. A value above the default invites the load problem above. A value below the default can starve a busy server of workers, and queries then wait for a thread. A fixed value also stops following the formula. Suppose the same virtual machine later gets more processors. The default would grow with them, and the typed number would stay where it was.

To put the default back, run the statement below as an administrator. The option is an advanced option, so Show advanced options must be on. Leave that setting as you found it. The statement changes a server setting, so it is not part of the demo. Write down the old value first, because that value is your undo.

EXEC sp_configure 'max worker threads', 0;
RECONFIGURE;

You could argue that a default is a guess, and that a busy server needs more threads. The default is a formula, and the way to beat it is evidence. In my health checks, I have raised the value only four times to gain speed. The rest of the time I leave it at 0.

What to Remember

Read the max worker count before you touch it. If value_in_use is 0, SQL Server already chooses well for your processors. If it shows a number, find out who chose it and why.

I have never lowered the value either, because a better goal is a system that uses the default threads well. Change the value only with evidence, by a small step, and with the old number written down. After the change, read the same two queries again. If nothing improved, put the old value back, because a setting that does not help is only a risk.

A worker thread cap is not a speed dial, it is a safety limit.

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