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.
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