Purpose of max worker threads option:
Thread pooling helps optimize
performance when large numbers of clients are connected to the server. Usually,
a separate operating system thread is created for each query request. However,
with hundreds of connections to the server, using one thread per query request
can consume large amounts of system resources. 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.
Use the max
worker threads option
to configure the number of worker threads available to Microsoft SQL
Server processes. SQL Server uses the native thread services of the Microsoft
Windows 2000 and Windows Server 2003 operating systems so that
one or more threads support each network that SQL Server supports
simultaneously, another thread handles database checkpoints, and a pool of
threads handles all users.
The max worker threads option is an advanced option. If you
are using the sp_configure system stored procedure
to change the setting, you can change max worker threads only when show
advanced options is
set to 1. The system must be restarted in order for the new setting to take
effect.
Calculating max
worker threads:
The
default value for max worker threads,
0, allows SQL Server to automatically configure the number of worker threads at
startup. This setting is best for most systems; however, depending on your
system configuration, setting max worker threads to a specific value sometimes improves
performance.
The
following table shows the automatically configured number of max worker threads
for various combinations of CPUs and versions of SQL Server.
|
Number of CPUs
|
32-bit computer
|
64-bit computer
|
|
<= 4 processors
|
256
|
512
|
|
8 processors
|
288
|
576
|
|
16 processors
|
352
|
704
|
|
32 processors
|
480
|
960
|
Note: We recommend 1024 as the maximum for 32 bit SQL Server.
When the actual number of query request
is less than the amount set in max worker threads,
one thread handles each query request. However, if the actual number of query
request exceeds the amount set in max worker threads,
SQL Server pools the worker threads so that the next available worker thread
can handle the request.
Note: When all worker threads are active with long running queries,
SQL Server may appear unresponsive until a worker thread completes and becomes
available. Though not a defect, this can sometimes be undesirable. If a process
appears to be unresponsive and no new queries can be processed, then connect to
SQL Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
