max degree of parallelism Option

Use the max degree of parallelism option to limit the number of threads (from 0 through 32) to use in parallel plan execution. The default value is 0, which uses the actual number of available CPUs. (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).) 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 threads used by a single query execution. If a value greater than the number of available CPUs is specified, it is ignored.

You should 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).

To set the max degree of parallelism option

See Also