SQL Server 2008 R2 Max Worker Threads and performance

- August 17, 2010

The parameter max worker threads is used to configure the number of worker threads available to Microsoft SQL Server processes, this helps optimize performance when large numbers of clients are connected to the server. The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query request, which improves performance. The default value of this options is 0, and allows SQL Server to automatically configure the number of worker threads at startup. Sometimes to improve performance is better to specify a specific value, you can use the following table as reference:

Number of CPUs	32-bit computer	64-bit computer
<= 4 processors	256	512
8 processors	288	576
16 processors	352	704
32 processors

Source: http://technet.microsoft.com/en-us/library/ms187024.aspx

Based on Bob Duffy blog http://blogs.msdn.com/b/boduff/archive/2008/05/17/configuring-max-worker-threads-in-sql-2005.aspx 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

Bob also recommends a formula to determine the max worker processes 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)

Be aware that 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, so in that case may need to set an specific value for the max worker threads option.

Regards ,

Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP