Database Reference
In-Depth Information
Automatic Update Statistics Asynchronously
Waiting for statistics to be updated before plan generation, which is the default behavior, will be just fine in most
cases. In the rare circumstances where the statistics update or the execution plan recompiles resulting from that
update are expensive (more expensive than the cost of out-of-date statistics), then you can turn on the asynchronous
update of statistics. Just understand that it may mean that procedures that would benefit from more up-to-date
statistics will suffer until the next time they are run. Don't forget—you do need automatic update of statistics enabled
in order to enable the asynchronous updates.
Amount of Sampling to Collect Statistics
It is generally recommended that you use the default sampling rate. This rate is decided by an efficient algorithm
based on the data size and number of modifications. Although the default sampling rate turns out to be best in
most cases, if for a particular query you find that the statistics are not very accurate, then you can manually update
them with
FULLSCAN
. You also have the option of setting a specific sample percentage using the
SAMPLE
number. The
number can be either a percentage or a set number of rows.
If this is required repeatedly, then you can add a SQL Server job to take care of it. For performance reasons,
ensure that the SQL job is scheduled to run during off-peak hours. To identify cases in which the default sampling
rate doesn't turn out to be the best, analyze the statistics effectiveness for costly queries while troubleshooting the
database performance. Remember that
FULLSCAN
is expensive, so you should run it only on those tables or indexes
that you've determined will really benefit from it.
Summary
As discussed in this chapter, SQL Server's cost-based optimizer requires accurate statistics on columns used in filter
and join criteria to determine an efficient processing strategy. Statistics on an index key are always created during
the creation of the index, and, by default, SQL Server also keeps the statistics on indexed and nonindexed columns
updated as the data changes. This enables it to determine the best processing strategies applicable to the current data
distribution.
Even though you can disable both the auto create statistics and auto update statistics features, it is
recommended that you leave these features
on,
since their benefit to the optimizer is almost always more than their
overhead cost. For a costly query, analyze the statistics to ensure that the automatic statistics maintenance lives up
to its promise. The best news is that you can rest easy with a little vigilance since automatic statistics do their job
well most of the time. If manual statistics maintenance procedures are used, then you can use SQL Server jobs to
automate these procedures.
Even with proper indexes and statistics in place, a heavily fragmented database will incur an increased data
retrieval cost. In the next chapter, you will see how fragmentation in an index can affect query performance, and you'll
learn how to analyze and resolve fragmentation.
