X hits on this document

Word document

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and ... - page 45 / 76

280 views

0 shares

0 downloads

0 comments

45 / 76

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability41

Figure 15

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 affinity mask 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

Document info
Document views280
Page views280
Page last viewedFri Dec 09 06:20:39 UTC 2016
Pages76
Paragraphs1372
Words23715

Comments