Sunday, 27 January 2013

SharePoint 2013 and Maximum Degree of Parallelism

Yesterday while installing SharePoint 2013 on production environment I encountered an issue stating that
"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."

Now, this is weird. I was simply trying to run a Powershell script for creating the Configuration as well as Other Content DB's required for my SharePoint Installation. I tried to run the SharePoint Configuration Wizard , which I was pretty sure would not work for me. And I was right. Boom the error came with the Configuration Wizard as well.

I tried to do some research across what this error actually mean and why I am getting this error.

It is important to note here that , if you are running the wizard for the first time itself with a user account that has sysadmin rights on SQL Server, then it will automatically make these changes in the SQL and you will not be required to do any of the below mentioned steps that I will be explaining shortly.

First , for all those, who are not aware of what maximum degree of parallelism means (just like me :p), I would explain what it means and then I will explain the error.

Maximum Degree of Parallelism -
It means number of processors that SQL Server use for one query. If SQL has to return lot of records then if we use the concept of Parallelism , then it breaks the query into smaller queries and each small query returns a subset of the records. All these queries run parallely on different threads.

By default the Maximum Degree of Parallelism is set to 0. It means that SQL can use all the processors available to execute a single query.

The decision whether to follow the Parallel Exceution Plan or Serial Execution Plan depends upon scenario to scenario. In case of OLTP systems, where queries are relatively smaller and they return less number of records, it is best to follow Serial Plan with Maximum Degree of Parallelism set to 1. However, in case of OLAP systems, where the queries are quite complex, it is preferable to go for Parallel Execution Plan with Degree of Parallelism set to 0.

Parallel Execution Plan has its own set of advantages and disadvantages. At one place it reduces the query execution time and on the other hand, by using multiple CPU's to execute single query, it consumes lot of CPU's as a result increasing CPU utilization. The same query may take different intervals at different time. As a result, query time is non-deterministic.

I hope, now you might have got some basic idea as to what Maximum Degree of Parallelism is. Coming back to SharePoint, in SharePoint 2010, setting Maximum Degree of Parallelism option was recommended to be set as 1 , but is was optional and not mandatory.
However, in case of SharePoint 2013, it is required to set this to 1 otherwise the configuration wizard will fail.

Below are the detailed steps :-

  1. Check in SQL Sever, whether the Configuration Database exists. If it exists then delete it.
  2. From SQL Server Management Studio, right click on your server and click Properties.
  3. Under Advanced group, modify the value of Maximum Degree of Parallelism to 1.

   4. Run the configuration wizard again.

Now, you will successfully be able to run the wizard.

1 comment: