A parallel query’s degree of parallelism is the number of worker processes used to execute the query. This number depends on several factors, including:
The values to which of the parallel configuration parameters or the session-level limits,
The number of partitions on a table (for partition-based scans)
The level of parallelism suggested by the optimizer
The number of worker processes that are available at the time the query executes.
You can establish limits on the degree of parallelism:
Server-wide – using sp_configure with parameters shown in Table 7-1. Only a System Administrator can use sp_configure.
For a session – using set with the parameters shown in Table 7-2. All users can run set; it can also be included in stored procedures.
In a select query – using the parallel clause, as shown in “Controlling parallelism for a query”.
