Note for SQL Server 2016 or later:
- For databases with compatibility level 130: You no longer have to enable trace flag 2371 in SQL Server 2016 or later 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.
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:
- If you have not observed performance issues due to outdated statistics, there is no need to enable this trace flag.
- 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.
- 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:
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 Software||Rule title||Rule description||Product versions against which the rule is evaluated|
|System Center Advisor||SQL 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.