UPDATE STATISTICS - What/When to RUN?

When and what to run Update Statistics

In terms of updating statistics, have multiple options, including:

1. Update Statistics Task (Maintenance Plan)
2. sp_updatestats (DO NOT use this option and not accurate)
3. UPDATE STATISTICS

There are two kinds of statistics:
1. Index Statistics
2. Column Statistics

1) By default, the UPDATE STATISTICS statement updates both index and column statistics.

•      Using the COLUMNS option of this statement will update column statistics only.
•      Using the INDEX option will update index statistics only.

2) By default, the UPDATE STATISTICS statement uses only a sample of records of the table.

•     Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

3) Rebuilding an index, for example by using the ALTER INDEX … REBUILD statement,

•    Will update only index statistics with the equivalent of using WITH FULLSCAN.
•    Rebuilding indexes does not update any column statistics.

4) Reorganizing an index, for example using the ALTER INDEX … REORGANIZE statement,
     does not update any statistics.


Maintenance Tasks:
----------------
Use below 2 tasks in maintenance plan in any order to update index statistics and column statistics.

1.Rebuild index  --Rebuilt Index and update index stats
  ----------------
  1.Drop and re-create indexes in single transaction.
    (This removes fragmentation and reclaims DISK Space by compacting pages based on FILL factor value.
     Re-order index rows in contiguous pages)
  2.This operation can be ONLINE or OFFLINE
  3.Update INDEX statistics only with FULL SCANNING all the rows in table.
  4.DOES NOT update Column statistics

2.Update Statistics: COLUMN statistics
----------------------
  UPDATE STATISTICS with COLUMNS option of this statement will update column statistics only.

Additional notes:

1. If Avg Fragmentation level 5 percent greater then and less then 30 percent  ---Use Reorganize Index
2. If Avg Fragmentation level  greater then 30 percent  ---Use Rebuild index

sp_updatestats: 
Do not use sp_updatestats, this will not give 100% correct statistics.

The sp_updatestats command will only update statistics if data has changed,but the caveat is that only one (1) row has to have changed.

<30 index="" nbsp="" p="" reorganize="" use="">