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.
Background
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) .
Recommendation:
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:
Service_BusinessConnectivity
Managed Metadata Service
SessionState
StateService_
Application_Registry_Service_DB
PerformancePoint Service Application
Profile Service Application_SyncDB
Bdc_Service_DB
Secure_Store_Service_DB
ReportServer
ReportServerTempDB
If using FAST Search:
FAST_Content_SSA_DB
FAST_Content_SSA_PropertyStoreDB
FAST_Query_SSA_DB
FAST_Query_SSA_PropertyStoreDB
FASTSearchAdminDatabase
If using SharePoint Search:
Search_Service_Application_DB
Search_Service_Application_PropertyStoreDB
If using Project Server 2010:
ProjectServer
ProjectServer_Draft
ProjectServer_DW
ProjectServer_Published
ProjectServer_Reporting
PPCentralConfig
· 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).
FOR 2013,
· 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