Article ID: 2754171 - View products that this article applies to.
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.
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:
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:
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
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2754171 - Last Review: October 1, 2012 - Revision: 1.0
Contact us for more help
Connect with Answer Desk for expert help.