Hi Friends,

This is my 29th blog on SQL Server Trace Flag 2371 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

There are lots of blogs about SQL Server statistics by many SQL Server professionals. We all know that auto update of statistics triggered based on standard threshold values. These are mention below:

  • For temp tables auto update will be trigger for every six modifications
  • For a table, having cardinality greater than 6 and less than or equals to 500 then auto update will be trigger for every 500 modifications.
  • For a table, having cardinality greater than 500, auto update will be triggered for (500 + 20% of the table) modifications.

If everything is running good then no issue i.e. no performance issue then no need to worry. Now just think about these thresholds in the situation when your table cardinality is too high like 50000 rows or 1000000. In such cases, for high cardinality tables these thresholds may not be good enough. Let say, I have a table with cardinality 50000. We have inserted 20% (10000) new rows. As per the above standard threshold, these 20% newly inserted rows are not sufficient to trigger the auto update for statistics. This may be one of the reasons of performance issue in your case. If you are facing the same kind of issue then no need to worry because Microsoft provide us a trace flag 2371. By using this trace flag; SQL server will decide dynamic threshold value for auto update of statistics on the tables with more than 25000 rows. Higher the number of rows (cardinality) will use lower the threshold value for auto update of statistics.

Let me show you this with TSQL practically. I am going to create a table with 50000 rows.

Now run the below code, here select statement will create a statistic on id column.

Now check the auto created statistic on id column from SSMS.


Run the below command to check the details of this newly created statistic.


As per the standard threshold for update stats, stats will be updated only after (20% of 50000 + 500) 10500 modifications. We are using trace flag 2371 here to change this threshold. Now try to delete 8000 rows from the table and then check the stats.


Excellent! From the above output you can see that statistics have been updated for below standard threshold value. If you are using this trace flag 2371 then threshold value to auto update stats will be decreases as the cardinality of that table grows. Here, I have shown to you only a single situation on a table with 50000 rows. You can try this with many other situations.

Keep in mind that this trace flag introduced from SQL Server 2008 R2 Service Pack 1 onwards. If you ever used this trace flag in your environment, then we will be more than happy to read your comments.

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.


Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook