Set Max Degree of Parallelism for SharePoint 2013 in SQL Server 2012

What is max degree of parallelism in SQL Server?
SQL Server configuration option ‘max degree of parallelism’ limits the number of processors used for the execution of a query with a parallel plan. By default, it’s set to ‘0’ to allow all processors available. SharePoint 2013 requires that the “max degree of parallelism” option be set to exactly 1 to ensure each request is served by a single SQL Server process.

You may get a configuration failed error in SharePoint 2013 if the max degree of parallelism isn’t set to 1.
This SQL Server instance does not have the required “max degree of parallelism” setting of 1. Database provisioning operations will continue to fail if “max degree of parallelism” is not set 1 or the current account does not have permissions to change the setting.

In SharePoint 2010 days, the Maximum Degree of Parallelism option was recommended but optional. But in SharePoint 2013, it is mandatory to set its value to “1”; otherwise, the SharePoint configuration wizard will fail.

Set Max Degree of Parallelism for SharePoint 2013 in SQL Server 2012

Here is how to set the maximum degree of parallelism for SQL Server instances.

1. Go to SQL Management Studio and connect to the instance which hosts SharePoint databases.

   

2. From the Object Explorer pane, Right-click on the SQL Server and select Properties.
   
3. From the Properties window, Select the ‘Advanced’ Node
4. Scroll to the bottom and change the Max Degree of Parallelism value from 0 to 1.
5. Click ‘OK’ to apply max degree of parallelism setting in SQL Server 2012.

Enable “Max degree of parallelism” from SQL Server Management Studio – Transact-SQL:

You can set the max degree of parallelism option from Transaction SQL by running the below code from SQL Server Management Studio.

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