If I enable auto create statistics in a database, how often are they created or updated?
asked
How often are statistics updated?
Taken from the excellent white paper Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
The optimizer automatically creates single-column statistics as needed as a side effect of optimizing SELECT, INSERT, UPDATE, and DELETE statements when AUTO_CREATE_STATISTICS is enabled, which is the default setting
So in simple terms then, when the you perform a query that references a given column for the first time, if no statistics already exist for that column then they will be created automatically by SQL Server.
Now things get interesting with regard to when statistics are automatically updated by SQL Server as there is a formula that is used to determine this. Again taken from the excellent White Paper already referenced and in particular the section titled "Maintaining Statistics in SQL Server 2005"
SQL Server 2005 determines whether to update statistics based on changes to column modification counters (colmodctrs).
A statistics object is considered out of date in the following cases:
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.
To conclude then, if you had not guessed already, statistics within SQL Server is both a tricky and detailed subject. If you are serious about performance tuning with SQL Server then you need to read Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
As so many things in SQL... it depends.
The statistics are created when a column is referenced in a query and auto create is on. Other statistics are created for the key columns of indexes when they are created.
Statistics are updated when the query optimizer determines they are out of date and updates them when used by a query that benefis from them and auto update statistics is on (the default).
Statistics become out of date through DML operations applied against a table.
The query optimizer uses an algorithm based on data mods, the number of these since the last update, and a threshold based on the number of rows in the table.
Statistics are also updated when indexes are rebuilt. Reorganizing an index would best be followed by a manual update of stats.
