Auto Update Statistics when does it occur

I was recently asked when does the actual Auto Update Stats trigger and while I know its during query compile I wasn’t exactly sure what the thresholds were so I did some RnD, the results are anyway well documented in the link in the references but I have still posted the results here because I went through the trouble of RnD before googling.

The Second thing to keep in mind is that even then the statistics still won’t be created unless a query is executed and actual rows are inserted into the table.

The Third thing to keep in mind is you can find out the time the Statistics were last updated by either this query

SELECT *
FROM sys.dm_db_stats_properties(object_id, stat_id) available IN 2012 SP1
    AND Above

OR

SELECT name AS stats_name
    ,STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats
WHERE object_id = OBJECT_ID('Person.Address');

OR

Statistics properties

OR

DBCC SHOW_STATISTICS

The Fourth thing to keep in mind is that Stats get updated when the table structure changes or roughly around 500 rows are inserted into the table , the width of the row has no role in stats update.

The Fifth thing to remember is that statistics are updated Asynchronously when the below option is set

This means that the current query will continue to execute with stale statistics but another thread is launched which will in parallel update the stats so that the next query gets the benefit.

If you create an index on a single column it is good to drop the old auto created statistics since the two might be out of sync and there is no guarantee which one SQL optimizer will use.

Added after linked in chat mentioned below :-The larger the table the longer it will take for Auto stats to update since futuer updates are governed by the threshold of 100+ 20% of row count. Having said that, this logic doesn’t apply to filtered stats where the selectivity of the column dictates the threshold.

Also , it might be worth while to lookup trace flag 2371 in SQL server 2008 R2 SP1 .

Reference

http://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/

A very good chat we had on linkedin is mentioned below

https://www.linkedin.com/grp/post/66097-6017174523202330628#commentID_discussion%3A6017174523202330628%3Agroup%3A66097

Please Consider Subscribing

Subscribe