Automatic update statistics is based on the datachange function, which returns the percentage of data modified within a table. You can include this function in existing update statistics scripts to take advantage of its tracking abilities. For example:
select @datachange = datachange("authors", null, null)
if @datachange > 50
begin
update statistics authors
end
Consider the following when you use the datachange function:
The percentage datachange returns is based on the number of DML operations and the table size. However, each deferred operation counts as two separate operations, one delete and one re-insert. Consequently, when updating multiple records in the same statement, datachange may report a percentage up to twice as high as the actual number of rows modified.
The datachange parameters are table_name, partition_name, and column_name, in that order. This allows you to detect the change in particularly volatile fields or a specific partition, and update the index statistics for specific indices as opposed to detecting the change in all indices.
datachange reports a percentage
changed instead of the number of rows changed because reporting
the number of rows does not really provide useful information by
itself and is useful only when compared in the context of the size
of the table. For example, a value of datachange=5,000 could
be significant if the table contains 5,100 rows, but insignificant
if it contains 500 million. By using a percentage, it makes it easier
to establish relative thresholds in maintenance scripts.
