SQL Server Research Statistics-discover overdue statistics and process them in detail

Objective

Statistics are the main source of information about the distribution of data in predicates, and if you do not know the specific data distribution, the optimizer cannot get the estimated data set, so it cannot count the data that needs to be returned.

After you create the statistics for a column, the statistics become obsolete after DML operations such as Insert,update,delete . Because these operations change the data, the data distribution is affected. You need to update the statistics at this time.

In a high-activity table, statistics can become obsolete in a few hours. For static tables, it may be several weeks before they become obsolete. It depends on the operation of DML on the table.

From The beginning,SQL Server Add and delete operations will increase the RowModCtrin table sysindexes (Row modification Counter) value, when the statistics are updated, the value resets to 0and accumulates again. So look at this value of the table to see if the statistics are obsolete. The previous chapter studies the influence of the statistical information on non-indexed keys.

After this,SQL Server changes the tracking method and stores the changes to the corresponding data rows. This value is an undisclosed colmodctr.

However, the sys.sysindexes is still available, or you can use the value of this table to determine whether it expires.

Preparatory work:

This article will use the following system views and compatibility views:

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

2. sys.indexes : Use the table ID to get the statistics name.

3. sys.objects : Gets the schema name.

Steps:

Displays statistics with high RowModCtr values:

123456789101112131415161718192021222324252627282930313233

SELECTDISTINCT        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_script fromSYS.Indexes as SI(NOLOCK )        INNERJOINSYS.Objects as So(NOLOCK ) onSI.object_id= So.object_id        INNERJOINSYS.sysindexesSSI(NOLOCK ) onSI.object_id= SSI.ID                                                     andSI.index_id= SSI.indid        INNERJOINSYS.Partitions as SP onSI.object_id= SP.object_idWHERESSI.rowmodctr> 0         andstats_date(SI.object_id,SI.index_id) is notNULL         and So.type= ' U 'ORDER byRowModCTRDESC

Analysis:

There are a few things to know:

1. when was the last time you updated statistics?

2. How many transactions occur on the table after the statistics are updated?

3. which T-SQL is required for updating statistics.

4. is it possible to update statistical information? This is the comparison between the RowModCTR column and the total_rows_in_table column.

It is necessary to update the statistics when there is data after the Auto_update_statistics is opened in the database. Here are some rules:

1. The table size grows from 0 .

2. There is no problem when the table data is less than or equal to $ , and the colmodctr begins to grow after more than a line.

3. when the number of rows in a table exceeds the number of lines, the colmodctr value of the boot column of the statistics object exceeds the line count of 500+20% and needs to be updated.

Example: There is a table with a million row, and the optimizer considers the statistics obsolete after inserting new data into the 200500 row. But it's not absolute.

Expand your knowledge:

There is no direct way to access the value of colmodctr because it is only used for optimization and is transparent to the user, but can be accessed using a DAC (dedicated administrator connection) Sys.sysrscols.rcmodified System. However, only 2008R2 and later versions are available.