SQL Server instance does not have the required “max degree of parallelism” setting of 1
Applies to: SharePoint Server 2013
When setting up a SharePoint 2013 lab, I ran into the following error while running the configuration wizard:
An exception of type Microsoft.SharePoint.SPException was thrown… 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 to 1 or the current account does not have permissions to change the setting.
Ok then. So what is “max degree of parallelism” that has SharePoint all bent out of shape? Well, according to Mat Stephen:
This option sets the maximum number of processors SQL Server can use for one query. If SQL Server has to bring back a lot of data (lots of rows) for a query it sometimes makes sense to break the query down into a number of smaller queries, each query returning a subset of the total rows. This way SQL Server can make use of more than processor and hence on multi processor boxes it can potentially return a lot of rows more quickly, for a given query, than it can on a single processor box.
The default value of 0 will allow SQL to use all of the processors for parallelism. Setting this value to 1 will basically disable parallel planning, with only one processor being used.
Ok, so lets’ set it to 1 and get back to business.
From SQL server management Studio, right click your server in Object Explorer and click Properties. Under the Advanced group modify the property “Max Degree of Parallelism”.
Because the database was already created, I will need to delete and re-create it and run the config wizard again.
Note: Were I using a highly privileged account (like farm, or domain admin) I would not have come across this error as SharePoint would simply have changed the value for me without me knowing. This is why I follow least privilege practices even in my lab.
Setting this to 1 got made SharePoint happy and the configuration continued normally.