Maximum Degree of Parallelism in SQL for SharePoint best Performance

Max Degree of Parallelism  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.

The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel. Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately. This article discusses the general guidelines that you can use to configure the max degree of parallelism option for SQL Server when you use the sp_configuresystem stored procedure.
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.
Max degree of parallelism

Max degree of parallelism

   4. Run the configuration wizard again.

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

Leave a Reply

Your email address will not be published. Required fields are marked *