In addition to the automatic adjustment of column statistics that is performed when a query is executed, the statistics governor also monitors the health and usage of optimizer statistics. The statistics governor automatically evaluates the health and usefulness of each statistic in the database and performs required maintenance so that the statistics are self-monitored and self-healing. Statistics maintenance is performed in the background and does not create a significant load on database server performance.

The statistics governor performs the following tasks:

• Records statistics usage and estimation errors from query feedback
• Fixes or remakes statistics that have low accuracy
• Stops automatic maintenance for statistics that cannot be maintained efficiently
• Creates potentially useful statistics
• Drops unused statistics

The update_statistics option controls whether the specified connection can send query feedback to the statistics governor. If this option is set to Off, the statistics governor does not receive query feedback from the specified connection. However, the statistics governor can still receive query feedback from other connections and perform maintenance operations on statistics.

The statistics governor decides when to fix or create a statistics based on its health and usage. A statistic can be fixed or created either by gathering statistics during query execution, or by a separate process called the statistics cleaner. You can disable the statistics cleaner by using the StatisticsCleaner option for the sa_server_option system procedure without disabling the statistics governor, but when the statistics cleaner is turned off, statistics are only created or fixed when a query is run.

To reduce server workload, the statistics governor stops maintenance on statistics that are hard to fix or never used. Statistics that have been fixed numerous times within a short period of time and still return poor estimates are dropped and are not maintained for 30 days. Dropped statistics are recreated after 30 days, and regular maintenance is resumed. You can disable this feature using the DropBadStatistics option for the sa_server_option system procedure. Statistics that have not been used in the last 90 days are also dropped. To disable this feature, use the DropUnusedStatistics option for the sa_server_option system procedure. You can resume maintenance on a statistic at any time by using the CREATE STATISTICS, DROP STATISTICS, or ALTER STATISTICS statements.

Statistics are only monitored for tables that are loaded into memory, and these statistics are flushed every 30 minutes. During flushing, the health and usage of the statistics are checked, and the statistics governor performs maintenance on the statistics. The state information about a statistic (such as health, usage, and information about when to update or drop a statistic) does not persist between sessions. The state information is reset when the database server shuts down.

 See also