Max Worker Threads Configuration

In 2005 the default value for this setting has changed from 255 (SQL2k) to 0 (dynamic in SQL2k5). According to various sources, the following table shows the automatically configured number of Max Worker Threads for SQL Servers (2005) with different numbers of CPUs when the SQL Server database engine is left to dynamically set this value:

CPUs	Auto Configured Max Worker Threads (32 bit Server)	Auto Configured Max Worker Threads (64 bit Server)
<= 4 CPUs	256	512
8 CPUs	288	576
16 CPUs	352	704
32 CPUs	480	960

The formula to determine the max worker processes is as follows:

For x86 systems where total number of logical processors <=4

# max worker threads = 256

Otherwise:

# max worker threads = 256 + ((# Procs – 4) * 8)

For x64 systems where total number of logical processors <= 4

# max worker threads = 512

Otherwise

# max worker threads = 512 + ((# Procs – 4) * 16)

On an 8 processor x86 machine (could be 4-proc HT or 4-proc dual core machine, therefore shows 8 logical procs), max worker threads will be configured to 288 (256 + (4 * 8))

You can find out how many threads have been configured by looking at the max_workers_count column in the sys.dm_os_sys_info DMV.

Note:

SQL and indeed Windows has NO method of identifying the difference between physical and logical processors – i.e. multi-core and hyper-threaded processors appear to windows (and therefore) SQL as processors. As such 4 x Dual core would appear as 8 logical processors – and max worker threads would be sized on this basis regardless of whether these are 8 x physical, 4 x dual core or 4 x HT.