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

Adnan Amin is a Microsoft MVP for Microsoft 365 (since 2015) and a Senior Microsoft Consultant. He writes practical guides and PowerShell scripts for SharePoint, OneDrive, Teams and Copilot admins.

Leave a Reply

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