Best Practice: SQL Server settings before deploying SharePoint Server 2016

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. SharePoint Server configures the required settings during provisioning and upgrade. Manually enabling auto-create statistics on a SharePoint database can significantly change the execution plan of a query. We recommend updating the SharePoint content database statistics daily using the FULLSCAN option from SQL Server. SharePoint products have a timer job to update statistics by calling proc_updatestatistics.

References:

(Important)Set Max Degree of parallelism

Set max degree of parallelism (MAXDOP) to 1 for instances of SQL Server that host SharePoint databases. SharePoint 2016 Installation will fail without this setting, the warning message given below:

SharePoint Server 2016 - Configuration Failed

To apply settings:

  • Launch SQL Server management studio.
  • Right Click on Server name and select Properties from context menu.
  • Select Advanced on Server Settings window to bring up Parallelism settings.

SQL Server 2016 - Properties

  • Select Advanced on Server Settings window to bring up Parallelism settings.

SQL Server 2016 - Server Properties

The following two tabs change content below.
Adnan Amin
Adnan is two time SharePoint MVP (Most Valuable Professional) with over 12 years of extensive experience with major expertise on SharePoint Server, Office 365, .Net Platform and Microsoft BI. He is currently working SharePoint Architect at FMT Consultants. He is MCT Regional Lead for Pakistan Chapter since 2012. He is working on SharePoint for past nine years and worked on different intranet/intranet solutions for private & govt. sector majorly in Gulf region, which include OOB and customized solutions. He is a trainer, technology evangelist and also speaks in community forums.

Leave a Reply

Your email address will not be published. Required fields are marked *