max worker threads Option

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 NT® 4.0 or Windows® 2000 operating system 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 a thread-per-connection can consume large amounts of system resources. max worker threads enables SQL Server to create a pool of worker threads to service a larger number of client connections, which improves performance.

The default setting for max worker threads (255) is best for most systems. However, depending on your system configuration, setting max worker threads to a smaller value sometimes improves performance.

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.

When the maximum number of worker threads is reached, SQL Server returns the following message:

The working thread limit of 255 has been reached.

Because Windows 98 does not support thread pooling, the option has no effect on those systems.

max worker threads is an advanced option. If you will be 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 setting takes effect immediately (without a server stop and restart).

To configure the maximum number of worker threads