SQL Engine - Optimizer Statistics


In order to make the best execution plan, the optimizer uses statistics on the database objects and the computer system.

The statistics are optimizer statistics, which are created for the purposes of query optimization and are stored in the data dictionary. These statistics should not be confused with others statistics such as performance statistics

Optimizer statistics are always treated as estimates and can become stale or out of date, even the microsecond after a complete computation.

To maintain the effectiveness of the query optimizer, you must have statistics that are representative of the data.

Good statistics improve the degree of accuracy of the measures that use the plan estimator to estimate a plan.

For table columns with skewed data, you should collect histograms.

The resulting statistics provide the query optimizer with information about data uniqueness and distribution. Using this information, the query optimizer is able to compute plan costs with a high degree of accuracy. This enables the query optimizer to choose the best execution plan based on the least cost.

Powered by ComboStrap