MAXDOP – What is best approach to use it!!!


The Maximum Degree of Parallelism (MAXDOP) setting will determine the degree to which SQL server will attempt to parallel process transactions which are received from the scheduler. As a general rule of thumb, OLTP environments with high transaction volume and repetitive queries should normally have a MAXDOP value of 1, as any benefit gained from parallel process would quickly be lost in overheads introduced in managing this. OLAP environments however (such as analysis services / data warehousing etc) where transactions are generally much fewer, however considerably more complex – these environments would benefit from a MAXDOP setting of 0 (unlimited) or near the number of CPUs in the system.

The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

1 The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
2 A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
3 The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

The MAXDOP value can be updated during run time by the following command, noting that the value below should be adjusted to suit your requirements;

sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE WITH OVERRIDE;
 GO
 sp_configure 'max degree of parallelism', 1;
 GO
 RECONFIGURE WITH OVERRIDE;
 GO

You may also want to clear the wait statistics after this to ensure your next checks on the system are clear / fresh statistics since this change has come into effect;

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

A good indicator on whether this value has been correctly configured is the CXPACKET performance counter. Where more than 5% of the total wait conditions are due to this counter, it’s most likely that the MAXDOP value will need to be adjusted.

When all is said and done, managing your MAXDOP and cost of parallelism settings are very much part of the DBA art form, where you will need to test and evaluate your workload to find the settings that are appropriate for your server. Here’s a couple steps to get started with on your server:

1 What kind of queries and how many transactions does my server see? Talk to your developers about the processes and take a look at your Batch Requests/second perfmon counter to help you evaluate this.
2 Check your current MAXDOP and cost of parallelism settings on your server. Are they set to the defaults?
3 What do your CXPACKET waits look like relative to other waits on your server? High CXPACKET waits might be an indication that the MAXDOP on your server is set to high. To find out your waits, Glenn Berry has an excellent DMV query within his SQL Server 2008 Diagnostic Information Queries that will provide this information for you.

Once you have this information, you can make a plan to adjust your parallelism settings, but you’ll need to test any changes to your settings before implementing them.