Databases Reference
In-Depth Information
these index statistics, it can only use a default sample, as it may take too long to scan the
entire table again.
By default, SQL Server needs to wait for the update statistics operation to
complete before optimizing and executing the query; that is, statistics are updated
synchronously. A new database configuration option introduced with SQL Server 2005,
AUTO_UPDATE_STATISTICS_ASYNC
, can be used to change this default and let the
statistics be updated asynchronously. As you might have guessed, with asynchronous
statistics update, the Query Optimizer does not wait for the update statistics operation
to complete, and instead just uses the current statistics for the optimization process.
This can help in situations where applications experience timeouts caused by delays
related to the automatic update of statistics. Although the current optimization will use
the out-of-date statistics, they will be updated in the background and will be used by any
later query optimizations.
SQL Server defines when statistics are out of date by using column modification
counters or
colmodctrs
, which count the number of table modifications, and which are
kept for each table column. Basically, for tables bigger than 500 rows, a statistics object
is considered out of date if the
colmodctr
value of the leading column has changed by
more than 500 plus 20% of the number of rows in the table. The same formula is used
by filtered statistics but, since they are built only from a subset of the records of the
table, the
colmodctr
value is first adjusted depending on the selectivity of the filter.
Colmodctrs
are usually not exposed by any SQL Server metadata although they can be
accessed by using a dedicated administrator connection and looking at the
rcmodified
column of the
sys.sysrscols
base system table in SQL Server 2008 (same information
can be found on the
sysrowset
columns
for SQL Server 2005).
The density information on multi-column statistics might improve the quality of execu-
tion plans in the case of correlated columns or statistical correlations between columns.
As mentioned previously, density information is kept for all the columns in a statistics
object, in the order that they appear in the statistics definition. By default, SQL Server
assumes columns are independent so, if a relationship or dependency exists between
columns, multi-column statistics can help with cardinality estimation problems in
