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).
The configuration option "max degree of parallelism" defines, how many processors SQL Server can use to execute a single SQL command in parallel. This may decrease the response time of a single, long running SQL command. But this will also decrease the overall throughput of SQL Server. Therefore it only makes sense to use parallelism, if the number of CPUs used by SQL Server is greater than the number of all concurrently running SAP work processes. Since this is typically not the case, SAP strongly recommends to turn off parallel query execution
In BI systems while running aggregations build or deltas, it is recommended to turn this parameter to 0 (0=use all the CPUs) to speed up the processes.
Hi, this would cause a problem for the rest of the time if you set the value to 0. ie its only worth setting to 0 'during' the aggregate building in a SAP BW.
Note 879941 - Configuration Parameters for SQL Server 2005 gives details of this.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.