Last year I started to get involved in the SharePoint community, particularly the SharePoint Saturday events. As a SQL Server DBA, I thought that SharePoint administrators need to have a fair understanding of the underlying database because it is the backbone of a highly-performing SharePoint farm. That gave me the idea of doing a presentation on database configuration for maximum SharePoint performance. I first did the presentation at a SharePoint Saturday event in Ottawa and was very well received (I blogged about it here). Because of that, I got invited to deliver the same presentation for another SharePoint Saturday event. This time, it’s in Montreal. I made the slides available on SlideShare for reference.

One of the things I mentioned in the presentation is the need for a dedicated SQL Server instance just for the SharePoint farm. There are two main reasons why that is the case. First, SharePoint requires a specific collation for the content databases. Microsoft KB 2008668 mentions the collation required for the content databases and the recommended one for the instance (while the KB article mentions SharePoint 2007, it still applies to SharePoint 2010 and 2013.) If your databases require a collation other than those, the collation difference will cause a lot of issues. Hence, the recommendation for a dedicated SQL Server instance.

Another reason for having a dedicated SQL Server instance for the SharePoint farm is the instance-wide maximum degree of paralellism configuration. This is clearly documented in this Microsoft TechNet article. Now, if you’re a SQL Server DBA, you might be laughing at this because it is not a best practice that we apply to our SQL Server instances. We usually analyze the workload and decide whether to keep the default setting of zero or tweak it to a number that would address the workload. However, SharePoint requires a maximum degree of parallelism of 1. Yes, that wasn’t a typo. This is one of the settings that I check whenever performing a best practices configuration check for a SQL Server instance running SharePoint databases. In the past, SharePoint will just install and create content databases even when the instance-wide maximum degree of parallelism value is not equal to 1. With SharePoint 2013, this is no longer the case. In fact, in the earlier builds of SharePoint 2013, the installation media will inform you that the maximum degree of parallelism is not set to 1 and will halt the installation with the error message below.

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

As I was preparing a new SharePoint 2013 farm for my test environment, I decided to play around with it. I intentionally switched my SQL Server instance’s maximum degree of parallelism from 1 to 0 just so I could hit the error. For the installation, I used the SharePoint 2013 build 15.0.4420.1017 released back in 24-Oct-2012. For starters, I used the SharePoint configuration wizard to create the farm and made sure that my maximum degree of parallelism configuration is set to 0. To my surprise, the installation went well. I decided to check the maximum degree of parallelism configuration after the installation and was shocked to see that it was changed to a value of 1. Not satisfied with what I found out, I recreated the SharePoint 2013 farm and decided to run SQL Profiler to capture the SQL statements being sent to the SQL Server instance while the SharePoint farm is initially created. This is what I found.

--DELETE_SQL_AZURE_START
IF (SELECT value FROM sys.configurations WHERE name = 'max degree of parallelism') <> 1
BEGIN
 exec sp_configure 'show advanced options', 1;
 RECONFIGURE WITH OVERRIDE;
 exec sp_configure 'max degree of parallelism', 1;
 RECONFIGURE WITH OVERRIDE;
END
--DELETE_SQL_AZURE_END
IF EXISTS (SELECT TOP 1 1 FROM sys.types WHERE name='tSystemID')
 DROP TYPE [dbo].[tSystemID]

Part of the SharePoint 2013 farm build process is to check whether or not the max degree of parallelism configuration value is equal to 1. This is a good thing. This means that if this configuration setting was ignored during the installation of your SQL Server instance, SharePoint 2013 will reconfigure it for you. But I didn’t stop there. I decided to switch it back from 1 to 0 before creating a new site collection. The site collection will create a corresponding content database, if one doesn’t already exist. I fired up SQL Profiler again to capture the SQL statements getting executed against the SQL Server instance. The same statements are being executed prior to creating the content database for the site collection. So, even if somebody accidentally changed the configuration setting from 1 to something else, SharePoint will automatically change it to 1 whenever a new content database is created.

While this is a good change in SharePoint 2013, I strongly recommend regularly checking your SQL Server instances for configuration changes that do not follow best practices, particularly those that are well documented. Even better is configuring your monitoring tool to notify you when this configuration setting is changed. A proactive approach to managing SQL Server instances will make sure that you minimize issues that you might come across with, whether they be performance-, reliability-, or security-related.