Databases Reference
In-Depth Information
FIGURE 5-3
This i gure shows just part of the multi-column output, which is the same output that you get when
running the following DBCC command:
DBCC SHOW_STATISTICS ("Person.Address", PK_Address_AddressID);
The following SQL Server coni guration options control how statistics are created.
Auto_create_statistics
When this is on (default), SQL Server automatically creates statistics when it thinks they would
result in a better plan. That usually means when it is optimizing a query that references a column
without statistics.
Auto_update_statistics
When this is on (default), SQL Server automatically updates statistics when a sufi cient amount of the
data in the relevant columns has changed. By default, this is done synchronously, which means that
a query has to wait for the statistics to be updated before the optimization process can be completed.
Auto_update_statistics_asynchronously
When this option is on, SQL Server updates statistics asynchronously. This means that when it's
trying to optimize a query and the statistics are outdated, it will continue optimizing the current
query using the old stats, and queue the stats to be updated asynchronously. As a result, the cur-
rent query doesn't benei t from the new stats, but it does not have to wait while stats are being
updated before getting a plan and running. Any future queries can then benei t from the new stats.
Plan Caching and Recompilation
Once the Query Optimizer has come up with a plan, which may have taken a considerable amount
of work, SQL Server does its best to ensure that you can leverage all that costly work again. It does
Search WWH ::
Custom Search