To be clear, if a query plan exists in cache and that plan uses specific statistics, when the query executes SQL Server checks to see if any of the statistics used in the plan are out of date.
If they are, then the automatic update of those statistics occurs.
During the PASS Summit in November I presented a session on Demystifying Database Statistics (if you attended Summit but missed it, you can stream it from here).
During the session I went through a demo that showed the automatic update of a statistic.
However, in SQL Server 2012 we can look at the plan XML and confirm this.
In his post, Statistics used in a cached query plan, Fabiano Neves Amorim describes a method to capture statistics information from the plan.
One thing I want to point out: the for three rows (the modification counter for the statistic increased from 24775 to 24778), the optimizer did not use that statistic in the plan.
If the plan does not use a statistic, the statistic will not automatically update, even if the query modifies columns in said statistic key.The database had the Auto Update Statistics option enabled, and I had added almost 25,000 rows to a table with 121,000 rows.The number of rows added via the bulk import was just over the limit to trigger an auto-update of statistics for the next query, which I showed.If a plan does not exist in cache for a query, then if the optimizer uses a statistics object that is out of date when the plan compiles, SQL Server will automatically update those statistics.Invoking the Automatic Update We have not run any query against except our bulk insert.I had many people ask why statistics did not update immediately after the bulk load of the 25,000 rows completed, so I want to step through the demo that I did at Summit to clarify when an automatic update will occur.