Bad or missing statistics can lead to poor choices by the query optimizer. Statistics are created when an index is created, using a full scan of the data, and table statistics are created when a column is referenced in a filtering clause such as WHERE, HAVING, JOIN, etc.
Statistics are updated based on the following formula (if set to auto-update):
* When a table with no rows gets a row
* When 500 rows are changed to a table that is less than 500 rows
* When 20% + 500 are changed in a table greater than 500 rows
DBCC SHOW_STATISTICS displays information about the statistic in question:
Automatic statistics updates work well in most situations, except when you have outlier tables in your database that are irregularly updated and may not trigger a statistics update. In these cases, manual updates will be necessary. There are two ways to update statistics:
sp_updatestats – will update based on a random data sampling; less accurate than those built with a full scan.
UPDATE STATISTICS WITH FULL SCAN – performs a full scan of data. Localized to a single table, index, or set of stats. The WITH FULL SCAN portion is optimal.
Index reorganizations do not modify statistics.
How do I know when to update stats?
By tracking the behavior of your queries to determine when the optimizer begins making suboptimal decisions.- not always an easy task.
The NORECOMPUTE option when creating or updating statistics prevents them from being automatically updated. STATISTICS_NORECOMPUTE option can be used when you create an index.