SharePoint 2013: This SQL Server instance does not have the required "max degree of parallelism" setting of 1

SharePoint 2013: This SQL Server instance does not have the required "max degree of parallelism"  setting of 1

At the Time of Configuration wizard or by power-shell you can get following error, If you are configuring least account permission.

Error:

New-SPConfigurationDatabase : 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....


What is Parallelism and what is the Role?

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.

Reason of the error:

The default setting for this in SQL is 0 and FOr SharePoint 2010 its doesnt need to change.

But In SharePoint 2013 you need to do change it to 1 . Then only you can create config database and installation will complete.


Resolution:

1. Login to SQL Server by using Management Studio.

2. Right click on the SQL Service Instance and select Properties

3  Under Advanced, locate Max Degree of Parallelism under the Parallelism section and switch the settings from 0 to 1.



4. CLick Ok and re run the wizard.

Please note at the time of getting error the config DB will create, So once you are going to run the wizard /powershell after fixing the Parallelism issue dont forget to delete old config DB which were created at the time of error. Other wise you will get the error.


You can say some time you dont get error this, it happens because from which account you running the wizard if that is having sys admin rights on SQL server then at time of configuration it automatically change the settings and switch to 1 , but once you follow proper permission level of accounts then you will get this error.