SQL Server Statistics are small, lightweight objects that describe the distribution of data in a SQL Server table or column. The SQL Server query optimizer uses statistics to estimate how many rows will be returned by parts of your query. This heavily influences the optimizer and has a direct correlation to how well queries will run. The importance of proper statistics maintenance cannot be overstated.
In versions of SQL Server prior to 2016, SQL Server would consider a statistic stale when any of the following has occurred:
- The table has gone from 0 rows to > 0 rows
- The number of rows in a table were < 500 when the statistics were gathered and the modification_counter has grown to more than 500 since the last update of the statistic2371,
- The table has more than 500 rows when the statistics were gathered and the modification counter is > 20% of the row count + 500 since the statistic was gathered.
Trace flag 2371 was first introduced in SQL 2008 R2 SP1 and has been turned on by default in SQL 2016 (with compatibility 130). Using trace flag 2371, SQL will now use a threshold that adjusts dynamically to the number of rows in the table to determine when statistics are stale. With this change, statistics in large tables are updated much more frequently.
The following graph illustrates the difference in thresholds for determining when statistics are stale between older versions of SQL Server and 2016 or previous versions with TF 2371.
This illustrates that a table with 1,000,000 rows will now only need 32,000 modifications before statistics are considered stale. Without the trace flag that same table would require 200,500 modifications before statistics would be considered out of date.
This flag can be turned on and off without restarting SQL Server services by using:
– This change should be tested in a development environment prior to being implemented on a production system.
DBCC TRACEON (2371,-1) DBCC TRACEOFF (2371,-1)
Be sure to add this as a startup parameter in the SQL Server configuration manager so the flag will persist after a services restart. Otherwise, it will not be re-enabled automatically.
Trace flag 2371 is documented in KB2754171. It recommends that this trace flag be enabled if:
- You are running an SAP system. See SAP Blog for more info.
- You are running a nightly job to update statistics because current automatic updates are not triggered frequently enough.
Querying sys.dm_db_stats_properties can help determine if larger tables could benefit from this more aggressive algorithm to update the statistics. A sample query for this table is:
SELECT sp.stats_id ,name ,filter_definition ,last_updated ,rows ,rows_sampled ,steps ,unfiltered_rows ,modification_counter FROM sys.stats AS stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE stat.object_id = object_id('YOUR_TABLENAME_HERE');
While databases with large tables can see an improvement in query execution times by enabling this trace flag, there are some items to be aware of prior to enabling this on your production machine.
- The trace flag is global. This cannot be changed for a specific database, only instance wide.
- Synchronously updating stats can cause a query to wait while stats are updated. For large tables, this could take some time. Consider enabling the auto update stats async option to protect against this.
Even though it does have the potential for an increase in I/O as well as causing delays while statistics are updated on larger tables, the overall query performance should improve. This trace flag has been promoted to default behavior in SQL 2016 and should solve more problems than it creates. By keeping the statistics maintenance jobs at off-peak times, SQL will be less likely to auto update during peak hours. I fully recommend enabling this on your SQL server in addition to any current statistics maintenance jobs that are running.