512
Part V: Enterprise Data Management
Auto Update Statistics
SQL Server’s cost-based query optimizer uses statistics to choose the most effi cient plan for
retrieving or updating data. Hence, out-of-date data-distribution statistics aren’t very use-
ful. The AUTO_UPDATE_STATISTICS database option causes statistics to be recomputed
every time a certain number of rows in the table changes. The default for this option is set
to on, which is best practice and works for most environments. Based on the row changes,
sometimes the statistics may be updated too frequently, other times too infrequently, and
sometimes automatically updating the statistics may cause a delay just when you don’t
want it. To avoid these situations, you can disable the AUTO_UPDATE_STATISTICS data-
base option and schedule jobs to recompute statistics during a low traffi c or maintenance
window. In some environments, DBAs schedule jobs to manually compute the statistics
and keep the AUTO_UPDATE_STATISTICS option on as a failsafe measure in case many
more rows change than they normally do. To set the auto update statistics option on for
AdventureWorks2012 sample database in code, do the following:ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS ON;Auto Update Statistics Asynchronously
When a query triggers an AUTO_UPDATE_STATISTICS event, the query waits until the
updated statistics can be used. This can cause unpredictable query response times. Starting
from SQL Server 2005, there is a database option called AUTO_UPDATE_STATISTICS_
ASYNC that you can enable to asynchronously update the statistics. By default, this option
is off. If the AUTO_UPDATE_STATISTICS_ASYNC database option is turned on, then SQL
Server performs the automatic update of statistics in the background. The query that
causes the automatic statistics does not need to wait for the statistics to be updated and
proceeds with the old statistics. This may result in a less-effi cient query plan, but the
query response times are predictable. Queries that start after the statistics are updated use
those statistics.To set the auto update statistics asynchronous option on for the AdventureWorks2012 sam-
ple database in code, do the following:ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC ON;The AUTO_UPDATE_STATISTICS_ASYNCdatabase option is dependent on the AUTO_UPDATE_
STATISTICS option. Therefore, you need to ensure that the AUTO_UPDATE_STATISTICS option is ON and then
enable the AUTO_UPDATE_STATISTICS_ASYNC option. Like any SQL Server confi guration, you need to thor-
oughly test this option to see if your SQL Server applications benefi t from this option.Query execution plans rely heavily on data-distribution statistics, which is covered in more detail in
Chapter 45, “Indexing Strategies.”c19.indd 512c19.indd 512 7/30/2012 5:43:08 PM7/30/2012 5:43:08 PM
http://www.it-ebooks.info