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:
Comments
Anonymous
January 30, 2013
sweet synergy!Anonymous
January 31, 2013
This is a bit odd. "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" If it's indeed the case that query hints are used, not sure why it is necessary to enforce the server setting at all - surely using hints is better from an architecture POV. Does "this does not appear to be necessary going forward" indicate that the hints are to be removed?Anonymous
January 31, 2013
Hi SQLSophist, I can't confirm that the query hints that were used will ever be removed, or that future queries that are written would omit their inclusion. Information regarding why that decision was made is not available to me. What I can confirm is that SharePoint performs a check against the instance every time you try to create a SharePoint database and blocks that type of action if your MAXDOP hasn't been set to 1.Anonymous
March 11, 2013
Why don't you raise cost of threshold and use half of your number of processors on DOP? That will work best for many concurrent queries.Anonymous
September 10, 2013
Weird restriction. On one hand they tell us the query optimiser is brilliant and not to change MaxDOP unless you have issues. Then in SharePoint they tell you to set it so you don't get issues. In a world with 40core plus servers does this really apply (although I usually set maxdop around 8 on these), I can imagine it helping on a dual core VM !Anonymous
January 05, 2014
Is this a recommend “Best Practice” setting for query performance or creating SharePoint databases? Would a MaxDOP of 1 still be recommended when you have 24 CORE’s and all SharePoint Content Databases are created within SQL Server not within SharePoint?Anonymous
January 05, 2014
For SharePoint 2013, this setting is non-negotiable, but yes, this would nonetheless be a best practice regardless of the number of cores, and regardless of how the databases were created. Due to the application architecture of current SharePoint releases, what you want is to have the maximum number of concurrent queries, and not necessarily the most horsepower for each query that's executing.Anonymous
January 21, 2014
SQL Server looks at server utilization as part of the process for determining whether to parallel a query. Even when the server is at a relatively low utilization, the plan may be changed to not use parallel query and that can result in sub-second queries suddenly taking a day to run (my experience, not specifically SP related queries). The old trick of adding where 1=1 to the query (which litters SP queries BTW along with NOLOCK) is a bad solution. If indeed their queries cannot be performant except under some very specific circumstances that they cannnot control, then I can understand how it would be better to disable parallelism.Anonymous
January 30, 2014
Thanks you!Anonymous
May 28, 2014
I faced same issue where my SharePoint Foundation 2013 failed to installed and gave error. Since we have other applications on our SQL server instance, our DBA is not willing to change setting of MAXDOP from 0 to 1 and we don't have budget for another SQL Server license. My question to you is Can I asked DBA to change setting of MAXDOP to 1 just for SharePoint install and then can reverse to 0 post install. Will it work or create any problems?Anonymous
November 16, 2015
Good question by Vijay. Could the DBA not just create another SQL instance? (I'm not that familiar on licensing) I guess technically you could but AFAIK you need to restart the instance (if not server) for the setting to take effect. Not really that feasible when all your wanting is a quick test site collection on a new content database in the middle of the day.