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 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 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.
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 |
 Caution |
|---|
| 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.
No comments:
Post a Comment