The impact of SQL Server statistics on queries

The optimizer chooses which execution plan is selected based on the extinction and is related to the row count statistics, which are automatically generated by default in the process of optimization.

Once the column is marked for statistical information, the query optimizer looks for the statistics for that column, and if there is a statistic, the next step is to check

To make sure it's new enough. Rebuild statistics if they expire. (This shows that statistics are column-based).

--------------------------------------------------------------------------------------------------------------- -----------------

The option to automatically create statistics in the database is turned on by default:

ALTER DATABASE .... set Auto_create_statistics {on | off};

ALTER DATABASE .... set Auto_update_statistics {on | off};

If automatic creation of the system is disabled, the query outputs a warning. In this mode the DBA ensures that the statistics object is up-to-date.

Actions can be prompted to control the behavior of the statistics object.

Create Index ... with (Statistics_norecompute = ON)

Create statistics .... with (NoRecompute)

Although these options are generally turned on, the ability to create or update statistics is disabled in the following scenarios.

1. The DBA explicitly decides to update statistics instead of Automatic Updates, and the database has a Maintenance window, in which case the DBA believes that the change in statistics will

Optimizer chooses a slower plan

2, the data table is too large, automatic update hair time is too long.

3. There are many unique values in the database tables, and the sampling frequency used to generate the statistics is not sufficient to obtain all the statistics required to generate a good execution plan.

A DBA can use a maintenance window to increase the sampling frequency (the default frequency varies according to the size of the table) to manually update statistics.

4. The database defines a short query timeout and does not want automatic statistics to cause the query time to be significantly longer, as this may cause a query to time out.

The impact of SQL Server statistics on queries