12th.--sqlserver statistics (3)--Find out the overdue statistics and process

In the original: 12th Chapter--sqlserver Statistics (3)------discover and process outdated statistics

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.

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:

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)        Inne R 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) are not NULL and        so.type = ' U ' ORDER by rowmodctr DESC

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.

12th.--sqlserver statistics (3)--Find out the overdue statistics and process