SQL Server basic algorithm for automatically updating statistics

When I first came into contact with SQL Server, I thought that SQL Server would update statistics while changing data. In fact, SQL Server did not. based on performance considerations, SQL server uses the following algorithm to update statistics.

 

The basic algorithm for automatically updating statistics is:

· If the table is in tempdb
The base number of the database table is less than 6, and each of the six modifications automatically updated to the table.

· If the base number of the table is greater than 6 but less than or equal to 500, the update status is
500 modification.

· If the base number is greater than 500, when the table is updating statistics (500 + 20% tables), it is changed.

· Changing the base of a table variable does not trigger automatic update of statistics.

 

Note:: Strictly speaking, SQL Server
The base number is the number of rows in the table.

Note:: In addition to the base, the selectivity of This predicate also affects autostats.
Generate. This means that the statistics may not be updated by the afer.
Every 500
The base number is modified to <500
Or change the base number of each 20% to 500. Proportional Factor (value range from
1 to 4, 1
And 4
Generate the autostats product of the number obtained from the algorithm according to the selectivity.
Generate the actual number of modifications required.

In the form of a table, you can summarised
The above algorithms:

_________________________________________________________________________________

Table type | empty condition | threshold whenempty | threshold when not empty

_________________________________________________________________________________

Permanent | <500 rows | # ofchanges> = 500 | # of changes> = 500 + (20% of cardinality)

___________________________________________________________________________

Temporary | <6 rows | # ofchanges> = 6 | # of changes> = 500 + (20% of cardinality)

___________________________________________________________________________

Table

Variables | change in cardinality does not affectautostats generation.

___________________________________________________________________________