統計資料Statistics

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

查詢最佳化工具會使用統計資料來建立可改善查詢效能的查詢計劃。The Query Optimizer uses statistics to create query plans that improve query performance. 對於大部分查詢而言,查詢最佳化工具已經產生高品質查詢計劃的必要統計資料。不過,在少數情況下,您必須建立其他統計資料或修改查詢設計,以便獲得最佳結果。For most queries, the Query Optimizer already generates the necessary statistics for a high quality query plan; in some cases, you need to create additional statistics or modify the query design for best results. 本主題將討論有效使用查詢最佳化統計資料的概念和指導方針。This topic discusses statistics concepts and provides guidelines for using query optimization statistics effectively.

元件和概念Components and Concepts

統計資料Statistics

查詢最佳化的統計資料是指包含資料表或索引檢視表之一或多個資料行中值分佈相關統計資料的二進位大型物件 (BLOB)。Statistics for query optimization are binary large objects (BLOBs) that contain statistical information about the distribution of values in one or more columns of a table or indexed view. 查詢最佳化工具會使用這些統計資料來估計查詢結果中的基數或資料列數目。The Query Optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. 這些基數估計值可讓查詢最佳化工具建立高品質的查詢計劃。These cardinality estimates enable the Query Optimizer to create a high-quality query plan. 例如,根據您的述詞而定,查詢最佳化工具可使用基數估計值來選擇索引搜尋運算子,而非需要更大量資源的索引掃描運算子 (如果這樣做會改善查詢效能)。For example, depending on your predicates, the Query Optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, if doing so improves query performance.

每個統計資料物件都是針對一或多個資料表資料行的清單所建立,其中包含「長條圖」 以顯示第一個資料行中的值分佈狀態。Each statistics object is created on a list of one or more table columns and includes a histogram displaying the distribution of values in the first column. 多個資料行的統計資料物件也會儲存這些資料行之間值相互關聯的相關統計資料。Statistics objects on multiple columns also store statistical information about the correlation of values among the columns. 這些相互關聯統計資料 (或稱「密度」 ) 衍生自資料行值之相異資料列的數目。These correlation statistics, or densities, are derived from the number of distinct rows of column values.

長條圖Histogram

「長條圖」 可測量資料集中每一個相異值的發生頻率。A histogram measures the frequency of occurrence for each distinct value in a data set. 查詢最佳化工具會計算有關統計資料物件之第一個索引鍵資料行中資料行值的長條圖,以統計方式取樣資料列或執行資料表或檢視表中所有資料列的完整掃描來選取資料行值。The query optimizer computes a histogram on the column values in the first key column of the statistics object, selecting the column values by statistically sampling the rows or by performing a full scan of all rows in the table or view. 如果長條圖是從一組取樣的資料列所建立,資料列數和相異值數的儲存總計會是預估值,而且不需要為整數。If the histogram is created from a sampled set of rows, the stored totals for number of rows and number of distinct values are estimates and do not need to be whole integers.

注意

SQL ServerSQL Server 中的長條圖只會針對單一資料行建置;也就是統計資料物件的索引鍵資料行集合中第一個資料行。Histograms in SQL ServerSQL Server are only built for a single column-the first column in the set of key columns of the statistics object.

若要建立長條圖,查詢最佳化工具會排序資料行值、計算符合每一個相異資料行值的值數目,然後將資料行值彙總成最多 200 個連續長條圖步驟。To create the histogram, the query optimizer sorts the column values, computes the number of values that match each distinct column value and then aggregates the column values into a maximum of 200 contiguous histogram steps. 每一個長條圖步驟都包含某個範圍的資料行值,後面緊接著上限資料行值。Each histogram step includes a range of column values followed by an upper bound column value. 此範圍包括界限值之間的所有可能資料行值,但是不包括界限值本身。The range includes all possible column values between boundary values, excluding the boundary values themselves. 最低的已排序資料行值就是第一個長條圖步驟的上限值。The lowest of the sorted column values is the upper boundary value for the first histogram step.

更詳細來說,SQL ServerSQL Server 會以下列三個步驟,從已排序的資料行值集合來建立「長條圖」 :In more detail, SQL ServerSQL Server creates the histogram from the sorted set of column values in three steps:

  • 長條圖初始化:第一個步驟會從已排序的集合開頭處理一連串值,並收集最多 200 個 range_high_keyequal_rowsrange_rowsdistinct_range_rows 的值 (在此步驟中,range_rowsdistinct_range_rows 一律為零)。Histogram initialization: In the first step, a sequence of values starting at the beginning of the sorted set is processed, and up to 200 values of range_high_key, equal_rows, range_rows, and distinct_range_rows are collected (range_rows and distinct_range_rows are always zero during this step). 當所有的輸入都已用完,或已找到 200 個值時,就會結束第一個步驟。The first step ends either when all input has been exhausted, or when 200 values have been found.
  • 使用貯體合併掃描:第二個步驟會依順序處理統計資料索引鍵之前置資料行的每一個額外值;每個後續的值可以新增到最後一個範圍,或在結束時建立新的範圍 (由於輸入的值會排序,因此這是可行的)。Scan with bucket merge: Each additional value from the leading column of the statistics key is processed in the second step, in sorted order; each successive value is either added to the last range or a new range at the end is created (this is possible because the input values are sorted). 建立新的範圍時,會將現有的一組相鄰範圍摺疊成單一範圍。If a new range is created, then one pair of existing, neighboring ranges is collapsed into a single range. 系統會選取這一組範圍,以將資訊遺失的機率降至最低。This pair of ranges is selected in order to minimize information loss. 此方法會使用「最大差異」 演算法,讓長條圖中的步驟數減至最少,同時讓界限值之間的差異最大化。This method uses a maximum difference algorithm to minimize the number of steps in the histogram while maximizing the difference between the boundary values. 在這整個步驟期間,範圍摺疊之後的步驟數目仍然為 200。The number of steps after collapsing ranges stays at 200 throughout this step.
  • 長條圖彙總:第三個步驟可能會摺疊更多範圍 (如果不會遺失大量資訊的話)。Histogram consolidation: In the third step, more ranges may be collapsed if a significant amount of information is not lost. 長條圖步驟的數目可以少於相異值數目,即使包含了少於 200 個界限點的資料行也是如此。The number of histogram steps can be fewer than the number of distinct values, even for columns with fewer than 200 boundary points. 因此,即使資料行具有超過 200 個唯一值,長條圖仍可能只需 200 個以下的步驟。Therefore, even if the column has more than 200 unique values, the histogram may have less than 200 steps. 若資料行都是由唯一值組成,則合併的長條圖將只有最少的三個步驟。For a column consisting of only unique values, then the consolidated histogram will have a minimum of three steps.

注意

如果已使用樣本來建置長條圖,而非進行完整掃描,則 equal_rowsrange_rowsdistinct_range_rowsaverage_range_rows 的值會是預估值,而且不需要為整數。If the histogram has been built using a sample rather than fullscan, then the values of equal_rows, range_rows, and distinct_range_rows and average_range_rows are estimated, and therefore they do not need to be whole integers.

下列長條圖顯示包含六個步驟的長條圖。The following diagram shows a histogram with six steps. 第一個上限值左側的區域就是第一個步驟。The area to the left of the first upper boundary value is the first step.

針對上述每一個長條圖步驟:For each histogram step above:

  • 粗線代表上限值 (range_high_key) 以及其所發生的次數 (equal_rows)Bold line represents the upper boundary value (range_high_key) and the number of times it occurs (equal_rows)

  • range_high_key 左邊的實線區域代表資料行值範圍,以及每一個資料行值發生的平均次數 (average_range_rows)。Solid area left of range_high_key represents the range of column values and the average number of times each column value occurs (average_range_rows). 第一個長條圖步驟的 average_range_rows 一定是 0。The average_range_rows for the first histogram step is always 0.

  • 虛線代表用來預估範圍內相異值總數的取樣值 (distinct_range_rows) 以及範圍內的值總數 (range_rows)。Dotted lines represent the sampled values used to estimate total number of distinct values in the range (distinct_range_rows) and total number of values in the range (range_rows). 查詢最佳化工具會使用 range_rowsdistinct_range_rows 來計算 average_range_rows,而且不會儲存取樣值。The query optimizer uses range_rows and distinct_range_rows to compute average_range_rows and does not store the sampled values.

密度向量Density Vector

密度是給定資料行或組合資料行中的重複項目數量資訊,其計算方式為 1/(相異值數目)。Density is information about the number of duplicates in a given column or combination of columns and it is calculated as 1/(number of distinct values). 查詢最佳化工具會使用密度來增強查詢的基數預估,這些查詢會從相同的資料表或索引檢視表傳回多個資料行。The query optimizer uses densities to enhance cardinality estimates for queries that return multiple columns from the same table or indexed view. 當密度降低時,值的選擇性會增加。As density decreases, selectivity of a value increases. 例如,在表示車種的資料表中,許多車種的製造商都是相同的,但每輛車都有一個唯一的汽車識別號碼。For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). 由於 VIN 的密度比製造商低,因此 VIN 的索引會比製造商的索引更具選擇性。An index on the VIN is more selective than an index on the manufacturer, because VIN has lower density than manufacturer.

注意

「頻率」是統計資料物件第一個索引鍵資料行中每一個相異值的發生次數資訊,其計算方式為資料列計數乘以密度。Frequency is information about the occurrence of each distinct value in the first key column of the statistics object, and is calculated as row count * density. 如果資料行具有唯一值,則其最大頻率為 1。A maximum frequency of 1 can be found in columns with unique values.

密度向量針對統計資料物件中資料行的每個前置詞各包含一個密度。The density vector contains one density for each prefix of columns in the statistics object. 例如,如果統計資料物件具有 CustomerIdItemIdPrice 等索引鍵資料行,就會根據下列每一個資料行前置詞來計算密度。For example, if a statistics object has the key columns CustomerId, ItemId and Price, density is calculated on each of the following column prefixes.

資料行前置詞Column prefix 計算密度的依據Density calculated on
(CustomerId)(CustomerId) 與 CustomerId 的值相符的資料列Rows with matching values for CustomerId
(CustomerId, ItemId)(CustomerId, ItemId) 與 CustomerId 和 ItemId 的值相符的資料列Rows with matching values for CustomerId and ItemId
(CustomerId, ItemId, Price)(CustomerId, ItemId, Price) 與 CustomerId、ItemId 和 Price 的值相符的資料列Rows with matching values for CustomerId, ItemId, and Price

篩選的統計資料Filtered Statistics

對於從定義完善的資料子集中選取的查詢而言,篩選的統計資料可以改善查詢效能。Filtered statistics can improve query performance for queries that select from well-defined subsets of data. 篩選的統計資料會使用篩選述詞來選取統計資料中所含的資料子集。Filtered statistics use a filter predicate to select the subset of data that is included in the statistics. 設計完善的篩選統計資料可以改善查詢執行計畫 (相較於完整資料表統計資料而言)。Well-designed filtered statistics can improve the query execution plan compared with full-table statistics. 如需篩選述詞的詳細資訊,請參閱 CREATE STATISTICS (Transact-SQL)For more information about the filter predicate, see CREATE STATISTICS (Transact-SQL). 如需有關何時建立篩選統計資料的詳細資訊,請參閱本主題的 何時建立統計資料 一節。For more information about when to create filtered statistics, see the When to Create Statistics section in this topic.

統計資料選項Statistics Options

您可以設定三個選項來影響何時及如何建立和更新統計資料。There are three options that you can set that affect when and how statistics are created and updated. 這些選項只會在資料庫層級設定。These options are set at the database level only.

AUTO_CREATE_STATISTICS 選項AUTO_CREATE_STATISTICS Option

開啟自動建立統計資料選項 AUTO_CREATE_STATISTICS 時,查詢最佳化工具就會視需要針對查詢述詞中的個別資料行來建立統計資料,以便改善查詢計劃的基數估計值。When the automatic create statistics option, AUTO_CREATE_STATISTICS is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan. 這些單一資料行統計資料是針對在現有統計資料物件中尚未具有長條圖的資料行建立的。These single-column statistics are created on columns that do not already have a histogram in an existing statistics object. AUTO_CREATE_STATISTICS 選項不會判斷系統是否針對索引建立了統計資料。The AUTO_CREATE_STATISTICS option does not determine whether statistics get created for indexes. 這個選項也不會產生篩選的統計資料。This option also does not generate filtered statistics. 它會嚴格套用至完整資料表的單一資料行統計資料。It applies strictly to single-column statistics for the full table.

當查詢最佳化工具因使用 AUTO_CREATE_STATISTICS 選項而產生統計資料時,統計資料名稱就會以 _WA 作為開頭。When the Query Optimizer creates statistics as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA. 您可以使用下列查詢來判斷查詢最佳化工具是否已經針對查詢述詞資料行建立統計資料。You can use the following query to determine if the Query Optimizer has created statistics for a query predicate column.

SELECT OBJECT_NAME(s.object_id) AS object_name,  
    COL_NAME(sc.object_id, sc.column_id) AS column_name,  
    s.name AS statistics_name  
FROM sys.stats AS s 
INNER JOIN sys.stats_columns AS sc  
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id  
WHERE s.name like '_WA%'  
ORDER BY s.name;  

AUTO_UPDATE_STATISTICS 選項AUTO_UPDATE_STATISTICS Option

開啟自動更新統計資料選項 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.

  • SQL ServerSQL Server (截至 SQL Server 2014 (12.x)SQL Server 2014 (12.x)) 會使用變更資料列的百分比作為臨界值。Up to SQL Server 2014 (12.x)SQL Server 2014 (12.x), SQL ServerSQL Server uses a threshold based on the percent of rows changed. 與資料表中的資料列數無關。This is regardless of the number of rows in the table. 臨界值是:The threshold is:

    • 若資料表基數在評估統計資料時為 500 或更小的數值,將會在每 500 次修改之後更新。If the table cardinality was 500 or less at the time statistics were evaluated, update for every 500 modifications.
    • 若資料表基數在評估統計資料時為超過 500 的數值,將會在每 500 + 20% 的修改次數之後更新。If the table cardinality was above 500 at the time statistics were evaluated, update for every 500 + 20 percent of modifications.
  • SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始並 在資料庫相容性層級 130 之下,SQL ServerSQL Server 會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and under the database compatibility level 130, SQL ServerSQL Server uses a decreasing, dynamic statistics update threshold that adjusts according to the number of rows in the table. 這是以 1000 乘以目前資料表基數的平方根來計算。This is calculated as the square root of the product of 1000 and the current table cardinality. 例如,如果您的資料表包含 2 百萬個資料列,則計算結果是 sqrt (1000 * 2000000) = 44721.359。For example if your table contains 2 million rows, then the calculation is? sqrt (1000 * 2000000) = 44721.359. 透過這項變更,大型資料表上的統計資料會經常更新。With this change, statistics on large tables will be updated more often. 不過,如果資料庫的相容性層級低於 130,便會套用 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 臨界值。However, if a database has a compatibility level below 130, then the SQL Server 2014 (12.x)SQL Server 2014 (12.x) threshold applies. ??

重要

資料庫相容性層級 低於 130 之下,從 SQL Server 2008 R2SQL Server 2008 R2 開始至 SQL Server 2014 (12.x)SQL Server 2014 (12.x),或是從 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017,使用追蹤旗標 2371 時,SQL ServerSQL Server 就會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。Starting with SQL Server 2008 R2SQL Server 2008 R2 through SQL Server 2014 (12.x)SQL Server 2014 (12.x), or in SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 under database compatibility level lower than 130, use trace flag 2371 and SQL ServerSQL Server will use a decreasing, dynamic statistics update threshold that adjusts according to the number of rows in the table.

在編譯查詢及執行快取查詢計劃之前,查詢最佳化工具會檢查是否有過期的統計資料。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. 在執行快取查詢計劃之前, Database EngineDatabase Engine 會確認查詢計劃是否參考最新的統計資料。Before executing a cached query plan, the Database EngineDatabase Engine verifies that the query plan references up-to-date statistics.

AUTO_UPDATE_STATISTICS 選項會套用至針對索引所建立的統計資料物件、查詢述詞中的單一資料行,以及使用 CREATE 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.

如需控制 AUTO_UPDATE_STATISTICS 的詳細資訊,請參閱 控制 SQL Server 中的 Autostat (AUTO_UPDATE_STATISTICS) 行為 (機器翻譯)。For more information about controlling AUTO_UPDATE_STATISTICS, see Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server.

AUTO_UPDATE_STATISTICS_ASYNCAUTO_UPDATE_STATISTICS_ASYNC

非同步統計資料更新選項 AUTO_UPDATE_STATISTICS_ASYNC 會決定查詢最佳化工具要使用同步或非同步統計資料更新。The 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. AUTO_UPDATE_STATISTICS_ASYNC 選項會套用至針對索引所建立的統計資料物件、查詢述詞中的單一資料行,以及使用 CREATE STATISTICS 陳述式所建立的統計資料。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.

注意

若要在 SQL Server Management StudioSQL Server Management Studio 中設定非同步統計資料更新選項,請在 [資料庫屬性] 視窗的 [選項] 頁面中,將 [自動更新統計資料] 和 [自動非同步更新統計資料] 選項設定為 [True] 。To set the asynchronous statistics update option in SQL Server Management StudioSQL Server Management Studio, in the Options page of the Database Properties window, both Auto Update Statistics and Auto Update Statistics Asynchronously options need to be set to True.

統計資料更新可以是同步 (預設值) 或非同步。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.

當您執行變更資料分佈的作業時 (例如截斷資料表,或大量更新大部分的資料列),請考慮使用同步統計資料。Consider using synchronous statistics when 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 operation, 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.

  • 您的應用程式遇到等候更新統計資料之一或多個查詢所造成的用戶端要求逾時。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.

INCREMENTALINCREMENTAL

當 CREATE STATISTICS 的 INCREMENTAL 選項為 ON 時,所建立的統計資料會依據每個分割區統計資料累加。When INCREMENTAL option of CREATE STATISTICS is ON, the statistics created are per partition statistics. 關閉 (OFF) 時,會卸除統計資料樹狀結構,而 SQL ServerSQL Server 會重新計算統計資料。When OFF, the statistics tree is dropped and SQL ServerSQL Server re-computes the statistics. 預設值為 OFF。The default is OFF. 此設定會覆寫資料庫層級 INCREMENTAL 屬性。This setting overrides the database level INCREMENTAL property. 如需建立累加統計資料的詳細資訊,請參閱 CREATE STATISTICS (Transact-SQL)For more information about creating incremental statistics, see CREATE STATISTICS (Transact-SQL). 如需自動建立每個分割區統計資料的詳細資訊,請參閱資料庫屬性 (選項頁面)ALTER DATABASE SET 選項 (Transact-SQL)For more information about creating per partition statistics automatically, see Database Properties (Options Page) and ALTER DATABASE SET Options (Transact-SQL).

當新的分割區區加入到大型資料表時,應更新統計資料,以包含新的分割區區。When new partitions are added to a large table, statistics should be updated to include the new partitions. 但是掃描整個資料表 (FULLSCAN 或 SAMPLE 選項) 所需的時間可能會很長。However the time required to scan the entire table (FULLSCAN or SAMPLE option) might be quite long. 此外,由於可能只需要新分割區區的統計資料,所以不需要掃描整個資料表。Also, scanning the entire table isn't necessary because only the statistics on the new partitions might be needed. 累加選項會以每個分割區區為基礎,建立及儲存統計資料,更新時只會重新整理需要新統計資料之分割區區的統計資料。The incremental option creates and stores statistics on a per partition basis, and when updated, only refreshes statistics on those partitions that need new statistics

如果不支援每個分割區區的統計資料,則會忽略該選項,並產生警告。If per partition statistics are not supported the option is ignored and a warning is generated. 針對下列統計資料類型,不支援累加統計資料:Incremental stats are not supported for following statistics types:

  • 建立統計資料時,所使用的索引未與基底資料表進行分割區對齊。Statistics created with indexes that are not partition-aligned with the base table.
  • 在 AlwaysOn 可讀取次要資料庫上建立的統計資料。Statistics created on Always On readable secondary databases.
  • 在唯讀資料庫上建立的統計資料。Statistics created on read-only databases.
  • 在篩選的索引上建立的統計資料。Statistics created on filtered indexes.
  • 在檢視上建立的統計資料。Statistics created on views.
  • 在內部資料表上建立的統計資料。Statistics created on internal tables.
  • 使用空間索引或 XML 索引建立的統計資料。Statistics created with spatial indexes or XML indexes.

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

何時建立統計資料When to create statistics

查詢最佳化工具已經用下列方式建立統計資料:The Query Optimizer already creates statistics in the following ways:

  1. 建立索引時,查詢最佳化工具就會針對資料表或檢視表的索引建立統計資料。The Query Optimizer creates statistics for indexes on tables or views when the index is created. 這些統計資料是針對索引的索引鍵資料行所建立的。These statistics are created on the key columns of the index. 如果索引是篩選的索引,查詢最佳化工具就會在針對篩選索引所指定的相同資料列子集上建立篩選的統計資料。If the index is a filtered index, the Query Optimizer creates filtered statistics on the same subset of rows specified for the filtered index. 如需篩選索引的詳細資訊,請參閱建立篩選的索引CREATE INDEX (Transact-SQL)For more information about filtered indexes, see Create Filtered Indexes and CREATE INDEX (Transact-SQL).

  2. 開啟 AUTO_CREATE_STATISTICS 時,查詢最佳化工具會針對查詢述詞中的單一資料行建立統計資料。The Query Optimizer creates statistics for single columns in query predicates when AUTO_CREATE_STATISTICS is on.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

對於大部分查詢而言,這兩種建立統計資料的方法可確保高品質的查詢計劃。不過,在少數情況下,您可以使用 CREATE STATISTICS 陳述式來建立其他統計資料,以便改善查詢計劃。For most queries, these two methods for creating statistics ensure a high-quality query plan; in a few cases, you can improve query plans by creating additional statistics with the CREATE STATISTICS statement. 這些額外的統計資料可以擷取查詢最佳化工具在建立索引或單一資料行的統計資料時無法說明的統計相互關聯。These additional statistics can capture statistical correlations that the Query Optimizer does not account for when it creates statistics for indexes or single columns. 您的應用程式可能會在資料表資料中具有其他統計相互關聯,如果它們計算成統計資料物件,就可讓查詢最佳化工具改善查詢計劃。Your application might have additional statistical correlations in the table data that, if calculated into a statistics object, could enable the Query Optimizer to improve query plans. 例如,資料列子集的篩選統計資料或查詢述詞資料行的多重資料行統計資料可能會改善查詢計劃。For example, filtered statistics on a subset of data rows or multicolumn statistics on query predicate columns might improve the query plan.

使用 CREATE STATISTICS 陳述式來建立統計資料時,我們建議您將 AUTO_CREATE_STATISTICS 選項保持開啟狀態,讓查詢最佳化工具能夠繼續例行地針對查詢述詞資料行建立單一資料行統計資料。When creating statistics with the CREATE STATISTICS statement, we recommend keeping the AUTO_CREATE_STATISTICS option on so that the Query Optimizer continues to routinely create single-column statistics for query predicate columns. 如需查詢述詞的詳細資訊,請參閱搜尋條件 (Transact-SQL)For more information about query predicates, see Search Condition (Transact-SQL).

當下列任何情況適用時,請考慮使用 CREATE STATISTICS 陳述式來建立統計資料:Consider creating statistics with the CREATE STATISTICS statement when any of the following applies:

  • Database EngineDatabase Engine Tuning Advisor 建議您建立統計資料。The Database EngineDatabase Engine Tuning Advisor suggests creating statistics.
  • 查詢述詞包含多個尚未存在相同索引中的相互關聯資料行。The query predicate contains multiple correlated columns that are not already in the same index.
  • 查詢會從資料子集中選取。The query selects from a subset of data.
  • 查詢具有遺失的統計資料。The query has missing statistics.

查詢述詞包含多個相互關聯的資料行Query Predicate contains multiple correlated columns

當查詢述詞包含多個具有跨資料行關聯性與相依性的資料行時,多個資料行的統計資料可能會改善查詢計劃。When a query predicate contains multiple columns that have cross-column relationships and dependencies, statistics on the multiple columns might improve the query plan. 多個資料行的統計資料包含跨資料行相互關聯統計資料 (稱為「密度」 ,而且這些統計資料不會在單一資料行統計資料中提供。Statistics on multiple columns contain cross-column correlation statistics, called densities, that are not available in single-column statistics. 當查詢結果相依於多個資料行之間的資料關聯性時,密度可以改善基數估計值。Densities can improve cardinality estimates when query results depend on data relationships among multiple columns.

如果資料行已經存在相同的索引中,就表示多重資料行統計資料物件已經存在,而且您不需要手動建立此物件。If the columns are already in the same index, the multicolumn statistics object already exists and it is not necessary to create it manually. 如果資料行尚未存在相同的索引中,您可以針對資料行建立索引或使用 CREATE STATISTICS 陳述式,藉以建立多重資料行統計資料。If the columns are not already in the same index, you can create multicolumn statistics by creating an index on the columns or by using the CREATE STATISTICS statement. 相較於統計資料物件而言,這種統計資料需要更多系統資源來維護索引。It requires more system resources to maintain an index than a statistics object. 如果應用程式不需要多重資料行索引,您就可以建立統計資料物件而不建立索引,藉以節省系統資源。If the application does not require the multicolumn index, you can economize on system resources by creating the statistics object without creating the index.

建立多重資料行統計資料時,統計資料物件定義中的資料行順序會影響建立基數估計值之密度的有效性。When creating multicolumn statistics, the order of the columns in the statistics object definition affects the effectiveness of densities for making cardinality estimates. 統計資料物件會將索引鍵資料行之每個前置詞的密度儲存在統計資料物件定義中。The statistics object stores densities for each prefix of key columns in the statistics object definition. 如需有關密度的詳細資訊,請參閱本頁面中的密度一節。For more information about densities, see Density section in this page.

若要建立對於基數估計值有用的密度,查詢述詞中的資料行必須與統計資料物件定義的其中一個資料行前置詞相符。To create densities that are useful for cardinality estimates, the columns in the query predicate must match one of the prefixes of columns in the statistics object definition. 例如,下列命令會針對 LastNameMiddleNameFirstName資料行建立多重資料行統計資料物件。For example, the following creates a multicolumn statistics object on the columns LastName, MiddleName, and FirstName.

USE AdventureWorks2012;  
GO  
IF EXISTS (SELECT name FROM sys.stats  
    WHERE name = 'LastFirst'  
    AND object_ID = OBJECT_ID ('Person.Person'))  
DROP STATISTICS Person.Person.LastFirst;  
GO  
CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);  
GO  

在這則範例中,統計資料物件 LastFirst 具有下列資料行前置詞的密度:(LastName)(LastName, MiddleName)(LastName, MiddleName, FirstName)In this example, the statistics object LastFirst has densities for the following column prefixes: (LastName), (LastName, MiddleName), and (LastName, MiddleName, FirstName). 此密度不適用於 (LastName, FirstName)The density is not available for (LastName, FirstName). 如果查詢使用 LastNameFirstName 而不使用 MiddleName,此密度就不適用於基數估計值。If the query uses LastName and FirstName without using MiddleName, the density is not available for cardinality estimates.

查詢會從資料子集中選取Query Selects from a subset of data

當查詢最佳化工具針對單一資料行和索引建立統計資料時,它就會針對所有資料列中的值建立統計資料。When the Query Optimizer creates statistics for single columns and indexes, it creates the statistics for the values in all rows. 當查詢從資料列的子集中選取,而且該資料列子集具有唯一的資料分佈時,篩選的統計資料就可以改善查詢計劃。When queries select from a subset of rows, and that subset of rows has a unique data distribution, filtered statistics can improve query plans. 您可以使用 CREATE STATISTICS 陳述式搭配 WHERE 子句來定義篩選述詞運算式,藉此建立篩選統計資料。You can create filtered statistics by using the CREATE STATISTICS statement with the WHERE clause to define the filter predicate expression.

例如,在使用 AdventureWorks2012AdventureWorks2012 時,Production.Product 資料表中的每個產品都屬於 Production.ProductCategory 資料表的四個類別目錄之一:Bikes、Components、Clothing 及 Accessories。For example, using AdventureWorks2012AdventureWorks2012, each product in the Production.Product table belongs to one of four categories in the Production.ProductCategory table: Bikes, Components, Clothing, and Accessories. 其中每個類別目錄都具有不同的重量資料分佈:腳踏車 (Bikes) 的重量範圍是從 13.77 到 30.0、元件 (Components) 的重量範圍是從 2.12 到 1050.00 且有些是 NULL 值、衣服 (Clothing) 的重量全部為 NULL,配件 (Accessories) 的重量也是 NULL。Each of the categories has a different data distribution for weight: bike weights range from 13.77 to 30.0, component weights range from 2.12 to 1050.00 with some NULL values, clothing weights are all NULL, and accessory weights are also NULL.

以 Bikes 為例,相較於在 Weight 資料行上具有完整資料表統計資料或不存在統計資料而言,針對所有腳踏車重量的篩選統計資料將能為查詢最佳化工具提供更精確的統計資料,而且可以改善查詢計劃品質。Using Bikes as an example, filtered statistics on all bike weights will provide more accurate statistics to the Query Optimizer and can improve the query plan quality compared with full-table statistics or nonexistent statistics on the Weight column. 雖然腳踏車重量資料行適合做為篩選的統計資料,但是不一定適合做為篩選的索引 (如果重量查閱的數目相當小的話)。The bike weight column is a good candidate for filtered statistics but not necessarily a good candidate for a filtered index if the number of weight lookups is relatively small. 篩選索引為查閱所提供的效能提升程度可能不會超過將篩選索引加入至資料庫的額外維護和儲存成本。The performance gain for lookups that a filtered index provides might not outweigh the additional maintenance and storage cost for adding a filtered index to the database.

下列陳述式會針對 Bikes 的所有子類別目錄建立 BikeWeights 篩選統計資料。The following statement creates the BikeWeights filtered statistics on all of the subcategories for Bikes. 篩選述詞運算式會使用比較 Production.ProductSubcategoryID IN (1,2,3)來列舉所有腳踏車子類別目錄,藉以定義腳踏車。The filtered predicate expression defines bikes by enumerating all of the bike subcategories with the comparison Production.ProductSubcategoryID IN (1,2,3). 此述詞無法使用 Bikes 類別目錄,因為它儲存在 Production.ProductCategory 資料表中,而且篩選運算式的所有資料行都必須位於相同的資料表中。The predicate cannot use the Bikes category name because it is stored in the Production.ProductCategory table, and all columns in the filter expression must be in the same table.

USE AdventureWorks2012;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

查詢最佳化工具可以使用 BikeWeights 篩選統計資料,來針對下列會選取所有重量超過 25 之腳踏車的查詢,改善其查詢計劃。The Query Optimizer can use the BikeWeights filtered statistics to improve the query plan for the following query that selects all of the bikes that weigh more than 25.

SELECT P.Weight AS Weight, S.Name AS BikeName  
FROM Production.Product AS P  
    JOIN Production.ProductSubcategory AS S   
    ON P.ProductSubcategoryID = S.ProductSubcategoryID  
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25  
ORDER BY P.Weight;  
GO  

查詢會識別遺失的統計資料Query identifies missing statistics

如果有錯誤或其他事件讓查詢最佳化工具無法建立統計資料,查詢最佳化工具會在不使用統計資料的情況下建立查詢計劃。If an error or other event prevents the Query Optimizer from creating statistics, the Query Optimizer creates the query plan without using statistics. 查詢最佳化工具會將統計資料標示為遺失,並且嘗試在下一次執行查詢時重新產生統計資料。The Query Optimizer marks the statistics as missing and attempts to regenerate the statistics the next time the query is executed.

當查詢的執行計畫是利用 SQL Server Management StudioSQL Server Management Studio以圖形顯示時,遺失的統計資料就會表示成警告 (資料表名稱為紅色)。Missing statistics are indicated as warnings (table name in red text) when the execution plan of a query is graphically displayed using SQL Server Management StudioSQL Server Management Studio. 此外,使用 SQL Server ProfilerSQL Server Profiler 來監視 Missing Column Statistics 事件類別會指出統計資料遺失的時間。Additionally, monitoring the Missing Column Statistics event class by using SQL Server ProfilerSQL Server Profiler indicates when statistics are missing. 如需詳細資訊,請參閱錯誤和警告事件類別目錄 (Database Engine)For more information, see Errors and Warnings Event Category (Database Engine).

如果統計資料已遺失,請執行下列步驟:If statistics are missing, perform the following steps:

如果唯讀資料庫或唯讀快照集上的統計資料遺漏或過時, Database EngineDatabase Engine 會在 tempdb中建立及維護暫時性統計資料。When statistics on a read-only database or read-only snapshot are missing or stale, the Database EngineDatabase Engine creates and maintains temporary statistics in tempdb. Database EngineDatabase Engine 建立暫時統計資料時,統計資料名稱會附加後置詞 _readonly_database_statistic,以便區分暫時統計資料與永久統計資料。When the Database EngineDatabase Engine creates temporary statistics, the statistics name is appended with the suffix _readonly_database_statistic to differentiate the temporary statistics from the permanent statistics. 後置詞 _readonly_database_statistic 會保留給由 SQL ServerSQL Server 產生的統計資料使用。The suffix _readonly_database_statistic is reserved for statistics generated by SQL ServerSQL Server. 暫時統計資料的指令碼可以在讀寫資料庫上建立和複製。Scripts for the temporary statistics can be created and reproduced on a read-write database. 已編寫指令碼時,Management StudioManagement Studio 會將統計資料名稱的後置詞從 _readonly_database_statistic 變更為 _readonly_database_statistic_scriptedWhen scripted, Management StudioManagement Studio changes the suffix of the statistics name from _readonly_database_statistic to _readonly_database_statistic_scripted.

只有 SQL ServerSQL Server 可以建立和更新暫時統計資料。Only SQL ServerSQL Server can create and update temporary statistics. 但是,您可以使用永久統計資料所使用的相同工具來刪除暫時統計資料及監控統計資料屬性:However, you can delete temporary statistics and monitor statistics properties using the same tools that you use for permanent statistics:

  • 使用 DROP STATISTICS 陳述式刪除暫時統計資料。Delete temporary statistics using the DROP STATISTICS statement.
  • 使用 sys.statssys.stats_columns 目錄檢視來監視統計資料。Monitor statistics using the sys.stats and sys.stats_columns catalog views. sys_stats 包含 is_temporary 資料行,以指示哪些統計資料為永久性及哪些統計資料為暫時性。sys_stats includes the is_temporary column, to indicate which statistics are permanent and which are temporary.

因為暫時統計資料會儲存在 tempdb中,所以重新啟動 SQL ServerSQL Server 服務會導致所有暫時統計資料消失。Because temporary statistics are stored in tempdb, a restart of the SQL ServerSQL Server service causes all temporary statistics to disappear.

何時更新統計資料When to update statistics

查詢最佳化工具會判斷統計資料可能過期的時間,然後在查詢計劃需要它們時進行更新。The Query Optimizer determines when statistics might be out-of-date and then updates them when they are needed for a query plan. 在某些情況下,您可以讓統計資料的更新頻率高於 AUTO_UPDATE_STATISTICS 開啟時的更新頻率,藉以改善查詢計劃,因而改善查詢效能。In some cases you can improve the query plan and therefore improve query performance by updating statistics more frequently than occur when AUTO_UPDATE_STATISTICS is on. 您可以使用 UPDATE STATISTICS 陳述式或 sp_updatestats 預存程序來更新統計資料。You can update statistics with the UPDATE STATISTICS statement or the stored procedure sp_updatestats.

更新統計資料可確保查詢使用最新的統計資料進行編譯。Updating statistics ensures that queries compile with up-to-date statistics. 不過,更新統計資料會導致查詢重新編譯。However, updating statistics causes queries to recompile. 我們建議您不要太頻繁地更新統計資料,因為改善查詢計劃與重新編譯查詢所花費的時間之間具有效能權衡取捨。We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. 特定的權衡取捨完全取決於您的應用程式。The specific tradeoffs depend on your application.

使用 UPDATE STATISTICS 或 sp_updatestats 來更新統計資料時,我們建議您將 AUTO_UPDATE_STATISTICS 保持設定為開啟,讓查詢最佳化工具能夠繼續例行地更新統計資料。When updating statistics with UPDATE STATISTICS or sp_updatestats, we recommend keeping AUTO_UPDATE_STATISTICS set to ON so that the Query Optimizer continues to routinely update statistics. 如需如何針對資料行、索引、資料表或索引檢視表更新統計資料的詳細資訊,請參閱 UPDATE STATISTICS (Transact-SQL)For more information about how to update statistics on a column, an index, a table, or an indexed view, see UPDATE STATISTICS (Transact-SQL). 如需如何針對資料庫中所有使用者定義和內部資料表更新統計資料的詳細資訊,請參閱預存程序 sp_updatestats (Transact-SQL)For information about how to update statistics for all user-defined and internal tables in the database, see the stored procedure sp_updatestats (Transact-SQL).

若要判斷上次更新統計資料的時間,請使用 sys.dm_db_stats_propertiesSTATS_DATE 函式。To determine when statistics were last updated, use the sys.dm_db_stats_properties or STATS_DATE functions.

在下列狀況中,請考慮更新統計資料:Consider updating statistics for the following conditions:

  • 查詢執行時間很慢。Query execution times are slow.
  • 插入作業針對遞增或遞減索引鍵資料行進行。Insert operations occur on ascending or descending key columns.
  • 在維護作業之後。After maintenance operations.

查詢執行時間很慢Query execution times are slow

如果查詢回應時間很慢或無法預測,請先確定查詢具有最新的統計資料,然後再執行其他疑難排解步驟。If query response times are slow or unpredictable, ensure that queries have up-to-date statistics before performing additional troubleshooting steps.

插入作業在遞增或遞減索引鍵資料行上發生Insert operations occur on ascending or descending key columns

遞增或遞減索引鍵資料行 (例如 IDENTITY 或即時時間戳記資料行) 上的統計資料所需的統計資料更新頻率,可能會比查詢最佳化工具所執行的更新頻率更高。Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might require more frequent statistics updates than the Query Optimizer performs. 插入作業會將新的值附加至遞增或遞減資料行。Insert operations append new values to ascending or descending columns. 所加入的資料列數目可能會太小,而無法觸發統計資料更新。The number of rows added might be too small to trigger a statistics update. 如果統計資料不是最新的,而且查詢會從最近加入的資料列中選取,則目前的統計資料將不會具有這些新值的基數估計值。If statistics are not up-to-date and queries select from the most recently added rows, the current statistics will not have cardinality estimates for these new values. 這可能會導致基數估計值不精確以及查詢效能緩慢。This can result in inaccurate cardinality estimates and slow query performance.

例如,如果統計資料沒有更新成包含最新銷售訂單日期的基數估計值,則從最新銷售訂單日期中選取的查詢就會具有不精確的基數估計值。For example, a query that selects from the most recent sales order dates will have inaccurate cardinality estimates if the statistics are not updated to include cardinality estimates for the most recent sales order dates.

在維護作業之後After maintenance operations

在執行變更資料分佈的維護程序 (例如截斷資料表或針對大部分的資料列執行大量插入) 之後,請考慮更新統計資料。Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. 這樣做可在查詢等候自動統計資料更新時,避免未來查詢處理產生延遲。This can avoid future delays in query processing while queries wait for automatic statistics updates.

重建、重組或重新組織索引等作業都不會變更資料的分佈。Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. 因此,在執行 ALTER INDEX REBUILDDBCC DBREINDEXDBCC INDEXDEFRAGALTER INDEX REORGANIZE 作業之後,您就不需要更新統計資料。Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. 當您使用 ALTER INDEX REBUILD 或 DBCC DBREINDEX 來重建資料表或檢視表的索引時,查詢最佳化工具就會更新統計資料。不過,這種統計資料更新是重新建立索引的副產品。The Query Optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however this statistics update is a byproduct of re-creating the index. 在 DBCC INDEXDEFRAG 或 ALTER INDEX REORGANIZE 作業之後,查詢最佳化工具則不會更新統計資料。The Query Optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations.

提示

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU4 開始,請使用 CREATE STATISTICS (Transact-SQL)UPDATE STATISTICS (Transact-SQL) 的 PERSIST_SAMPLE_PERCENT 選項,來針對後續不會明確指定取樣百分比的統計資料更新,設定並保留特定的取樣百分比。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 CU4, use the PERSIST_SAMPLE_PERCENT option of CREATE STATISTICS (Transact-SQL) or UPDATE STATISTICS (Transact-SQL), to set and retain a specific sampling percentage for subsequent statistic updates that do not explicitly specify a sampling percentage.

自動索引與統計資料管理Automatic index and statistics management

利用自適性索引重組等解決方案,為一或多個資料庫自動管理索引重組以及統計資料更新。Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. 這項程序會根據索引分散程度與其他參數,自動選擇要進行重建或是重新組織索引,並以線性閾值更新統計資料。This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

有效使用統計資料的查詢Queries that use statistics effectively

某些查詢實作 (例如查詢述詞中的區域變數和複雜運算式) 可能會導致次佳的查詢計劃。Certain query implementations, such as local variables and complex expressions in the query predicate, can lead to suboptimal query plans. 不過,遵循查詢設計指導方針來有效使用統計資料有助於避免這種情況發生。Following query design guidelines for using statistics effectively can help to avoid this. 如需查詢述詞的詳細資訊,請參閱搜尋條件 (Transact-SQL)For more information about query predicates, see Search Condition (Transact-SQL).

您可以套用有效使用統計資料的查詢設計指導方針來改善查詢述詞中使用之運算式、變數和函數的「基數估計值」 ,藉以改善查詢計劃。You can improve query plans by applying query design guidelines that use statistics effectively to improve cardinality estimates for expressions, variables, and functions used in query predicates. 當查詢最佳化工具不知道運算式、變數或函式的值時,它就不知道要在長條圖中查閱哪個值,因此無法從長條圖中擷取最佳的基數估計值。When the Query Optimizer does not know the value of an expression, variable, or function, it does not know which value to lookup in the histogram and therefore cannot retrieve the best cardinality estimate from the histogram. 此時,查詢最佳化工具會改為以長條圖中所有取樣資料列之每個相異值的平均資料列數目做為基數估計值的基礎。Instead, the Query Optimizer bases the cardinality estimate on the average number of rows per distinct value for all of the sampled rows in the histogram. 這樣會導致次佳的基數估計值,而且可能會損及查詢效能。This leads to suboptimal cardinality estimates and can hurt query performance. 如需長條圖的詳細資訊,請參閱本頁面中的長條圖一節,或是 sys.dm_db_stats_histogramFor more information about histograms, see histogram section in this page or sys.dm_db_stats_histogram.

下列指導方針描述的是如何撰寫查詢,以便透過改善基數估計值,改善查詢計劃。The following guidelines describe how to write queries to improve query plans by improving cardinality estimates.

改善運算式的基數估計值Improving cardinality estimates for expressions

若要改善運算式的基數估計值,請遵循下列指導方針:To improve cardinality estimates for expressions, follow these guidelines:

  • 您應該盡可能簡化含有常數的運算式。Whenever possible, simplify expressions with constants in them. 在判斷基數估計值之前,查詢最佳化工具不會評估包含常數的所有函式和運算式。The Query Optimizer does not evaluate all functions and expressions containing constants prior to determining cardinality estimates. 例如,請將運算式 ABS(-100) 簡化為 100For example, simplify the expression ABS(-100) to 100.

  • 如果運算式使用多個變數,請考慮建立運算式的計算資料行,然後再針對計算資料行建立統計資料或索引。If the expression uses multiple variables, consider creating a computed column for the expression and then create statistics or an index on the computed column. 例如,如果您建立了 WHERE PRICE + Tax > 100 運算式的計算資料行, Price + Tax查詢述詞可能會具有較佳的基數估計值。For example, the query predicate WHERE PRICE + Tax > 100 might have a better cardinality estimate if you create a computed column for the expression Price + Tax.

改善變數和函式的基數估計值Improving cardinality estimates for variables and functions

若要改善變數和函數的基數估計值,請遵循下列指導方針:To improve the cardinality estimates for variables and functions, follow these guidelines:

  • 如果查詢述詞使用區域變數,請考慮將查詢重新撰寫成使用參數而非區域變數。If the query predicate uses a local variable, consider rewriting the query to use a parameter instead of a local variable. 當查詢最佳化工具建立查詢執行計畫時,並無法得知區域變數的值。The value of a local variable is not known when the Query Optimizer creates the query execution plan. 當查詢使用參數時,查詢最佳化工具就會使用傳遞給預存程序之第一個實際參數值的基數估計值。When a query uses a parameter, the Query Optimizer uses the cardinality estimate for the first actual parameter value that is passed to the stored procedure.

  • 請考慮使用標準資料表或暫存資料表來保存多重陳述式資料表值函式 (mstvf) 的結果。Consider using a standard table or temporary table to hold the results of multi-statement table-valued functions (mstvf). 查詢最佳化工具不會針對多重陳述式資料表值函式建立統計資料。The Query Optimizer does not create statistics for multi-statement table-valued functions. 透過這種方法,查詢最佳化工具就可以建立資料表資料行的統計資料,然後使用它們來建立較佳的查詢計劃。With this approach the Query Optimizer can create statistics on the table columns and use them to create a better query plan.

  • 請考慮使用標準資料表或暫存資料表當做資料表變數的取代項目。Consider using a standard table or temporary table as a replacement for table variables. 查詢最佳化工具不會針對資料表變數建立統計資料。The Query Optimizer does not create statistics for table variables. 透過這種方法,查詢最佳化工具就可以建立資料表資料行的統計資料,然後使用它們來建立較佳的查詢計劃。With this approach the Query Optimizer can create statistics on the table columns and use them to create a better query plan. 當您在判斷要使用暫存資料表或資料表變數時,存在權衡取捨。在預存程序中使用的資料表變數會讓預存程序重新編譯的次數比暫存資料表更少。There are tradeoffs in determining whether to use a temporary table or a table variable; Table variables used in stored procedures cause fewer recompilations of the stored procedure than temporary tables. 根據應用程式而定,使用暫存資料表來取代資料表變數可能不會改善效能。Depending on the application, using a temporary table instead of a table variable might not improve performance.

  • 如果預存程序包含使用傳入參數的查詢,請避免在查詢中使用之前,變更預存程序中的參數值。If a stored procedure contains a query that uses a passed-in parameter, avoid changing the parameter value within the stored procedure before using it in the query. 查詢的基數估計值是以傳入參數而非更新的值為基礎。The cardinality estimates for the query are based on the passed-in parameter value and not the updated value. 若要避免變更參數值,您可以將查詢重新撰寫成使用兩個預存程序。To avoid changing the parameter value, you can rewrite the query to use two stored procedures.

    例如,下列預存程序 Sales.GetRecentSales 會在 @date 為 NULL 時變更 @date 參數的值。For example, the following stored procedure Sales.GetRecentSales changes the value of the parameter @date when @date is NULL.

    USE AdventureWorks2012;  
    GO  
    IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL  
        DROP PROCEDURE Sales.GetRecentSales;  
    GO  
    CREATE PROCEDURE Sales.GetRecentSales (@date datetime)  
    AS BEGIN  
        IF @date IS NULL  
            SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))  
        SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d  
        WHERE h.SalesOrderID = d.SalesOrderID  
        AND h.OrderDate > @date  
    END  
    GO  
    

    如果預存程序 Sales.GetRecentSales 的第一次呼叫傳遞 NULL 給 @date 參數,查詢最佳化工具就會使用 @date = NULL 的基數估計值來編譯此預存程序,即使沒有使用 @date = NULL 來呼叫查詢述詞也一樣。If the first call to the stored procedure Sales.GetRecentSales passes a NULL for the @date parameter, the Query Optimizer will compile the stored procedure with the cardinality estimate for @date = NULL even though the query predicate is not called with @date = NULL. 這個基數估計值可能會與實際查詢結果中的資料列數目具有大幅差異。This cardinality estimate might be significantly different than the number of rows in the actual query result. 因此,查詢最佳化工具可能會選擇到次佳的查詢計劃。As a result, the Query Optimizer might choose a suboptimal query plan. 為了協助避免這種情況發生,您可以將此預存程序重新撰寫成兩個程序,如下所示:To help avoid this, you can rewrite the stored procedure into two procedures as follows:

    USE AdventureWorks2012;  
    GO  
    IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL  
        DROP PROCEDURE Sales.GetNullRecentSales;  
    GO  
    CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime)  
    AS BEGIN  
        IF @date is NULL  
            SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))  
        EXEC Sales.GetNonNullRecentSales @date;  
    END  
    GO  
    IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL  
        DROP PROCEDURE Sales.GetNonNullRecentSales;  
    GO  
    CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime)  
    AS BEGIN  
        SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d  
        WHERE h.SalesOrderID = d.SalesOrderID  
        AND h.OrderDate > @date  
    END  
    GO  
    

使用查詢提示來改善基數估計值Improving cardinality estimates with query hints

若要改善區域變數的基數估計值,您可以使用 OPTIMIZE FOR <value>OPTIMIZE FOR UNKNOWN 查詢提示搭配 RECOMPILE。To improve cardinality estimates for local variables, you can use the OPTIMIZE FOR <value> or OPTIMIZE FOR UNKNOWN query hints with RECOMPILE. 如需詳細資訊,請參閱查詢提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

對於某些應用程式而言,每次執行查詢都重新編譯查詢可能會花費太多時間。For some applications, recompiling the query each time it executes might take too much time. 即使您沒有使用 RECOMPILE 選項,OPTIMIZE FOR 查詢提示仍然有所幫助。The OPTIMIZE FOR query hint can help even if you don't use the RECOMPILE option. 例如,您可以將 OPTIMIZE FOR 選項加入至預存程序 Sales.GetRecentSales,以便指定特定日期。For example, you could add an OPTIMIZE FOR option to the stored procedure Sales.GetRecentSales to specify a specific date. 下列範例會將 OPTIMIZE FOR 選項加入至 Sales.GetRecentSales 程序。The following example adds the OPTIMIZE FOR option to the Sales.GetRecentSales procedure.

USE AdventureWorks2012;  
GO  
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.GetRecentSales;  
GO  
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)  
AS BEGIN  
    IF @date is NULL  
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))  
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d  
    WHERE h.SalesOrderID = d.SalesOrderID  
    AND h.OrderDate > @date  
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))  
END;  
GO  

使用計劃指南來改善基數估計值Improving cardinality estimates with Plan Guides

對於某些應用程式而言,查詢設計指導方針可能不適用,因為您無法變更查詢或者使用 RECOMPILE 查詢提示可能會導致重新編譯次數太多。For some applications, query design guidelines might not apply because you cannot change the query or using the RECOMPILE query hint might be cause too many recompiles. 此時,您可以使用計畫指南來指定其他提示 (例如 USE PLAN),以便控制查詢的行為,同時向應用程式廠商調查應用程式變更。You can use plan guides to specify other hints, such as USE PLAN, to control the behavior of the query while investigating application changes with the application vendor. 如需有關計畫指南的詳細資訊,請參閱 計畫指南For more information about plan guides, see Plan Guides.

另請參閱See Also

CREATE STATISTICS (Transact-SQL) CREATE STATISTICS (Transact-SQL)
UPDATE STATISTICS (Transact-SQL) UPDATE STATISTICS (Transact-SQL)
sp_updatestats (Transact-SQL) sp_updatestats (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL) DBCC SHOW_STATISTICS (Transact-SQL)
ALTER DATABASE SET 選項 (Transact-SQL) ALTER DATABASE SET Options (Transact-SQL)
DROP STATISTICS (Transact-SQL) DROP STATISTICS (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
建立篩選的索引 Create Filtered Indexes
控制 SQL Server 中的 Autostat (AUTO_UPDATE_STATISTICS) 行為 (機器翻譯)Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server
STATS_DATE (Transact-SQL) STATS_DATE (Transact-SQL)
sys.dm_db_stats_properties (Transact-SQL) sys.dm_db_stats_properties (Transact-SQL)
sys.dm_db_stats_histogram (Transact-SQL)sys.dm_db_stats_histogram (Transact-SQL)
sys.statssys.stats
sys.stats_columns (Transact-SQL) sys.stats_columns (Transact-SQL)
自適性索引重組Adaptive Index Defrag