Sunday, December 22, 2024
Latest:

SQLChamp

Let's learn together!

LearnSQL

Trace Flag 2371 to Control Auto update Statistics threshold and behaviour in SQL Server

Sarabpreet Singh Anand

Hi Friends,

As you already know, 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 exceeds a threshold.

AutoStat Threshold

The sysindexes.rowmodctr column maintains a running total of all modifications to a table that, over time, can adversely affect the query processor’s decision making process. This counter is updated each time any of the following events occurs:

  • A single row insert is made.
  • A single row delete is made.
  • An update to an indexed column is made.

NOTE: TRUNCATE TABLE does not update rowmodctr.

The basic algorithm for auto update statistics is:

  • If the cardinality for a table is less than six and the table is in the tempdb database, auto update with every six modifications to the table.
  • If the cardinality for a table is greater than 6, but less than or equal to 500, update status every 500 modifications.
  • If the cardinality for a table is greater than 500, update statistics when (500 + 20 percent of the table) changes have occurred.
  • For table variables, cardinality changes do not trigger auto update statistics.

NOTE: In this strictest sense, SQL Server counts cardinality as the number of rows in the table.

NOTE: In addition to cardinality, the selectivity of the predicate also affects AutoStats generation. This means that statistics may not be updated after every 500 modifications if cardinality were < 500 or for every 20% of changes if cardinality were > 500.

Problem with VLDBs \ Large Tables

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 behaviour.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
  3. If you have to rely on nightly job to update statistics because current automatic update is not triggered frequently enough, consider enabling SQL Server 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.

DMV sys.dm_db_stats_properties (Transact-SQL)Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database.

The DMV takes two parameters Object_ID and Stats_ID, where both the values are int. Syntax: sys.dm_db_stats_properties (object_id, stats_id)

object_id: Is the ID of the object in the current database for which properties of one of its statistics is requested.

stats_id:   Is the ID of statistics for the specified object_id. The statistics ID can be obtained from the sys.stats dynamic management view.

The DMV will return number of columns; the columns which require our attention are last_updated&modification_Counter.

  1. last_updated will tell us when was the last time the stats for this given table\index was updated.
  2. Modification_Counter Column will reflect the Total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated.

System Center Advisor checks for statistics information using this DMV and raises an alert if the column modification counter is more than 1000.

Hope this new Trace Flag and DMV might help you to resolve the performance issues related to obsolete stats.

Do let me know your take on this – just leave a comment.

Enjoy!

Regards

Sarabpreet Anand