The default option is ON.
How do the statistics increase the performance of your SQL Server query execution? The statistical histograms are used by the Query Optimizer to choose the optimal query execution plan. If a query predicate contains a column with statistics, the Query Optimizer does not have to estimate the number of rows affected by that query, thus the Query Optimizer has enough information to create the execution plan. The SQL Server creates statistics in different ways:
- The statistics are automatically created for each new index.
- If the database setting AUTO_CREATE_STATISTICS is on, then the SQL Server will automatically create statistics for non-indexed columns that are used in your queries.
When you set the AUTO_CREATE_STATISTICS option on, the Query Optimizer creates statistics on individual columns used in a predicate, if these statistics are not already available. These statistics are necessary to generate the query plan. They are created on columns that do not have a histogram in an existing statistics object. The name of the auto-created statistics includes the column name and the object ID in hexadecimal format: _WA_Sys_<column_name>_<XXXX>. These statistics are used by the Query Optimizer to determine the optimal Query Execution Plan.
Statistics are checked before query compilation or before executing a cached query plan.
The outdated statistics can cause a lot of performance issues therefore it is recommended to enable it. The default option is ON. The usual symptoms of non-updated statistics are suboptimal query plans and degraded performance. Sometimes it is even worse to have outdated statistics than no statistics at all!
Sources:
Comments