You Shall Configure your MAXDOP When Using SharePoint 2013
In this post, I'm going to talk about why and how to configure your Maximum Degree of Parallelism on your SQL database instance. The reason I'm explaining this now is because of something I ran into while setting up my first SharePoint Server 2013 RTM farm tonight.
Background
Microsoft has been telling customers for years to set their Maximum Degree of Parallelism (MAXDOP) to 1 for SQL instances which host SharePoint database. For the most part, this guidance has been widely ignored with very little consequence, with the exception perhaps of sub-optimal performance.
For those of you who are unfamiliar with what this setting actually does, you can refer to the following documents:
max degree of parallelism Option
Recommendations and Guidelines for 'max degree of parallelism' configuration option
The second link actually does a pretty good job of explaining in detail what this option does – and here's the short summary: The SQL Server configuration option 'max degree of parallelism' controls the number of processors used for the execution of a query with a parallel plan.
A higher MAXDOP value would typically be used when you have a very small number of concurrently executing queries relative to the number of processors. A lower MAXDOP value would typically be used when you have a very large number of concurrently executing queries relative to the number of processors. The default value of '0' will effectively limit the MAXDOP value to the number of processors.
SharePoint is a system in which there will be a very high number of concurrently executing queries; therefore this system favors a lower MAXDOP value. As indicated in the following documents, the optimal MAXDOP value for SharePoint environments is 1.
Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)
Enterprise intranet collaboration environment technical case study (SharePoint Server 2010)
Historically, Microsoft has been making this recommendation, and adding query hints in order to limit MAXDOP for each query to 1 in order to limit the impact of setting MAXDOP to any value other than 1, or leaving it at 0. This does not appear to be necessary going forward, and I'll do my best to explain why.
What Happens If I Haven't Set MAXDOP to 1?
The answer here is pretty simple. If your MAXDOP value has not been set to 1 on the SQL server instance on which you're attempting to host your databases, you will not be able to create databases on the instance. The error message you will encounter is the following:
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 or the current account does not have permissions to change the setting. See documentation for details on manually changing the setting
Also seen in this screenshot:
How Do I Work Around This Problem?
There are basically two ways to remediate this issue. The first one (not recommended) is to grant the account running the PowerShell cmdlets, or executing the Configuration Wizard, sysadmin access to your SQL instance. The second, more desirable approach, is to set the MAXDOP value to 1 in the SQL instance.
The MAXDOP option is an advanced option. The documentation above does provide the SQL statements to change this option. I'll briefly explain how to do this using SQL Management Studio. My screenshot is from SQL Server 2012, the process will be the same for SQL Server 2008 R2, and SharePoint 2013 does not support older versions of SQL:
- Open SQL Management Studio and connect to the instance which will host SharePoint databases
- Right-click the Instance you wish to manage
- Select 'Properties'
- Select the 'Advanced' Node
- The Second option from the bottom will be 'Maximum Degree of Parallelism'. Change this value to 1
- Click 'OK'
Your value should appear as in the screenshot below:
After having set the MAXDOP value to 1, you should be able to proceed and create your farm and/or additional databases, all the while following at least one best practice.
What Does This Mean In The Enterprise?
This should mean that enterprises will start to be a little more mindful of what kind of applications, if any, are going to share database instances with SharePoint. People in the enterprise need to consider what type of query patterns are going to be used in their applications, and whether or not the performance of these applications will be negatively impacted by having a MAXDOP value of 1. What I anticipate will happen is that we're going to see more SQL instances that are dedicated to hosting SharePoint databases, possibly for multiple farms, and overall I see this as a good move.
Feedback
As always, if you have any questions or feedback, let me know. I'd be interested in finding out what your thoughts are regarding this change in SharePoint 2013
You can also follow me on Twitter:
