Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server

Article ID: 2754171 - View products that this article applies to.
Expand all | Collapse all

Summary

The article documents trace flag 2371 that 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 that you can inspect to decide if you need to manually update statistics for a table.

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.
Collapse this tableExpand this table
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.


Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2754171 - Last Review: October 1, 2012 - Revision: 1.0
Applies to
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Integration Services
  • Microsoft SQL Server 2008 R2 Service Pack 2
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
Keywords: 
KB2754171

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com