SQL SERVER – Default Worker Threads Per Number of CPUs

The default Worker Threads limit depends on SQL Server version, logical CPUs and memory. A workshop reader asked about automatic configuration.

SELECT cpu_count, scheduler_count, max_workers_count
FROM sys.dm_os_sys_info;
SELECT name, value, value_in_use
FROM sys.configurations WHERE name = N'max worker threads';

Modern 64-bit defaults use 512 + (CPUs – 4) × 16 above four CPUs, through 64 CPUs. Newer larger-server defaults use × 32 above 64 CPUs. That larger-server rule has release-specific applicability. Check the exact release before using the formula.

From SQL Server 2017, servers below 2 GB memory use half the default maximum. My earlier unconditional 512-worker minimum was incorrect. Read max_workers_count for the current instance.

A configured zero means automatic selection. Workers are not allocated one per connected user, and some tasks operate outside this pool. I keep automatic configuration unless evidence justifies a change. Investigate blocking, long work and THREADPOOL pressure first.

Reference: Version and memory-dependent worker defaults.

Related reading

• SQL Server Performance Tuning Practical Workshop
• Twitter

An automatic setting is not zero available workers, it is a request for the engine to select its supported default.

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