SharePoint and MAXDOP

Microsoft strongly recommends that the SQL Server MAXDOP (maximum degree of parallelism) setting be set to “1” for servers supporting SharePoint. This setting suppresses parallel plan generation, and until SQL 2016, it’s an instance level setting, which means that all databases on that SQL instance would be affected by the change. Since most databases benefit from parallel plan generation, this setting will result in worse performance for most databases, except SharePoint, where this setting is strongly recommended. Though, when Microsoft says “recommended”, they really mean “required”, as SharePoint will not install or create new databases when the SQL Server does not have a MAXDOP of 1, starting in 2013. This is an easy requirement to miss, for a few reasons. First, this requirement is not mentioned in the documentation. Second, the SharePoint installer will set MAXDOP to 1 automatically if the install account has sysadmin permissions on the SQL Server. Since many shops will follow the advice to have a SQL instance that is dedicated to SharePoint, it’s rarely an issue as the setting applies automatically and no other database is affected as SharePoint is the only application using that SQL Server instance.

However, if you have a company that decided to have one huge database server that runs as many databases as possible, then this is a significant issue. Changing the server so that all databases run without parallel execution plans is a no-go. But installing SharePoint 2013 or 2016 without this option is also a problem. The solution is to follow best practices and set up a new SQL instance to support the SharePoint installation, but it’s not always easy to convince the IT group they need to diverge from their established plan of hosting as many databases as possible on one server and to spin up a new SQL instance in order to support SharePoint.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s