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
By default, when SQL is installed on a server, the parallelism setting is set to 0 meaning that the optimizer can utilize all the available processors to execute an individual query. This is not necessarily the most optimal setting for the application and the types of queries it is designed to support. It is therefore necessary for project teams to examine the impact of parallelism on query performance and server stability and make a considered choice as discussed below.
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.
For Further Information
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
]]>