Export (0) Print
Expand All

max degree of parallelism Option

SQL Server 2000

Use the max degree of parallelism option to limit the number of processors (a maximum of 32) to use in parallel plan execution. The default value is 0, which uses the actual number of available CPUs. Set max degree of parallelism to 1 to suppress parallel plan generation. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available CPUs is specified, the actual number of available CPUs is used.

Note  If the affinity mask option is not set to the default, it may restrict the number of CPUs available to Microsoft® SQL Server™ on a symmetric multiprocessor (SMP) systems.

Change max degree of parallelism rarely for servers running on an SMP computer. If your computer has only one processor, the max degree of parallelism value is ignored.

max degree of parallelism is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change max degree of parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).

In addition to queries, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP. Parallel checking can be overridden by using trace flag 2528. For more information, see Trace Flags.

To set the max degree of parallelism option

Enterprise Manager

Transact-SQL

SQL-DMO

See Also

affinity mask Option

cost threshold for parallelism Option

RECONFIGURE

Setting Configuration Options

sp_configure

Show:
© 2014 Microsoft