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, a distinguished professional, boasts an impressive track record as a Microsoft MVP, having achieved this prestigious recognition for the eighth consecutive year since 2015. With an extensive career spanning over 18 years, Adnan has honed his expertise in various domains, notably excelling in SharePoint, Microsoft 365, Microsoft Teams, the .Net Platform, and Microsoft BI. Presently, he holds the esteemed position of Senior Microsoft Consultant at Olive + Goose. Notably, Adnan served as the MCT Regional Lead for the Pakistan Chapter from 2012 to 2017, showcasing his leadership and commitment to fostering growth within the tech community. His journey in the realm of SharePoint spans 14 years, during which he has undertaken diverse projects involving both intranet and internet solutions for both private and government sectors. His impact has transcended geographical boundaries, leaving a mark on projects in the United States and the Gulf region, often collaborating with Fortune 500 companies. Beyond his roles, Adnan is a dedicated educator, sharing his insights and knowledge as a trainer. He also passionately advocates for technology, frequently engaging with the community through speaking engagements in various forums. His multifaceted contributions exemplify his dedication to the tech field and his role in driving its evolution.

Leave a Reply