使用統計資料來改善查詢效能

對於從定義完善的資料子集中選取的查詢而言，篩選的統計資料可以改善查詢效能。篩選的統計資料會使用篩選述詞來選取統計資料中所含的資料子集。設計完善的篩選統計資料可以改善查詢執行計畫 (相較於完整資料表統計資料而言)。如需有關篩選述詞的詳細資訊，請參閱＜CREATE STATISTICS (Transact-SQL)＞。如需有關何時建立篩選統計資料的詳細資訊，請參閱本主題中的「判斷要建立統計資料的時機」一節。若需個案研究的詳細資訊，請參閱 SQLCAT 網站中配合資料分割資料表使用已篩選的統計資料部落格項目。

開啟自動建立統計資料選項 AUTO_CREATE_STATISTICS 時，查詢最佳化工具就會視需要針對查詢述詞中的個別資料行建立統計資料，以便改善查詢計畫的基數估計值。這些單一資料行統計資料是針對在現有統計資料物件中尚未具有長條圖的資料行建立的。

您可以使用下列查詢來判斷查詢最佳化工具是否已經針對查詢述詞資料行建立了統計資料。它會查詢目錄檢視 sys.stats 和 sys.stats_columns，以便傳回資料庫物件名稱、資料行名稱以及具有單一資料行統計資料之所有資料行的統計資料名稱。當查詢最佳化工具由於使用 AUTO_CREATE_STATISTICS 選項而針對單一資料行產生統計資料時，統計資料名稱就會以 _WA 為開頭。

USE AdventureWorks;
GO
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 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_CREATE_STATISTICS 選項不會判斷系統是否針對索引建立了統計資料。這個選項也不會產生篩選的統計資料。它會嚴格套用至完整資料表的單一資料行統計資料。

開啟自動更新統計資料選項 AUTO_UPDATE_STATISTICS 時，查詢最佳化工具就會判斷統計資料可能過期的時間，然後在查詢使用統計資料時更新它們。當插入、更新、刪除或合併作業變更資料表或索引檢視表中的資料分佈之後，統計資料就會變成過期。查詢最佳化工具會計算自從上次更新統計資料以來資料修改的次數，並且比較修改次數與臨界值，藉以判斷統計資料可能過期的時間。此臨界值是以資料表或索引檢視表中的資料列數目為基礎。

在編譯查詢之前以及執行快取查詢計畫之前，查詢最佳化工具會檢查是否有過期的統計資料。在編譯查詢之前，查詢最佳化工具會使用查詢述詞中的資料行、資料表和索引檢視表來判斷哪些統計資料可能已過期。在執行快取查詢計畫之前，Database Engine 會確認查詢計畫是否參考最新的統計資料。

AUTO_UPDATE_STATISTICS 選項會套用至針對索引所建立的統計資料物件、查詢述詞中的單一資料行，以及使用 CREATE STATISTICS 陳述式所建立的統計資料。此外，這個選項也會套用至篩選的統計資料。

自動建立統計資料選項 AUTO_CREATE_STATISTICS 和自動更新統計資料選項 AUTO_UPDATE_STATISTICS 預設已開啟，而且我們建議您針對大部分的使用者資料庫使用此預設值。您可以使用下列 SELECT 陳述式來檢視所有使用者資料庫之這些選項的目前值：

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;
GO

下列範例會將 AdventureWorks2008R2 資料庫的 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 設定為 ON：

USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO

如需有關如何設定這些統計資料選項的詳細資訊，請參閱＜ALTER DATABASE SET 選項 (Transact-SQL)＞。

開啟 AUTO_UPDATE_STATISTICS 時，您可以根據應用程式的要求，覆寫資料庫範圍統計資料更新行為並且針對個別資料表、索引或資料行關閉自動統計資料更新。開啟 AUTO_UPDATE_STATISTICS 時，您可以用下列方式，針對個別資料表、索引或資料行停用並重新啟用自動統計資料更新：

• 使用 sp_autostats 系統預存程序。這個預存程序可以針對資料表或索引停用或重新啟用統計資料更新。

• 使用 UPDATE STATISTICS 陳述式來指定 NORECOMPUTE 選項。若要重新啟用統計資料更新，請重新執行不含 NORECOMPUTE 選項的 UPDATE STATISTICS。

• 使用 CREATE STATISTICS 陳述式來指定 NORECOMPUTE 選項。若要重新啟用統計資料更新，請使用 DROP STATISTICS 來移除統計資料，然後再執行不含 NORECOMPUTE 選項的 CREATE STATISTICS。

• 使用 CREATE INDEX 陳述式來指定 STATISTICS_NORECOMPUTE 選項。若要重新啟用統計資料更新，您可以執行含有 STATISTICS_NORECOMPUTE = OFF 的 ALTER INDEX。

關閉 AUTO_UPDATE_STATISTICS 時，您就無法針對個別資料表、索引或資料行，將自動更新設定為開啟。重新啟用自動統計資料更新就會還原 AUTO_UPDATE_STATISTICS 選項所指定的行為。如果已關閉 AUTO_UPDATE_STATISTICS 選項，系統就不會進行統計資料更新。

統計資料更新可以是同步 (預設值) 或非同步。使用同步統計資料更新時，查詢一律會使用最新的統計資料進行編譯和執行。如果統計資料已過期，查詢最佳化工具就會先等候更新的統計資料，然後再編譯並執行查詢。使用非同步統計資料更新時，查詢就會使用現有的統計資料進行編譯，即使現有的統計資料已過期也一樣。如果查詢進行編譯時統計資料已過期，查詢最佳化工具可能會選擇次佳查詢計畫。在非同步更新完成之後進行編譯的查詢將會從使用更新的統計資料中獲益。

資料庫範圍非同步統計資料更新選項 AUTO_UPDATE_STATISTICS_ASYNC 會決定查詢最佳化工具要使用同步或非同步統計資料更新。根據預設，非同步統計資料更新選項處於關閉狀態，而且查詢最佳化工具會以同步方式更新統計資料。AUTO_UPDATE_STATISTICS_ASYNC 選項會套用至針對索引所建立的統計資料物件、查詢述詞中的單一資料行，以及使用 CREATE STATISTICS 陳述式所建立的統計資料。

您可以使用下列命令來檢視所有資料庫的自動更新非同步選項：

SELECT name AS "Name", 
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;
GO

在下列狀況中，請考慮使用同步統計資料：

• 您要執行變更資料分佈的作業，例如截斷資料表或針對大部分的資料列執行大量更新。如果您沒有在完成此作業之後更新統計資料，使用同步統計資料將可在針對變更的資料執行查詢之前，確保統計資料處於最新狀態。

在下列狀況中，請考慮使用非同步統計資料來達到更可預測的查詢回應時間：

• 您的應用程式經常會執行相同的查詢、相似的查詢或相似的快取查詢計畫。相較於使用同步統計資料更新而言，使用非同步統計資料更新可能會讓您的查詢回應時間更可預測，因為查詢最佳化工具不需要等候最新的統計資料，就可以執行傳入的查詢。這樣會避免延遲某些查詢，但無法避免延遲其他查詢。如需有關尋找相似查詢的詳細資訊，請參閱＜使用查詢和查詢計畫雜湊來尋找並微調類似的查詢＞。

• 您的應用程式遇到等候更新統計資料之一或多個查詢所造成的用戶端要求逾時。在某些情況下，等候同步統計資料可能會造成具有彙總逾時的應用程式失敗。

Database Engine Tuning Advisor 是一種工具，可針對一或多個資料庫分析工作負載的效能影響。它會提供改善效能的建議事項 (例如，建議要建立的索引)，而且可能會建議您使用 CREATE STATISTICS 來建立查詢最佳化統計資料。您應該遵循這個建議事項。如需有關 Database Engine Tuning Advisor 的詳細資訊，請參閱＜微調實體資料庫設計＞。

當查詢述詞包含多個具有跨資料行關聯性與相依性的資料行時，多個資料行的統計資料可能會改善查詢計畫。多個資料行的統計資料包含跨資料行相互關聯統計資料 (稱為「密度」(Density))，而且這些統計資料不會在單一資料行統計資料中提供。當查詢結果相依於多個資料行之間的資料關聯性時，密度可以改善基數估計值。

如果資料行已經存在相同的索引中，就表示多重資料行統計資料物件已經存在，而且您不需要手動建立此物件。如果資料行尚未存在相同的索引中，您可以針對資料行建立索引或使用 CREATE STATISTICS 陳述式，藉以建立多重資料行統計資料。相較於統計資料物件而言，這種統計資料需要更多系統資源來維護索引。如果應用程式不需要多重資料行索引，您就可以建立統計資料物件而不建立索引，藉以節省系統資源。

建立多重資料行統計資料時，統計資料物件定義中的資料行順序會影響建立基數估計值之密度的有效性。統計資料物件會將索引鍵資料行之每個前置詞的密度儲存在統計資料物件定義中。如需有關密度的詳細資訊，請參閱＜DBCC SHOW_STATISTICS (Transact-SQL)＞。

若要建立對於基數估計值有用的密度，查詢述詞中的資料行必須與統計資料物件定義的其中一個資料行前置詞相符。例如，下列命令會針對 LastName、MiddleName 和 FirstName 資料行建立多重資料行統計資料物件。

USE AdventureWorks2008R2;
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)。此密度不適用於 (LastName, FirstName)。如果查詢使用 LastName 和 FirstName 而不使用 MiddleName，此密度就不適用於基數估計值。

當查詢最佳化工具針對單一資料行和索引建立統計資料時，它就會針對所有資料列中的值建立統計資料。當查詢從資料列的子集中選取，而且該資料列子集具有唯一的資料分佈時，篩選的統計資料就可以改善查詢計畫。您可以使用 CREATE STATISTICS 陳述式搭配 WHERE 子句來定義篩選述詞運算式，藉此建立篩選統計資料。

例如，在使用 AdventureWorks2008R2 時，Production.Product 資料表中的每個產品都屬於 Production.ProductCategory 資料表的其中一個類別目錄：Bikes、Components、Clothing 及 Accessories。其中每個類別目錄都具有不同的重量資料分佈：腳踏車 (Bikes) 的重量範圍是從 13.77 到 30.0、元件 (Components) 的重量範圍是從 2.12 到 1050.00 且有些是 NULL 值、衣服 (Clothing) 的重量全部為 NULL，配件 (Accessories) 的重量也是 NULL。

以 Bikes 為例，相較於 Weight 資料行的完整資料表統計資料或不存在的統計資料而言，所有腳踏車重量的篩選統計資料將提供更精確的統計資料給查詢最佳化工具，而且可以改善查詢計畫品質。雖然腳踏車重量資料行適合做為篩選的統計資料，但是不一定適合做為篩選的索引 (如果重量查閱的數目相當小的話)。篩選索引為查閱所提供的效能提升程度可能不會超過將篩選索引加入至資料庫的額外維護和儲存成本。

下列陳述式會針對 Bikes 的所有子類別目錄建立 BikeWeights 篩選統計資料。篩選述詞運算式會使用比較 Production.ProductSubcategoryID IN (1,2,3) 來列舉所有腳踏車子類別目錄，藉以定義腳踏車。此述詞無法使用 Bikes 類別目錄，因為它儲存在 Production.ProductCategory 資料表中，而且篩選運算式的所有資料行都必須位於相同的資料表中。

USE AdventureWorks2008R2;
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 的腳踏車)。

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

如果錯誤或其他事件讓查詢最佳化工具無法建立統計資料，查詢最佳化工具就會建立查詢計畫，但不使用統計資料。查詢最佳化工具會將統計資料標示為遺失，並且嘗試在下一次執行查詢時重新產生統計資料。

當查詢的執行計畫是利用 SQL Server Management Studio 以圖形顯示時，遺失的統計資料就會表示成警告 (資料表名稱為紅色)。如需詳細資訊，請參閱＜顯示圖形執行計畫 (SQL Server Management Studio)＞。此外，使用 SQL Server Profiler 來監視 Missing Column Statistics 事件類別會指出統計資料遺失的時間。如需詳細資訊，請參閱＜Errors and Warnings 事件類別目錄 (Database Engine)＞。

如果統計資料已遺失，請執行下列步驟：

• 確認已開啟 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS。

• 確認資料庫不是唯讀的。如果資料庫是唯讀的，查詢最佳化工具就無法儲存統計資料。

• 使用 CREATE STATISTICS 陳述式來建立遺失的統計資料。

如果查詢回應時間很慢或無法預測，請先確定查詢具有最新的統計資料，然後再執行其他疑難排解步驟。如需有關疑難排解查詢執行緩慢的詳細資訊，請參閱＜分析執行緩慢之查詢的檢查清單＞。

遞增或遞減索引鍵資料行 (例如 IDENTITY 或即時時間戳記資料行) 之統計資料所需的統計資料更新頻率可能會比查詢最佳化工具所執行的更新頻率更高。插入作業會將新的值附加至遞增或遞減資料行。所加入的資料列數目可能會太小，而無法觸發統計資料更新。如果統計資料不是最新的，而且查詢會從最近加入的資料列中選取，則目前的統計資料將不會具有這些新值的基數估計值。這可能會導致基數估計值不精確以及查詢效能緩慢。

例如，如果統計資料沒有更新成包含最新銷售訂單日期的基數估計值，則從最新銷售訂單日期中選取的查詢就會具有不精確的基數估計值。

在執行變更資料分佈的維護程序 (例如截斷資料表或針對大部分的資料列執行大量插入) 之後，請考慮更新統計資料。這樣做可在查詢等候自動統計資料更新時，避免未來查詢處理產生延遲。

請勿在重建、重組或重新組織索引等作業之後更新統計資料。這些作業不會變更資料的分佈，也不會影響統計資料。不過，請注意，當您使用 ALTER INDEX REBUILD 或 DBCC DBREINDEX 來重建資料表或檢視表的索引時，查詢最佳化工具就會更新統計資料。這是這些作業之內部實作的副產品，但是無法完成，因為統計資料需要更新。查詢最佳化工具不會在重組或重新組織作業之後更新統計資料，因為這些作業不會卸除並重新建立索引。

若要改善運算式的基數估計值，請遵循下列指導方針：

• 您應該盡可能簡化含有常數的運算式。在判斷基數估計值之前，查詢最佳化工具不會評估包含常數的所有函數和運算式。例如，請簡化運算式 ABS(-100) to 100。

• 如果運算式使用多個變數，請考慮建立運算式的計算資料行，然後再針對計算資料行建立統計資料或索引。例如，如果您建立了 Price + Tax 運算式的計算資料行，WHERE PRICE + Tax > 100 查詢述詞可能會具有較佳的基數估計值。

若要改善變數和函數的基數估計值，請遵循下列指導方針：

• 如果查詢述詞使用區域變數，請考慮將查詢重新撰寫成使用參數而非區域變數。當查詢最佳化工具建立查詢執行計畫時，無法得知區域變數的值。當查詢使用參數時，查詢最佳化工具就會使用傳遞給預存程序之第一個實際參數值的基數估計值。

• 請考慮使用標準資料表或暫存資料表來保存多重陳述式資料表值函式的結果。查詢最佳化工具不會針對多重陳述式資料表值函式建立統計資料。透過這種方法，查詢最佳化工具就可以建立資料表資料行的統計資料，然後使用它們來建立較佳的查詢計畫。如需有關多重陳述式資料表值函式的詳細資訊，請參閱＜函數類型＞。

• 請考慮使用標準資料表或暫存資料表當做資料表變數的取代項目。查詢最佳化工具不會針對資料表變數建立統計資料。透過這種方法，查詢最佳化工具就可以建立資料表資料行的統計資料，然後使用它們來建立較佳的查詢計畫。當您在判斷要使用暫存資料表或資料表變數時，存在權衡取捨。在預存程序中使用的資料表變數會讓預存程序重新編譯的次數比暫存資料表更少。根據應用程式而定，使用暫存資料表來取代資料表變數可能不會改善效能。

• 如果預存程序包含使用傳入參數的查詢，請避免在查詢中使用之前，變更預存程序中的參數值。查詢的基數估計值是以傳入參數而非更新的值為基礎。若要避免變更參數值，您可以將查詢重新撰寫成使用兩個預存程序。

  例如，當 @date is NULL 時，下列預存程序 Sales.GetRecentSales 就會變更 @date 參數的值。

  USE AdventureWorks2008R2;
  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 來呼叫查詢述詞也一樣。這個基數估計值可能會與實際查詢結果中的資料列數目具有大幅差異。因此，查詢最佳化工具可能會選擇次佳查詢計畫。為了協助避免這種情況發生，您可以將此預存程序重新撰寫成兩個程序，如下所示：

  USE AdventureWorks2008R2;
  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
  

若要改善區域變數的基數估計值，您可以使用 OPTIMIZE FOR 或 OPTIMIZE FOR UNKNOWN 查詢提示搭配 RECOMPILE。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

對於某些應用程式而言，每次執行查詢都重新編譯查詢可能會花費太多時間。即使您沒有使用 RECOMPILE 選項，OPTIMIZER FOR 查詢提示仍然有所幫助。例如，您可以將 OPTIMIZER FOR 選項加入至預存程序 Sales.GetRecentSales，以便指定特定日期。下列範例會將 OPTIMIZE FOR 選項加入至 Sales.GetRecentSales 程序。

USE AdventureWorks2008R2;
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

對於某些應用程式而言，查詢設計指導方針可能不適用，因為您無法變更查詢或者使用 RECOMPILE 查詢提示可能會導致重新編譯次數太多。此時，您可以使用計畫指南來指定其他提示 (例如 USE PLAN)，以便控制查詢的行為，同時向應用程式廠商調查應用程式變更。如需有關計畫指南的詳細資訊，請參閱＜使用計畫指南對已部署應用程式中的查詢進行最佳化＞。