SharePoint and auto statistics guidance
On TechNet, at this link, you will find the guidance for auto-create statistics on a server that hosts SQl Server and SharePoint.
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. The SharePoint databases either use a stored procedure that maintains the statistics (proc_UpdateStatistics) or rely on SQL Server to do this.
There seems to be no mention for auto_update_statistics. So I worked with my colleague, Derek Wilson, an SME in SQL Server to find the official stand on the update statistics.
For SharePoint 2010, we do not recommend to enable the configurations (Create and Update). However, if you see performance issues, you can enable these settings. For SharePoint 2010 only, this is supported by content databases as it does not affect the SharePoint functionality.
For SharePoint 2013, enabling this setting is NOT supported.
As far as auto-update, it can be a very helpful feature at times, and other occasions can cause performance issues after it kicks in within a SharePoint environment. If you turn it off then you would have to make sure that either the DBA's or someone on the SharePoint team has a process that manually updates statistics that need to be updated on a fairly regular basis.
While the built-in SharePoint timer job (one of those Health Analyzer rules) will run the stored procedure - proc_UpdateStatistics. This procedure does not exist in every SharePoint database. If the stored procedure is not found in a particular database, then that database will end up with stale statistics - which can lead to inappropriate and inefficient query plans based on the amount of data that is being queried (https://msdn.microsoft.com/en-us/library/ms190397(v=sql.105).aspx) .
For SharePoint 2010
· If you are manually updating statistics, then you would not need to enable the “auto update statistics asynchronously” option along with the auto update statistics option. If you are not manually updating the statistics, then you can enable the “auto update statistics asynchronously” option on non-content databases. This would be an option that could be enabled on the databases that do not have the proc_UpdateStatistics stored procedure. These databases would include the following (this is not an all-inclusive list):
Application Service databases:
Managed Metadata Service
PerformancePoint Service Application
Profile Service Application_SyncDB
If using FAST Search:
If using SharePoint Search:
If using Project Server 2010:
· Databases that do not have the proc_UpdateStatistics, have a process in place to manually update the statistics on the databases that are not covered by the Health Analyzer rule. This process can include the setup and configuration of a SQL Server Maintenance plan (https://technet.microsoft.com/en-us/library/cc262731%28v=office.14%29.aspx#DBMaintenanceForSPS2010_CreateSQLS2008MaintPlan).
· Respect SharePoint configuration settings and do not turn it on or off.
· Databases that do not have the proc_UpdateStatistics, have a process in place to manually update the statistics on the databases that are not covered by the Health Analyzer rule
Here is a great video that talks about optimizing SQL Server for SharePoint: https://channel9.msdn.com/Events/TechEd/NewZealand/2013/SES312