SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability41
max worker threads
The max worker threads option defines the maximum number of worker threads that are available to handle user requests.
In contrast to competitor databases, an incoming connection to SQL Server 2005 does not result in a shadow process. Rather, the incoming connection is assigned to a scheduler thread. One scheduler thread is assigned to each of the processors or cores (CPUs) on which SQL Server is allowed to run. See earlier in this paper.
Incoming connections are assigned to the scheduler threads in a round-robin manner. This guarantees an even distribution of the hundreds of connections, or in many cases, thousands of connections that can result in large three-tier SAP systems.
Every scheduler thread has a pool of worker threads. When a query request arrives over a connection, the scheduler thread assigns a worker thread, which executes the query request. Then the worker thread is available to serve another connection.
There is no 1:1 relationship between the number of connections and worker threads. SQL Server 2005 can reassign connections to other schedulers if the original scheduler is overloaded, thereby avoiding an uneven CPU load.
As shown in Figure 16, for SQL Server 2005, the max worker threads option is dynamic and should remain at the default of 0. Note that this setting is different from the one used with SQL Server 2000, where a value had to be set because the logic used was not dynamic.
Microsoft Corporation © 2005