I found, that since SQL Server 2008 R2 there is a trace flag 2317 available. It turns on dynamical autoupdate statistics threshold, instead of default threshold of 20%. So, it greatly improves performance on systems with high tables (from 1 billion rows), because it could take a long time to reach 20% of rows modified. More of this here: http://support.microsoft.com/kb/2754171
Secondly, I found few unofficial topics, which says, that this trace flag is turned on by default since SQL Server 2012. But I can’t find any proof of this from vendor, Microsoft.
So, the question is: does anybody know anything about any changes on autoupdate stats default behavior since SQL Server 2012? Any link to BOL or MSDN would be appreciated.
Best Answer
I have checked on sql 2012 RTM and SP1, this trace flag
TF 2317
is not enabled by default.I have not seen this Trace Flag used widely, except on SQL Server running SAP. Also, if you are facing issues due to AUTO_UPDATE_STATISTICS then only you should consider enabling this trace flag.
My suggestion is that, monitor your statistics using
sys.dm_db_stats_properties
DMV to check the last time the statistics were updated, the number of rows in the table when the stats were updated along with the sampled rows. Its an interesting DMF to play around to better understand the statistics.From Changes to automatic update statistics in SQL Server – traceflag 2371
The only downside will be that updating stats will result in query recompilations occurring more frequently that are accessing the tables and hence a different plan is generated which might be good or bad.