Incremental Statistics and Trace Flag 2371 in SQL 2014

  • Article

Over the last few days I've been asked a few times if trace flag 2371 works in SQL2014 with Incremental Statistics.  I wasn’t too sure so I decided to check test it and see for myself.

What is Trace Flag 2371?

TF 2371 alters the behavior of auto update statistics so that it triggers at a lower percentage threshold.  In a “normal” environment statistics auto update under the following conditions

  • The table size has gone from 0 to >0 rows
  • The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr (hidden system column) of the leading column of the statistics object has changed by more than 500 since then
  • The table has more than 500 rows when the statistics when the statistics were gathered, and the colmodctr (hidden system column) of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered

 

Pretty standard stuff right?  Well consider a table with millions of rows, it will take a considerable amount of row modifications to trigger the update statistics.  Not a cool situation to be in!

In environments where you have these huge tables, enabling trace flag 2371 could be a viable option to help get those all important statistics updated more frequently.  Check out this blog post which has a nice graph to help you visualize the percentage threshold triggers under this trace flag.

 

What are Incremental Statistics?

These are statistics built on top of a partitioned table.  This enables you to maintain these stats at a partition level instead of a table level.  This has a significant advantage in that instead of having to scan the entire table you can scan at only the partition level.  This can significantly reduce the duration of your maintenance operations by updating only the statistics on the partitions which need updating. Check out this blog post for one such example!

 

One limitation of SQL2014 and Incremental statistics is that there is no way to accurately determine when to update statistics based on modifications at a partition level.  This is where the question comes in around “for a huge table, we could ensure auto update stats is enabled and enable trace flag 2371.  Would this work?”.

 

Note:  The following TSQL Scripts are NOT supported and I do not recommend using it in anyway to monitor the row counts and modifications to your partitioning implementation.

For my basic test I have a table with 5 partitions and 10,000 rows in each partition.  Here’s how it looks.

image

 

I’ve updated the statistics on all partitions so that we have the same update time on all partitions:

image

 

As I have 10,000 rows in each table, I would expect approximately 2501 rows to trigger the update stats:

image

 

And the same update again but for a different partition:

image

 

And now if I enable trace flag 2371 and lower the rows in the update, we can see the auto update trigger:

image

 

As you can see, if I lower the row count to 1500 rows, this now triggers the auto update of statistics as opposed to 2501 rows.

I’ve used the sys.dm_db_stats_properties_internal DMV just to show the updated timestamp when updating statistics.  I wouldn’t recommend using this DMV in production code to make decisions when to update stats as it’s an unsupported DMV and as always, testing and monitoring of these large tables is recommended before deciding to enable this trace flag.

Comments

  • Anonymous
    November 01, 2016
    Good article. Thank you.