When SQL Server runs on a computer with more than one microprocessor/CPU or on SMP (a computer-architecture where two or more identical processors can connect to a single shared main memory and I/O and can perform the same functions. In case of multi-core processors, the SMP architecture applies to the cores, treating them as separate processors), it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. In other words, the max degree of parallelism setting governs the maximum number of processors that a particular query statement or index operations (index data definition language) can utilize at run time.
The default value of 0 uses all available processors. Set max degree of parallelism to 1 to suppress parallel plan generation (serial plan only). Set the value to a number greater than 1 (up to a maximum of 64) to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.
What all benefit from parallel execution plan:
· Complex/Long running queries – During query optimization, SQL Server looks for queries that might benefit from parallel execution. It distinguishes between queries that benefit from parallelism and those that do not benefit, by comparing the cost of an execution plan using a single processor versus the cost of an execution plan using more than one processor and uses the cost threshold for parallelism (By default 5, this value of cost threshold for parallelism determines which queries are considered short, and they should therefore be run using serial plans) value as a boundary point to determine short or long query. In a parallel query execution plan, the INSERT, UPDATE, and DELETE operators are executed serially. However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. The actual data changes are then serially applied to the database.
· Index data definition language (DDL) – Index operations that create or rebuild an index (REBUILD only, not applicable to REORGANIZE), or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates.
· Other Operations – Apart from the above, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP.
The degree of parallelism value is set at the SQL server instance level and can be modified by using the sp_configure system stored procedure (command shown below). You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option. Note that this can be set differently for each instance of SQL Server. So if you have multiple SQL Server instances in the same server, it is possible to specify a different Maximum DOP value for each one.
--The max degree of parallelism option is an advanced option
--and let you set only when show advanced options is set to 1
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
--configuring to use 8 processors in parallel
--setting takes effect immediately (without restarting the MSSQLSERVER service)
sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
· Scenario 1 – For OLTP application, a typical setting is 1 would help. The reason for this is that in an OLTP environment, most of the queries are expected to be point queries which address one or a relatively small number of records. Such queries do not need parallelized processing for efficient execution. If there are specific queries which have a need for a setting greater than 1, then the source code needs to be examined to see if a MAXDOP hint can be availed.
· Scenario 2 – For OLAP application, the setting should typically be default 0 (up to 8 processors) or be greater than 1, because each queries, such application will use, will typical target thousands of, millions of records and also there might a scenario when you drop the index before ETL operation and re-create it once refreshed data is uploaded in typical data warehousing application. There will definitely be performance advantages in using multiple processors to do these works in parallel fashion.
Note: Using a setting of 0 in these applications is not recommended, especially when there are more than 8 processors in order to keep the coordination costs, context switching down to manageable levels. It is typical to start with a value of 4 and experiment with the reporting queries to see if this needs to be adjusted upwards.
Please note, using higher value for his setting means using more processors, in turn it means managing more threads, requires more cache synchronization and more context switching। So it’s recommended to test and evaluate your particular scenario in your particular environment before changing the default value in production server.
Further information can be found in:
· Max Degree Of Parallelism
http://technet.microsoft.com/en-us/library/ms181007.aspx
· Parallel Query Processing
http://msdn.microsoft.com/en-us/library/ms178065(SQL.90).aspx
· Parallel Index Operations
http://msdn.microsoft.com/en-us/library/ms191292(SQL.90).aspx
