This post is for the MAXDOP option – how to set it in a SQL Server instance. The option is discussed much on the forums and there are different recommendations for it. If you don’t understand what it does mean, then you’d better leave it with its default value of 0. If you want to play with it you can do something so that it brings benefit to your instance.
If you a have a very large number of queries that are executed at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value. For example, you can set the MAXDOP value to 4.
If SQL Server uses a serial plan, it will use only one processor. However, if SQL Server uses parallelism, it must use all the configured processors (as determined by the MAXDOP query hint configuration) for the execution of a parallel plan. For example, if you use MAXDOP=0 on a 32-way server, SQL Server tries to use all 32 processors even if 7 processors might perform the job more efficiently compared with a serial plan that uses only one processor. Because of this all-or-nothing behaviour, if SQL Server uses the parallel plan, and if you do not restrict the MAXDOP query hint to a maximum value of 8, the time that is required by SQL Server to coordinate all the processors on a high-end server outweighs the advantages of using a parallel plan. – https://support.microsoft.com/en-gb/kb/2806535
I considered this option on a 32 core (NUMA) dedicated SQL Server with Enterprise edition installed. MAXDOP = 8 was chosen because of the existence of long duration operations and queries. Long lasting queries will benefit of increasing the parallelism. The question is how “long” queries. Most of the queries in an OLTP system are short and fast, and smaller part of them ask for parallelism in execution. Tests were done with default value for Cost threshold for parallelism = 5. For the Cost threshold for parallelism you can check this post.
Results
Results next were done for the heavy queries and operations in an instance. They would be most affected in an instance where you’ll be possibly change the MAXDOP option setting.
The next figure shows how CHECKDB performed with a 300 GB database. CHECKDB was run without using some of the arguments.
Figure 1. CHECKDB with changed MAXDOP
Figure 1 shows how better the CHECKDB command on a database is when the MAXDOP option is set to 8 instead of 0 (on a 32 core instance).
Next, Figure 2 (a, b) shows how Complex queries (multiple joins on biggest tables) behaved under MAXDOP option set to 8 and compared to 0. Two test series were done for two groups of queries, each group having three queries. Each group was executed several times, so the average duration times are shown in the figures.
Figure 2 a. Complex queries with changed MAXDOP
Figure 2 b. Complex queries with changed MAXDOP
The test was run for queries with high-parallelism in their execution plans, i.e. for queries using the biggest tables of the database, joint with several JOIN operators. Queries were long duration, as you can see from Figure 2, executing for more than a minute. The MAXDOP option set to 0 goes into application for heavy queries. However the results are slightly lower for MAXDOP option set to 8. Remember that those were the heaviest queries, usually used for reporting purposes.
Last Figure 3 shows how Rebuild indexes showed difference under the different settings of MAXDOP. The test was done on a database with the FULL recovery model. The rebuild is high-intensive write operation to the transaction log.
Figure 3. Indexes rebuild with changed MAXDOP
The rebuild indexes job got slower for about 8%. This is an example where the MAXDOP option was better than when it was set to 8.
As stated in the beginning of this post, the MAXDOP option will contribute a lot to the medium and short duration queries and operations in the instance.
Advice for the MAXDOP setting
SQL Server 2005 and later versions
For servers that use more than eight processors, use the following configuration: MAXDOP=8
For servers that use eight or fewer processors, use the following configuration: MAXDOP=0 to N. Note In this configuration, N represents the number of processors.
For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
For servers that have hyper threading enabled, the MAXDOP value should not exceed the number of physical processors.
For servers that have NUMA configured and hyper threading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.
If you have a very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value. For example, you can set the MAXDOP value to 16.
If you have a very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value. For example, you can set the MAXDOP value to 4.
If SQL Server uses a serial plan, it will use only one processor. However, if SQL Server uses parallelism, it must use all the configured processors (as determined by the MAXDOP query hint configuration) for the execution of a parallel plan. For example, if you use MAXDOP=0 on a 32-way server, SQL Server tries to use all 32 processors even if 7 processors might perform the job more efficiently compared with a serial plan that uses only one processor. Because of this all-or-nothing behavior, if SQL Server uses the parallel plan, and if you do not restrict the MAXDOP query hint to a maximum value of 8, the time that is required by SQL Server to coordinate all the processors on a high-end server outweighs the advantages of using a parallel plan.