Statistics of SQL Server research-discover and process expired statistics, and analyze SQL statistics

Statistics of SQL Server research-discover and process expired statistics, and analyze SQL statistics
Preface:

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. These operations change the data and affect the data distribution. Update the statistics.

In a high-activity table, the statistics 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.

Starting from 2000, SQLServer will add the RowModCtr (Row Modification Counter) value in the table sysindexes. After the statistics are updated, the value is reset to 0 and then accumulated again. Therefore, you can check the value of this table to see whether the statistics are out of date. The previous chapter studies the impact of statistics on non-index keys.

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 the table has expired.

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: use the table ID to obtain the statistical information name.

3. sys. objects: Get the schema name.

Steps:

Displays statistics with a high RowModCtr value:

123456789101112131415161718192021222324252627282930313233

SELECTDISTINCT OBJECT_NAME (SI. object_id) AS Table_Name, SI. nameAS Statistics_Name, STATS_DATE (SI. object_id, SI. index_id) AS Last_Stat_Update_Date, SSI. rowmodctrAS RowModCTR, SP. rowsAS Total_Rows_In_Table, 'Update STATISTICS ['+ SCHEMA_NAME (SO. schema_id) + ']. ['+ OBJECT_NAME (SI. object_id) + ']' + SPACE (2) + SI. nameAS Update_Stats_Script FROM sys. indexesAS SI (NOLOCK) INNERJOIN sys. objectsAS SO (NOLOCK) on si. object_id = SO. object_id INNERJOIN sys. sysindexesSSI (NOLOCK) on si. object_id = SSI. id ANDSI. index_id = SSI. indid INNERJOIN sys. partitionsAS spon si. object_id = SP. object_id where ssi. rowmodctr> 0 ANDSTATS_DATE (SI. object_id, SI. index_id) is notnull andso. type = 'U' ORDERBY RowModCTR DESC

Analysis:

You need to know something:

1. When was the last time you updated the statistics?

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

3, which T-SQL needs to be used to update statistics.

4. Is it feasible to update statistics? 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 statistics. 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 500 rows, you must update the number of rows in the guiding column of the statistical information object when the value of ColModCtr exceeds 500 + 20%.

Example: if there is a 1 million-row table, the optimizer inserts 200500 rows of new data and considers the statistical information obsolete. 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.

SQL SERVER 2008 database separation problems

Delete link:
When the database has one or more active connections, the "status" is "not ready", and the "message" column displays "<active connections> active connections"-for example: "1 active connection ". Before detaching a database, You must select "Delete Connection" to disconnect all active connections.

Update statistics:
By default, the separation operation retains the expired optimization statistics when the database is detached. to update the existing optimization statistics, select the "update statistics" check box.

How does an SQL statement count the number of tables in database A in SQL server 2005?

SELECT count (*)
FROM sysobjects
WHERE (xtype = 'U ')

Sysobjects is a system table of SQL server used to record various objects in the database.
Under the Enterprise Manager, right-click the database server and select "Edit SQL server Registration attributes" in the pop-up menu. Then, check the system database and system objects.
Then, when you open the database you are querying, you will see the hidden system table, which contains a sysobjects table.