Note – this blog post has a title ‘best practices’. I named it so to match the referred TechNet article entitled “Best practices for SQL Server in a SharePoint Server farm”. So please forgive me for using the term ‘best practices’. Probably ‘guidelines’ or ‘good practices’ is a better name for this article.
Getting back to the topic: Some times you need a refresher when it comes to guidelines in setting up SQL for SharePoint – especially when setting up the newer SharePoint 2013.
There is a Technet article I would like to refer to here which specifically refers to ‘best practices’ for SQL server in a SharePoint 2013 environment. There are a few changes to note when it comes to SharePoint 2013.
A couple of important things to note:
Do not enable auto-create statistics on a server that hosts SQL Server and SharePoint Server. Enabling auto-create statistics is not supported for SharePoint Server.
Set the MAXDOP (max degree of parallelism) setting to 1 and nothing else. Setting the max degree of parallelism to any other number can cause a less optimal query plan to be used that will decrease SharePoint Server 2013 performance.
There is also some guidance on setting up the SQL instance for better performance and managing the SharePoint databases.
Here’s the article for further reading: