Databases Reference
In-Depth Information
quality statistics for big tables, especially for those where data is not randomly distributed
in their data pages. Manually updating statistics can also be a benefit after operations
such as data loads, that update large amounts of data, are performed.
On the other hand, also note that the update of statistics will cause a recompiling of plans
already in the plan cache which are using these statistics, so you may not want to do this
too often, either.
An additional consideration for manually updating statistics in a maintenance job is how
they relate to index rebuild maintenance jobs, which also update the index statistics. Keep
the following items in mind when combining maintenance jobs for both indexes and
statistics, remembering that there are both index and non-index column statistics, and
that index operations obviously may impact only the first of these.
•
Rebuilding an index
, for example by using the
ALTER
INDEX
…
REBUILD
statement,
will also update index statistics by scanning all the rows in the table, which is the
equivalent of using
UPDATE
STATISTICS
WITH
FULLSCAN
. Rebuilding indexes does
not update any column statistics.
•
Reorganizing an index
, for example using the
ALTER
INDEX
…
REORGANIZE
statement, does not update any statistics, not even index statistics.
•
By default
, the
UPDATE
STATISTICS
statement updates both index and column
statistics. Using the
INDEX
option will update index statistics only, and using the
COLUMNS
option will update non-indexed column statistics only.
So, depending on your maintenance jobs and scripts, several scenarios can exist. The
simplest maintenance plan is if you want to rebuild all the indexes and update all the
statistics. As mentioned before, if you rebuild all your indexes, then all the index statistics
will also be automatically updated by scanning all the rows on the table. Then you just
need to update your non-indexed column statistics by running
UPDATE
STATISTICS
WITH
FULLSCAN,
COLUMNS
. Since one job updates only index statistics, and the second
one updates only column statistics, it does not matter which one is executed first.
