Statistics and Cost Estimation - Inside the SQL Server Query Optimizer - page 69

With the default configuration (if AUTO_UPDATE_STATISTICS is on), the Query
Optimizer automatically updates statistics when they are out of date. As noted, the
Query Optimizer does not automatically create multi-column or filtered statistics,
but once they are created, by using any of the methods described earlier, they can be
automatically updated. Alternatively, index rebuild operations and statements like
UPDATE STATISTICS can also be used to update statistics. Both the auto-create and
auto-update default choices will give you good quality statistics most of the time, and
you naturally have the choice to change this configuration, or use some other statements,
if you need more control over the quality of the statistics.
So, statistics may be automatically created (if non-existent) and updated (if out of date)
as necessary during query optimization. If an execution plan for a specific query already
exists in the plan cache and the statistics used by the plan are out of date, then the plan
is discarded, the statistics are updated, and a new plan is created. In a similar way,
updating statistics, either manually or automatically, invalidates any existing execution
plan that used those statistics, and will cause a new optimization the next time the
query is executed.
When it comes to determining the quality of your statistics, a fact to consider is the size
of the sample used to calculate said statistics. The Query Optimizer always uses a sample
of the target table when it creates or updates statistics, and the minimum sample size is
8 MB, or the size of the table if it's smaller than 8 MB. The sample size will increase for
bigger tables, but it may still only be a small percentage of the table.
If needed, you can explicitly request a bigger sample or scan the entire table to have
better quality statistics. Using the CREATE STATISTICS and UPDATE STATISTICS
statements you can specify a sample size or use the WITH FULLSCAN option to scan the
entire table. Doing either of these can be of benefit, especially with data that is not
randomly distributed throughout the table. Scanning the entire table will naturally give
you the most accurate statistics possible. In fact, given that statistics are always
created alongside a new index, and given that this operation scans the entire table
anyway, index statistics are initially created with the equivalent of the WITH
FULLSCAN option. However, if the Query Optimizer needs to automatically update