SQL Server 2012 autoupdate statistics default behaviour

Question

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.

Answer 1

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

When enabled -- With smaller tables, the threshold will still be around a 20% percentage. It only is when you exceed 25,000 rows per table where the dynamic rule will kick in and where, with an increasing # of rows, the percentage of records changed is becoming lower and lower. E.g. in a table with 100,000 rows, the threshold to trigger update statistics will already be down to 10%. In a table with 1,000,000 rows it only will take changes of around 3.2% to trigger automatic update statistics doing its job.

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.

Answer 2

I was just about to create a table, insert a bunch of data, create some statistics and do a test. But then I remembered this excellent blog post Erin Stellato wrote over on her blog. She's done that already.

Her tests show 20% in action. You can take what she's done her and the scripts provided, test it on your environment and witness the behavior yourself.

I've not experienced, seen or read anything that indicates this traceflag, or its behavior, is on by default in SQL Server 2012. I can't point you to official articles or documentations to prove a negative, though.

You can see for yourself which traceflags are enabled, however, by running

DBCC TRACESTATUS(-1)

Biggest advice here is - Empirical evidence rules the day. Not only will you know for sure, but you'll learn a lot in the process of figuring it out.