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:
- Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors
- Best practices for SQL Server in a SharePoint Server farm
(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:
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.
- Select Advanced on Server Settings window to bring up Parallelism settings.
No Comments