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.
Adnan is six time Microsoft MVP (Since 2015) with over 16 years of extensive experience with major expertise on SharePoint, SharePoint based development, Microsoft 365, Microsoft Teams, .Net Platform and Microsoft BI. He is currently working Sr Microsoft Consultant at Olive + Goose. He is MCT Regional Lead for Pakistan Chapter since 2012. He is working on SharePoint for past 12 years and worked on different intranet/intranet solutions for private & govt. sector majorly in United states and Gulf region and have experience of working with multiple Fortune 500 companies. He is a trainer, technology evangelist and also speaks in community forums.
No Comments