Configuring Max Worker Threads in SQL 2005

  • Article

One of the changes in SQL 20005 is that the default value of Max Worker Threads has changed from 255 to zero, meaning that SQL Server now automatically determines the optimum number of threads. You can determine the maximum number of threads SQL Server has actually configured and how many it is currently using by the following DMVs:

select max_workers_count From sys.dm_os_sys_info

select count(*) from sys.dm_os_threads

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)

One reason why Hyper-threading causes SQL Server a lot of hassle is that it appears as two physical cores, so may result in SQL Server allocating too many threads.

In a previous blog Indicated the relationship between how to configure the max server memory base don the platform and max number of worker threads.