Statistics Sql Server 統計資訊

標籤：

統計資訊的作用是：查詢最佳化工具使用統計資訊來建立可提高查詢效能的查詢計劃。

 

統計資訊是資料庫的object，提供的統計資訊是關於table或indexed view上列的統計資訊。

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

 

在Table 目錄下查看Statistics能夠看到Statistics object，選中CIX_dt_test_idcode，查看Properties，查看該統計對象的資訊

 

Statistics columns是索引列，Update Statistics描述了Statistics最後一次更新的時間和statistics的更新方式：在建立或更新statistics時更新，或者被Sql Server自動更新，或者手動更新

對於索引，Sql Server會自動建立statistics，所以當索引建立時或修改時，會更新statistics；

Sql Server會自動更新statistics，必須滿足一定的條件，修改的資料量到了一定的threshold value，並且自動更新統計資訊選項 AUTO_UPDATE_STATISTICS 設定為 ON。

手動更新，只需要勾選update statistics for thees，並確定即可手動更新，也可以使用Update statistics語句更新。

 

 Statistics對象的詳細資料，請參考Update statistic。

 

 

自動建立的statistics對象，以“_WA_sys”開頭，需要開啟資料庫選項，將自動建立統計資訊選項 AUTO_CREATE_STATISTICS設定為ON。

 

從sys.databasees中查看是否允許自動更新和建立statistics對象。

SELECT  name AS dbName,         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‘FROM sys.databases WHERE database_ID =db_id();

 

使用 AUTO_CREATE_STATISTICS 選項

在自動建立統計資訊選項 AUTO_CREATE_STATISTICS 為 ON 時，查詢最佳化工具將根據需要在查詢謂詞中的單獨列上建立統計資訊，以便改進查詢計劃的基數估計。這些單列統計資訊在現有統計資訊對象中尚未具有長條圖的列上建立。

可以使用下面的查詢來確定查詢最佳化工具是否為查詢謂詞列建立了統計資訊。它將查詢目錄檢視 sys.stats 和 sys.stats_columns，以便為具有單列統計資訊的所有列返回資料庫物件名、列名和統計資訊名稱。查詢最佳化工具通過使用 AUTO_CREATE_STATISTICS 選項在單列上建立統計資訊時，統計資訊名稱以 _WA 開頭。

SELECT OBJECT_NAME(s.object_id) AS object_name,    COL_NAME(sc.object_id, sc.column_id) AS column_name,    s.name AS statistics_nameFROM sys.stats AS s Inner Join sys.stats_columns AS sc    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_idWHERE s.name like ‘_WA%‘    and s.object_id=object_id(‘dbo.dt_test‘)ORDER BY s.name;

使用 AUTO_UPDATE_STATISTICS 選項

When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is on, the query optimizer determines when statistics might be out-of-date and then updates them when they are 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 be 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 determine 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, and statistics created with the CREATE STATISTICS statement. This option also applies to filtered statistics.

 

ALTER DATABASE AdventureWorks    SET AUTO_CREATE_STATISTICS ON;ALTER DATABASE AdventureWorks    SET AUTO_UPDATE_STATISTICS ON;

如果資料自動更新統計資訊選項設定為ON，那麼可以將單獨的表上的統計資訊設定為不自動update，這樣資料庫不會自動更新這些統計資訊，但是當資料自動更新統計資訊選項設定為OFF時，資料庫上的所有統計資訊都將不能自動更新。

Disabling and Re-enabling AUTO_UPDATE_STATISTICS for Some Statistics

When AUTO_UPDATE_STATISTICS is on, you can override the database-wide statistics update behavior and set automatic statistics updates off for an individual table, index, or column, as required by your application. When AUTO_UPDATE_STATISTICS is on, you can disable and re-enable automatic statistics updates for a table, index, or column 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 NORECOMPUTE option.

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

When AUTO_UPDATE_STATISTICS is off, you cannot set automatic updates to on for an 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 will not occur.

 

統計資訊更新有時是需要耗費很多資源的operation，當query optimizer發現表中的statistics對象到期時，需要更新統計資料，如果必須等待統計資訊更新完成才產生查詢計劃，那麼勢必增加查詢計劃的產生時間，可以使用非同步更新，查詢計劃仍然使用舊的統計資訊來產生查詢計劃，同時資料庫繼續統計資訊，即不等統計學資訊更新完成，query optimizer使用已有的統計資訊來產生查詢計劃，這樣並行進行，提高效能。

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 are 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 are out-of-date; The query optimizer could choose a suboptimal query plan if statistics are out-of-date when the query compiles. Queries that compile after the asynchronous updates have completed will benefit from using the updated statistics.

The database-wide asynchronous statistics update option, AUTO_UPDATE_STATISTICS_ASYNC, determines whether the query optimizer 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 predicates, and statistics created with the CREATE STATISTICS statement.

 

Consider using synchronous statistics for the following scenario:

• You perform operations that change the distribution of data, such as truncating a table or performing a bulk update of a large percentage of the rows. If you do not update the statistics after completing the operaton, using synchronous statistics will ensure statistics are up-to-date before executing queries on the changed data.

Consider using asynchronous statistics to achieve more 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 more 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 Hashes.

• Your application has 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.

 

參考文檔

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

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

Statistics Sql Server 統計資訊