Statistics SQL Server Statistics

Statistical information is used by the query optimizer to create query plans that improve query performance by using statistics.

Statistics are the object of the database, and the statistics provided are information about the table or indexed view.

Statistics for query optimization is objects that contain statistical information on the distribution of values in one or more columns of a table or indexed view.

View statistics in table directory to see Statistics object, check Cix_dt_test_idcode, view properties, view information for this statistic

Statistics columns is an indexed column, update Statistics describes the time Statistics last update and how Statistics was updated: Updated when you create or update Statistics, or by SQL Server is automatically updated, or manually updated

For indexes, SQL Server automatically creates statistics, so the statistics is updated when the index is created or modified;

SQL Server automatically updates the statistics, must meet certain conditions, the amount of modified data to a certain threshold Value, and auto update STATISTICS options

To update manually, you only need to tick update statistics for thees and make sure you can update it manually, or use the UPDATE STATISTICS statement.

For more information on statistics objects, refer to update statistic.

The auto-created statistics object, beginning with "_wa_sys", needs to turn on the database option and set the Auto-Create statistics option auto_create_statistics to ON.

See if you want to allow automatic updates and creation of statistics objects from sys.databasees.

SELECTName asDbName, is_auto_create_stats_on as 'Auto Create Stats', is_auto_update_stats_on as 'Auto Update Stats', is_auto_update_stats_async_on as 'Async Auto Update Stats', Is_read_only as 'Read only' fromsys.databasesWHEREdatabase_id=db_id();

Using the Auto_create_statistics option

When the auto-create statistics option auto_create_statistics is on, the query optimizer creates statistics on separate columns in the query predicate as needed in order to improve the cardinality estimation of the query plan. These single-column statistics are created on columns in the existing statistics object that do not already have a histogram.

You can use the following query to determine whether the query optimizer has created statistics for the query predicate column. It queries catalog views sys.stats and Sys.stats_columns to return database object names, column names, and statistic names for all columns that have single-column statistics. When the query optimizer creates statistics on a single column by using the Auto_create_statistics option, the statistic name begins with _wa.

SELECT object_name(S.object_id) as object_name,    col_name(SC.object_id, sc.column_id) ascolumn_name, S.name asStatistics_name fromSys.stats assInner JoinSys.stats_columns asSC ons.stats_id=sc.stats_id andS.object_id =Sc.object_idWHERES.name like '_wa%'     andS.object_id=object_id('dbo.dt_test')ORDER  byS.name;

Using the Auto_update_statistics option

When the Automatic Update STATISTICS option, Auto_update_statistics, was on, and the query optimizer determines when statist ICS might is out-of-date and then updates to them when they is used by a query. Statistics become out-of-date after insert, UPDATE, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might is out-of-date by counting the number of data modifications since the Last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.

The query optimizer checks for out-of-date statistics before compiling a query and before executing a cached query plan. Before compiling a query, the query optimizer uses the columns, tables, and indexed views in the query predicate to Determ Ine which statistics might be out-of-date. Before executing a cached query plan, the Database Engine verifies that the query plan references up-to-date statistics.

The Auto_update_statistics option applies to STATISTICS objects created for indexes, single-columns in query predicates, a ND statistics created with the CREATE statistics statement. This option also applies to filtered statistics.

ALTER DATABASE AdventureWorks     SET  on ; ALTER DATABASE AdventureWorks     SET on;

If the Data Automatic Update STATISTICS option is set to ON, you can set the statistics on individual tables to not automatically update, so that the database does not automatically refresh these statistics, but when the Data Auto Update STATISTICS option is set to OFF, all statistics on the database will not be updated automatically.

Disabling and re-enabling auto_update_statistics for Some STATISTICS

When auto_update_statistics are on, you can override the Database-wide STATISTICS UPDATE behavior and set automatic stat Istics updates off for a individual table, index, or column, as required by your application. When auto_update_statistics are on, you can disable and re-enable automatic STATISTICS updates for a table, index, or Colum N in the following ways:

• Use the sp_autostats system stored procedure. This can disable or re-enable statistics updates for a table or index.

• Specify the NoRecompute option with the UPDATE STATISTICS statement. To re-enable statistics updates rerun UPDATE statistics without the norecompute option.

• Specify the NoRecompute option with the CREATE STATISTICS statement. To re-enable statistics updates, remove the statistics with DROP statistics and then run CREATE statistics without the NOR Ecompute option.

• Specify the Statistics_norecompute option with the CREATE INDEX statement. To re-enable statistics updates, you can run the ALTER INDEX with statistics_norecompute = OFF.

When auto_update_statistics are off, you cannot set automatic Updates to on for a individual table, index, or column. Re-enabling Automatic statistics updates restores the behavior specified by the Auto_update_statistics option. If The Auto_update_statistics option is off, STATISTICS updates would not occur.

Statistics update is sometimes a lot of resource-intensive operation, when the query Optimizer Discovery table Statistics object expires, you need to update the statistics, if you have to wait for the statistics update to complete before generating a query plan, it is bound to increase the generation time of queries plan , you can use the asynchronous update, the query plan still uses the old statistics to generate the query plan, and the database continues to statistics, that is, the statistical information update is complete, query optimizer using the existing statistics to generate the plan, so parallel, improve performance.

When to use synchronous or asynchronous Statistics Updates

Statistics updates can be either synchronous (the default) or asynchronous. With synchronous statistics updates, queries always compile and execute with up-to-date statistics; When statistics is out-of-date, the query optimizer waits for updated statistics before compiling and executing the query . With asynchronous statistics updates, queries compile with existing statistics even if the existing statistics is out-of- Date The query optimizer could choose a suboptimal query plan if statistics is out-of-date when the query compiles. Queries that compile after the asynchronous updates has completed would benefit from using the updated statistics.

The Database-wide asynchronous Statistics update option, Auto_update_statistics_async, determines whether the query Optim Izer uses synchronous or asynchronous statistics updates. By default, the asynchronous Statistics update option is off, and the query optimizer updates statistics synchronously. The Auto_update_statistics_async option applies to STATISTICS objects created for indexes, single columns in query Predica TES, and statistics created with the CREATE statistics statement.

Consider using synchronous statistics for the following scenario:

• You perform operations This change the distribution of data, such as truncating a table or performing a bulk update of a l Arge percentage of the rows. If You don't update the statistics after completing the Operaton, using synchronous statistics would ensure statistics are Up-to-date before executing queries on the changed data.

Consider using asynchronous statistics to achieve + predictable query response times for the following scenarios:

• Your application frequently executes the same query, similar queries, or similar cached query plans. Your Query response times might be + predictable with asynchronous statistics updates than with synchronous statistics Updates because the query optimizer can execute incoming queries without waiting for up-to-date statistics. This avoids delaying some queries and not others. For more information about finding similar queries, see Finding and Tuning similar queries by Using query and query Plan H Ashes.

• Your application have experienced client request time outs caused by one or more queries waiting for updated statistics. In some cases, waiting for synchronous statistics could cause applications with aggressive time outs to fail.

Reference documents

https://msdn.microsoft.com/zh-cn/library/ms190397 (v=sql.100). aspx

Https://msdn.microsoft.com/en-us/library/ms187348.aspx

Statistics SQL Server Statistics