Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors
09/12/2015 Leave a comment
After many performance issue investigations, we have released at 10th of October 2015 following kb article for about “Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors”
In this article scope we make availability and some flexiblity for database maintenance operations about preventing “outdated update statistics” for DBAs , and now you are not depending just only SharePoint Daily Timer job which responsible update database statistics by using the proc_updatestatistics SQL procedure anymore.
Our TechNet article “Best practices for SQL Server in a SharePoint Server farm” has now been updated with the same guidance and cross referencing the new KB article.
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. Although SharePoint does have a timer job to update statistics by calling proc_updatestatistics, we strongly recommend implementing a scheduled maintenance plan from SQL Server to ensure database statistics are reliably updated on a daily basis. For more information, see Outdated database statistics.
Best practices for SQL Server in a SharePoint Server farm
Now ; to prevent potential service outages, SQL Server maintenance plans can be implemented to keep SharePoint content database statistics updated by using the FULLSCAN option and it can be done manually by DBAs
When implementing the SQL Server maintenance plan to update the statistics on your SharePoint databases, it is not required to disable the job from SharePoint. However, because these maintenance tasks perform similar functions from both locations, it is permissible to disable the timer job from the SharePoint farm.