This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view.
The query optimizer of Microsoft SQL Server is a cost-based optimizer.
One of the most important of is the information about the number of rows that are expected to be returned when the query is executed. This information is gleaned from what is called Statistics.
If statistics are not accurate enough, the query optimizer of Microsoft SQL Server may choose an inappropriate execution plan because of overestimates the number of records.
When AUTO_CREATE_STATISTICS is on, query optimizer creates statistics for single columns and also query optimizer creates statistics for indexes on tables or views when the index is created.
If an error or other event prevents the query optimizer from creating statistics, the query optimizer creates the query plan without using statistics. The query optimizer marks the statistics as missing and attempts to regenerate the statistics the next time the query is executed.
Statistics updates can be either synchronous (the default) or asynchronous:
With synchronous statistics updates, queries always compile and execute with up-to-date statistics.
When statistics are out-of-date, the query optimizer waits for updated statistics before compiling and executing the query.
With asynchronous statistics updates, queries compile with existing statistics even if the existing statistics are out-of-date.
Your query response times might be more predictable with asynchronous statistics updates than with synchronous statistics updates because the query optimizer can execute incoming queries without waiting for up-to-date statistics.
The query optimizer determines when statistics might be out-of-date and then updates them when they are needed for a query plan.
In some cases you can improve the query plan by updating Statistics manually using sp_updatestats.
You can also set AUTO_UPDATE_STATISTICS is on.
