Sqlserver statistics are automatically updated

The Statistical Update Conditions for Applied tables (non-temporary tables) are 500 + 20%. The specific update conditions are Ifthestatisticsisdefinedonaregulartable and itisoutofdateif: Thetablesizehasgonefrom0to0rows. rows.

The statistical update condition for tables (non-temporary tables) that have been applied is 500 + 20%. the specific update condition is If the statistics is defined on a regular table, it is out of date if: the table size has gone from 0 to 0 rows. the number of rows in the table when the statistics were gath

The Statistical Update Conditions for Applied tables (non-temporary tables) are 500 + 20%. Specific Update Conditions

If the statistics is defined on a regular table, it is out of date if:

1. The table size has gone from 0 to> 0 rows.

2. The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then.

3. The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered.

• If the statistics object is defined on a temporary table, it is out of date as discussed above, then t that there is an additional threshold for recomputation at 6 rows, with a test otherwise identical to test 2 in the previous list.

It may take a long time to trigger this update condition in some large tables, because the query performance may be affected. Here we will introduce a trace flag 2371 of sqlserver.

When this flag is enabled, Statistics update will dynamically adjust the overall size of the table data volume to trigger the update condition.

(For example, 1000000 of the data will trigger an update (30000), 3% of the data will trigger (1000000000), which may improve the overall system performance.

Note: As updating statistics also consumes a certain amount of resources, enabling statistics will result in frequent updates. Therefore, the overall cost should be fully evaluated before enabling statistics.

(You do not need to enable this function unless the Statistics Update has a significant impact on the global data)

Personal Idea: since the development of relational databases, the differences between all products are getting smaller and smaller, and the details reflect the excellence of a product.

If 2371 of the features can be set as an option for a database, the granularity will be refined (the database may lead to different access features according to the purpose), the management of statistical information will be more reasonable and flexible.