Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 363

CREATE STATISTICS
If you are confident that you know all the potential queries against your database, you can disable
AUTO_CREATE_STATISTICS and CREATE STATISTICS manually on one or more columns that don't
already have an index. You just have to specify a name for the statistics, the columns you want them
on, and the sample size you require. Databases that have frequent data loads can have AUTO_CRE-
ATE_STATISTICS disabled to prevent automatically created statistics from having to be maintained. It's
an unusual scenario though, so for most systems you work on having AUTO_CREATE_STATISTICS
enabled is by far the best option.

UpdatingStatistics
As you change the data in your table, the statistics that were originally created may not accurately repre-
sent the distribution of your data, leading to the optimizer making poor choices. There are three ways to
keep your statistics up-to-date:

AUTO_UPDATE_STATISTICS
This is a database option that, when enabled, triggers the optimizer to automatically update statistics
when a test for current statistics fails. The effect of this in most cases is that statistics are automatically
updated after 20 percent of the rows have been changed unless it's a small table ( < 500 rows) where auto
update won't be triggered unless nearly all the rows change. Most of the time you don't need to worry
about updating your statistics because this option is enabled by default.

AUTO_UPDATE_STATISTICS_ASYNC
When a test for current statistics fails, the query that prompted the check has to wait for the statistics
to be updated. This can cause unpredictable response times in environments with frequently changing
data, which is a problem for companies that favor predictability over efficiency. One of the authors of
this topic worked with an investment bank a few years ago on their trading platform, which ran on SQL
Server 2000. They experienced this exact problem and had to disable automatic updating entirely because
the implication of a query waiting for a statistics update meant that the stock trade price it provided was
too old.

This database option is new to SQL Server 2005 and is more commonly referred as Asynchronous
Statistics Update. It allows queries that cause a statistics update to continue compiling using the old
statistics while the update is queued in a background process, thus providing more predictable perfor-
mance. The risk of course is that the optimizer might choose a less efficient execution plan.

Disabling Automatic Update Statistics
There are a number of ways to disable automatic updating of statistics that provide more granularity
than disabling it entirely at the database level:

❑

sp_autostats can be used to disable or enable it for individual indexes or tables.

❑ You can specify STATISTICS_NORECOMPUTE when you create an index with CREATE

INDEX.

❑ You can specify NORECOMPUTE when creating or updating statistics with CREATE or

UPDATE STATISTICS.