X hits on this document

Word document

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

363 views

0 shares

0 downloads

0 comments

43 / 76

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

max degree of parallelism

The max degree of parallelism option defines the maximum number of threads (CPUs) that can be used for parallel query execution. The max degree of parallelism option has a default setting of 0 after installation. This means all processors can be used in parallel to execute a query.

Before each parallel query is executed, SQL Server 2005 checks the available resources such as processors, available worker threads, and memory to determine the number of processors to use for the query. Figure 14 shows where to modify the max degree of parallelism option.

Figure 14

When there are multiple executions of the same query, the query execution can be single-threaded with different degrees of parallelism dependent on the actual workload at the time of execution.

This dynamic for parallel query execution is based on higher resource consumption. Multiple streams of data get sorted in parallel and are merged afterward, making the consumption of buffers in the cache higher. In addition, the momentary CPU consumption of a single query usually increases with the degree of parallelism.

Microsoft Corporation © 2005

Document info
Document views363
Page views363
Page last viewedMon Jan 23 11:01:47 UTC 2017
Pages76
Paragraphs1372
Words23715

Comments