Max degree of parallelism (MAXDOP) is the SQL Server instance level setting that describes the number of processors or cores to be used for a particular query that is selected by the database engine to have a parallel execution plan. By setting the max degree of parallelism value, we can limit the number of processors/cores to use in the parallel execution plan. But we can also override the value set at the SQL Instance level by configuring it at the database level (setting MAXDOP value in database properties) and also at the query level by using the MAXDOP query hint.
MAXDOP value depends on the number of processors/cores of the server. You can keep the MAXDOP value the same as the number of processors/cores of the server. However, if the server has more than 8 cores then it is recommended to keep the MAXDOP value maximum to 8. For example, set the value of MAXDOP to 8 for an 8-core CPU and also for a 16-core CPU.
The default value of MAXDOP is 0, which means it can use all the available processors/Cores up to 64. But it is recommended to reset this value to greater than 0 according to your requirement. If you do not want to generate parallel plans for queries that met the cost threshold condition, set the value of the maximum degree of Parallelism to 1. So, in short MAXDOP 1 means, no parallel plan.
How to set max degree of parallelism in SQL Server
You can set max degree of parallelism using SQL Server Studio Management (SSMS) OR using T-SQL.
Configure max degree of parallelism (MAXDOP) using SSMS (Instance/Server level)
To change the MAXDOP value using SSMS, follow the below-mentioned steps.
- Open SQL Server Management Studio (SSMS)
- Right-click on the SQL Server Instance
- Click on Properties
- Select Advanced on the left pane
- On the right side, Under the Parallelism
- Set Max Degree of Parallelism value
Configure MAXDOP using T-SQL (Instance/Server level)
To change the MAXDOP value using the T-SQL command, Execute the below-mentioned T-SQL.
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure ‘max degree of parallelism’, 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
The above code will change the MAXDOP value to 8. You can change the value according to your requirement. It is always better to monitor the performance of the SQL Server for some time after changing the value of MAXDOP. You can test with different values of MAXDOP and check the performance of SQL Server, it is not necessary that the same value will have the same performance for different SQL instances.
You can confirm the changes by checking the Instance properties using SSMS or by executing the following code.
EXEC sp_configure ‘max degree of parallelism’;
GO
It will show you the currently configured value for max degree of parallelism as shown below.
Configure MAXDOP for the database (database level)
To configure the max degree of parallelism at the database level, follow below mentioned steps.
- Right-Click on the database
- Click properties
- Select Options on the left pane
- On the right pane, under the Database Scoped Configurations, you will see Max DOP
- Change as per your requirement and Click OK to apply the changes.
Configure MAXDOP for SQL query (query level)
We can specify the max degree of Parallelism at the query level as well, it will be effective only for that particular query. For example, the below-mentioned query will take benefit from the MAXDOP value defined in the query hint.
Select EMPID, EMPName
from dbo.employees Option(MAXDOP 2)
It is important to note that the MAXDOP value defined at the query level will override any MAXDOP setting at the Server level or database level, the MAXDOP value in the query will take precedence. In the same way, the MAXDOP value configured at the database level will override the MAXDOP value configured at the Instance/Server level. If there is no MAXDOP defined at the database level and query level, then the Instance/server level MAXDOP configuration will take effect for all the queries fulfilling the cost threshold for parallel plan execution.
Conclusion
In this article, we tried to explain the max degree of parallelism, and its purpose, and discuss how to configure it at the Instance/server, database, and query level. Next, we will try to write an article about the cost threshold for parallelism which is pretty much related to this article.