Auto Update Statistics on SQL Server – Statistics Part 2 – Trace Flag 2371

OK, so we know that for larger tables, SQL Server only automatically updates statistics (from AUTO_UPDATE_STATISTICS) when they go above the 20% range come rain or shine, don’t they?  Well, yes they do, but from SQL Server 2008 R2 Service Pack 1 and later you can have a much more aggressive way of automatically updating statistics on large tables.

By enabling trace flag 2371 it will decrease the 20% incrementally (or decrementally?) for larger tables…Look a this graph…

 

This is more like it.  Notice that it kicks in at about 25,000 rows. The red line revealing the decreasing percentage alogorithm being used.

Here’s the originating Microsoft article…

https://support.microsoft.com/en-us/kb/2754171

and here’s more information about it.

http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

This is much better, methinks.  Improve your large table statistics with this trace flag. If you do enable this you might want to think about enabling Auto Update Statistics Asynchronously

“When True, queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available.

When False, queries that initiate an automatic update of out-of-date statistics, wait until the updated statistics can be used in the query optimization plan.

Setting this option to True has no effect unless Auto Update Statistics is also set to True.”

More on this later…