What triggers the update of statistics in my SQL 2008 database, when are the statistics out of date ?

Microsoft SQL Server 2008 collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. By default, SQL Server 2008 also creates and updates statistics automatically, when such an operation is considered to be useful. Sometimes it can happen that your statistics are not representative for your current data distribution which can result in a not efficient query plan. SQL Server 2008 determines whether to update statistics based on changes to column modification counters (colmodctrs).


If the statistics is defined on a regular table, it is out of date if:

• The table size has gone from 0 to >0 rows.
• The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then.
• 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.
• For filtered statistics, the colmodctr is first adjusted by the selectivity of the filter before these conditions are tested. For example, for filtered statistics with predicate selecting 50% of the rows, the colmodctr is multiplied by 0.5.

One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.

More detailed information about database statistics can be found in this whitepaper: Statistics used by the query optimizer in Microsoft SQL Server 2008.