Export (0) Print
Expand All
Expand Minimize
11 out of 12 rated this helpful - Rate this topic

max worker threads Option

Updated: 5 December 2005

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.

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 client connection to consume fewer system resources. However, with hundreds of connections to the server, using one thread per connection 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 client connections, which improves performance.

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

ms187024.note(en-US,SQL.90).gifNote:
We recommend 1024 as the maximum for 32-bit SQL Server and 2048 for 64-bit SQL Server.

When the actual number of user connections is less than the amount set in max worker threads, one thread handles each connection. However, if the actual number of connections 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.

The default setting of max worker threads in SQL Server 2000 was 255. Upgrading an instance of the SQL Server 2000 Database Engine to SQL Server 2005 retains the configuration value for max worker threads. When upgrading, we recommend changing the SQL Server 2005 max worker threads value to 0, to allow the Database Engine to calculate the optimal number of threads.

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.

ms187024.note(en-US,SQL.90).gifNote:
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.

Release History

5 December 2005

New content:
  • Added the section Upgrading from SQL Server 2000.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.