Chapter 3 SQL server statistics (3) discover and process expired statistics

Statistical information is the main source of information about data distribution in predicates. If you do not know the specific data distribution, the optimizer cannot obtain the estimated data set and therefore cannot count the data to be returned. After the statistical information of a column is created, after DML operations such as insert, update, and delete are performed, the statistical information will become obsolete. Because

Statistics are the main sources of data distribution in predicates. If you do not know the specific data distribution, the optimizer cannot obtain the estimated data set and therefore cannot count the data to be returned. After the statistical information of a column is created, after DML operations such as insert, update, and delete are performed, the statistical information will become obsolete. Because

Preface:

StatisticsInformationIs the main data distribution in predicates.InformationSource. If you do not know the specific data distribution, the optimizer cannot obtain the estimated dataset, and thus cannotStatisticsThe data to be returned.

InStatisticsInformationAfter DML operations such as insert, update, and delete,StatisticsInformationIt will be out of date. These operations change the data and affect the data distribution. UpdateStatisticsInformation.

In the high activity table,StatisticsInformationIt may expire in a few hours. For a static table, it may take several weeks to become obsolete. This depends on the DML operations on the table.

From 2000, SQLServer will add the RowModCtr (Row Modification Counter) value in the table sysindexes. WhenStatisticsInformationAfter the update, the value is reset to 0 and then accumulated again. So you can see the value of this table.StatisticsInformationWhether it is out of date.

After 2000, SQLServer changed the tracking method and stored the changes to the corresponding data row. This value is an undisclosed ColModCtr.

However, sys. sysindexes to 2012 is still available. You can still use the value of this table to determine whether or notExpired.

Preparations:

This article uses the following system view and Compatibility View:

1. sys. sysindexes: Compatibility View, which provides RowModCtr column values, is the core of this article.

2. sys. indexes: Table IDStatisticsInformationName.

3. sys. objects: Get the schema name.

Steps:

Show RowModCtr with a high valueStatisticsInformation:

SELECT DISTINCT        OBJECT_NAME(SI.object_id) AS Table_Name ,        SI.name AS Statistics_Name ,        STATS_DATE(SI.object_id, SI.index_id) AS Last_Stat_Update_Date ,        SSI.rowmodctr AS RowModCTR ,        SP.rows AS Total_Rows_In_Table ,        'UPDATE STATISTICS [' + SCHEMA_NAME(SO.schema_id) + '].['        + OBJECT_NAME(SI.object_id) + ']' + SPACE(2) + SI.name AS Update_Stats_ScriptFROM    sys.indexes AS SI( NOLOCK )        INNER JOIN sys.objects AS SO( NOLOCK ) ON SI.object_id = SO.object_id        INNER JOIN sys.sysindexes SSI( NOLOCK ) ON SI.object_id = SSI.id                                                    AND SI.index_id = SSI.indid        INNER JOIN sys.partitions AS SP ON SI.object_id = SP.object_idWHERE   SSI.rowmodctr > 0        AND STATS_DATE(SI.object_id, SI.index_id) IS NOT NULL        AND SO.type = 'U'ORDER BY RowModCTR DESC

Analysis:

You need to know something:

1. From your last updateStatisticsInformationWhen?

2. UpdateStatisticsInformationHow many transactions will occur on the table afterwards?

3. Which T-SQL needs to be updated?StatisticsInformation.

4. UpdateStatisticsInformationIs it feasible? This is a comparison between the RowModCTR column and the Total_Rows_In_Table column.

When Auto_Update_Statistics is enabled for the database, it is necessary to update the data.StatisticsInformation. The following are some rules:

1. The table size increases from 0.

2. There is no problem when the table data is smaller than or equal to 500, and the ColModCtr increases from more than 500 rows.

3. When the number of rows in the table exceeds 500StatisticsInformationWhen the value of ColModCtr in the guiding column of an object exceeds 500 + 20%, it needs to be updated.

Example: if there is a 1 million-row table, the optimizer will consider it after inserting 200500 rows of new dataStatisticsInformationObsolete. However, this is not absolute.

Expand knowledge:

There is no direct access to the value of ColModCtr because it is only used for optimization and transparent to users. However, you can use DAC (dedicated administrator connection) to access the sys. sysrscols. rcmodified system. However, it is only available in 2008R2 and later versions.