Updating and creating indexes unsuccessful
If so, the plan is removed from the cache, and during recompilation of the query, the statistics are updated.The plan also is removed from the cache if any of the statistics it depends on have changed.
In his post, Statistics used in a cached query plan, Fabiano Neves Amorim describes a method to capture statistics information from the plan.The technical article, Statistics Used by the Query Optimizer in Microsoft SQL Server 2008, documents these thresholds.For a regular table, statistics are out of date when:o The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 20% of the number of rows in the table when the statistics were gathered (test 3). Sales Order Detail table has 121317 rows:(121317 * 0.20) 500 = 24764The bulk insert below loads 24775 rows, which should be enough to invalidate statistics. The statistics have not updated, but the modification counter has changed, as expected.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.
Test Sales Order Detail'); This is my go-to DMV for statistics because in addition to including the date that statistics were last updated and row information, I also get a count of modifications since the last statistic update.