Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server

Summary

This article documents trace flag 2371, which you can use to control when the query optimizer generates autostats on a table. It also introduces a new DMV sys.dm_db_stats_properties element that you can inspect to decide if you must manually update statistics for a table.

Note for SQL Server 2016:
  • For databases with compatibility level 130: You no longer have to enable trace flag 2371 in SQL Server 2016 because the corresponding behavior is enabled by default.
  • For databases with compatibility level 120 or lower: This KB article (2754171) still applies and you should still enable trace flag 2371 to trigger more frequent stat updates. 
For more information see Default auto statistics update threshold change for SQL Server 2016.

More Information

The auto update statistics feature of SQL Server relies on number of rows changed or updated to determine if statistics update is needed.  The statistics of a table will only be automatically updated if the number of rows changed exceed a threshold.  This threshold is documented in KB article http://support.microsoft.com/kb/195565 .

However, when a table becomes very large, the old threshold (a fixed rate – 20% of rows changed) may be too high and the Autostat process may not be triggered frequently enough. This could lead to potential performance problems. SQL Server 2008 R2 Service Pack 1 and later versions introduce trace flag 2371 that you can enable to change this default behavior. The higher the number of rows in a table, the lower the threshold will become to trigger an update of the statistics. For example, if the trace flag is activated, update statistics will be triggered on a table with 1 billion rows when 1 million changes occur. If the trace flag is not activated, then the same table with 1 billion records would need 200 million changes before an update statistics is triggered.

You can use the following guidance for enabling the new trace flag in your environment:

  1. If you have not observed performance issues due to outdated statistics, there is no need to enable this trace flag.
  2. If you are on SAP systems, enable this trace flag.  Refer to this blog http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx for additional information.
  3. If you have to rely on nightly job to update statistics because current automatic update is not triggered frequently enough, consider enabling trace flag 2371 to reduce the threshold.

sys.dm_db_stats_Properties DMV: Starting SQL Server 2008 R2 SP2 and SQL Server 2012 Service Pack 1, a new DMV sys.dm_db_stats_Properties is introduced to more accurately track the number of rows changed in a table.   You can use this DMV to detect number of rows changed in a table and decide if you wish to update statistics manually. For additional information refer to the following page in SQL Server Books Online: 

sys.dm_db_stats_properties (Transact-SQL)

The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.
Rule SoftwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server has stale statistics which can cause performance issue.System Center Advisor checks for statistics information using the sys.dm_db_stats_Properties DMV and raises an alert if the column modification counter is more than 1000. Review the information in this article and take necessary actions.SQL Server 2008 R2, Service Pack 2 and later versions.

SQL Server 2012 Service Pack 1 and later versions.


Propiedades

Id. de artículo: 2754171 - Última revisión: 23 nov. 2016 - Revisión: 1

Comentarios