針對受查詢優化器逾時影響的慢速查詢進行疑難解答

發行項
12/14/2024

適用於：SQL Server

本文介紹優化器逾時、如何影響查詢效能，以及如何優化效能。

什麼是優化器逾時？

SQL Server 使用以成本為基礎的 查詢優化器 （QO）。如需 QO 的相關信息，請參閱查詢處理架構指南。成本型查詢優化器會在建置及評估多個查詢計劃之後，選取成本最低的查詢執行計劃。相較於查詢執行，SQL Server 查詢優化器的目標之一是花費合理的時間進行查詢優化。優化查詢的速度應該比執行快得多。為了達成此目標，QO 在停止優化程式之前，會先考慮內建的工作臨界值。當在 QO 考慮所有可能計劃之前達到臨界值時，就會達到優化器逾時限制。優化工具逾時事件會在查詢計劃中回報為「語句優化早期終止原因」底下的 TimeOut。請務必瞭解，此臨界值不是以時鐘時間為基礎，而是根據優化器所考慮的可能性數目。在目前的 SQL Server QO 版本中，在達到逾時之前，會先考慮超過 500 萬個工作。

優化器逾時是設計成 SQL Server，而且在許多情況下，這不是影響查詢效能的因素。不過，在某些情況下，SQL 查詢計劃選擇可能會受到優化器逾時的影響，而且查詢效能可能會變慢。當您遇到這類問題時，瞭解優化器逾時機制，以及影響複雜查詢的方式，可協助您疑難解答並改善查詢速度。

達到優化器逾時閾值的結果是，SQL Server 並未考慮優化的完整可能性集。也就是說，它可能錯過了可能會產生較短運行時間的計劃。 QO 會停止在臨界值，並考慮當時的最低成本查詢計劃，即使可能更好、未探索的選項也一樣。請記住，達到優化器逾時之後選取的計劃可能會為查詢產生合理的執行持續時間。不過，在某些情況下，選取的計劃可能會導致次佳的查詢執行。

如何偵測優化器逾時？

以下是表示優化器逾時的癥狀：

複雜查詢

您有一個涉及許多聯結數據表的複雜查詢（例如，已聯結八個以上的數據表）。
慢速查詢

查詢的執行速度可能會比在另一個 SQL Server 版本或系統上執行的速度慢或慢。

查詢計劃顯示 StatementOptmEarlyAbortReason=Timeout

查詢計劃會顯示 StatementOptmEarlyAbortReason="TimeOut" 在 XML 查詢計劃中。

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

檢查 SQL Server Management Studio 中最左邊計劃運算子的屬性Microsoft。您可以看到「原因提早終止語句優化」的值是 TimeOut。

造成優化器逾時的原因為何？

沒有簡單的方法來判斷哪些條件會導致優化工具閾值達到或超過。下列各節是一些因素，會影響 QO 在尋找最佳方案時探索到多少計劃。

應該以何種順序聯結數據表？

以下是三個資料表聯結的執行選項範例（Table1、、 Table2Table3）：
- 使用 Table2 和結果聯結Table1Table3
- 使用 Table3 和結果聯結Table1Table2
- 使用 Table3 和結果聯結Table2Table1
注意： 數據表數目越大，可能性就越大。
要用來從數據表擷取數據列的堆積或二進位樹狀結構（HoBT）存取結構為何？
- 叢集索引
- 非叢集索引1
- 非叢集索引2
- 數據表堆積
要使用的實體存取方法為何？
- 索引搜尋
- 索引掃描
- 表格掃描
要使用的實體聯結運算符為何？
- 巢狀循環聯結（NJ）
- 哈希聯結（HJ）
- 合併聯結（MJ）
- 自適性聯結（從 SQL Server 2017 開始（14.x）
如需詳細資訊，請參閱聯結。
以平行或串行方式執行查詢的元件？

如需詳細資訊，請參閱平行查詢處理。

雖然下列因素會減少考慮的存取方法數目，因而考慮的可能性：

查詢述詞（子句中的 WHERE 篩選條件）
條件約束的存在
設計良好且最新統計數據的組合

注意： QO 到達臨界值並不表示最後會有較慢的查詢。在大部分情況下，查詢會正常執行，但在某些情況下，您可能會看到查詢執行速度較慢。

如何考慮因素的範例

為了說明，讓我們以三個數據表（t1、 t2和 t3）之間的聯結範例為例，而每個數據表都有叢集索引和非叢集索引。

首先，請考慮實體聯結類型。這裡涉及兩個聯結。而且，因為有三種實體聯結可能性（NJ、HJ 和 MJ），因此查詢可以 3² = 9 的方式執行。

NJ - NJ
NJ - HJ
NJ - MJ
HJ - NJ
HJ - HJ
HJ - MJ
MJ - NJ
MJ - HJ
MJ - MJ

然後，請考慮使用 Permutations 計算的聯結順序：P （n， r）。前兩個數據表的順序並不重要，因此可能會有 P（3,1） = 3 種可能性：

使用 t1 t2 和，然後使用 t3
使用 t1 t3 和，然後使用 t2
使用 t2 t3 和，然後使用 t1

接下來，請考慮可用於數據擷取的叢集和非叢集索引。此外，針對每個索引，我們有兩個存取方法，搜尋或掃描。這表示，針對每個數據表，有 2 2 = 4 個選項。我們有三個數據表，因此可以有 4³ = 64 個選項。

最後，考慮到所有這些條件，可以有 9*3*64 = 1728 可能的方案。

現在，假設查詢中聯結了 n 個數據表，而且每個數據表都有叢集索引和非叢集索引。請考慮下列因素：

聯結訂單：P（n，n-2） = n！/2
聯結類型：3^n-1
具有搜尋和掃描方法的不同索引類型：4ⁿ

將上述所有專案相乘，我們可以取得可能的方案數目：2*n！*12^n-1。當 n = 4 時，數位為 82,944。當 n = 6 時，數位為 358,318,080。因此，隨著查詢所涉及的數據表數目增加，可能的計劃數目會以幾何方式增加。此外，如果您包含平行處理原則和其他因素的可能性，您可以想像將考慮多少個可能的計劃。因此，具有許多聯結的查詢更有可能達到優化器逾時臨界值，而不是具有較少聯結的查詢。

請注意，上述計算說明最差的情況。如我們所指出，有一些因素會減少可能性，例如篩選述詞、統計數據和條件約束。例如，篩選述詞和更新的統計數據會減少實體存取方法的數目，因為使用索引搜尋比掃描更有效率。這也會導致較小的聯結選擇等等。

為什麼我會看到具有簡單查詢的優化器逾時？

查詢優化器沒有任何項目很簡單。有許多可能的案例，複雜度如此之高，難以掌握所有可能性。查詢優化器可能會根據在特定階段找到的計劃成本動態設定逾時閾值。例如，如果找到相對有效率的計劃，可能會減少搜尋較佳計劃的工作限制。因此，低估的基數估計（CE）可能是一個提前達到優化工具逾時的情況。在此情況下，調查的重點為 CE。相較於執行上一節所討論的複雜查詢案例，這是比較罕見的案例，但有可能。

解決方法

出現在查詢計劃中的優化器逾時不一定表示這是查詢效能不佳的原因。在大部分情況下，您可能不需要對這種情況執行任何動作。 SQL Server 結尾的查詢計劃可能是合理的，而您執行的查詢可能會執行良好。您可能永遠不知道您遇到優化器逾時。

如果您發現需要微調和優化，請嘗試下列步驟。

步驟 1：建立基準

檢查您是否可以在不同的 SQL Server 組建、使用不同的 CE 組態或不同系統（硬體規格）上，使用相同的數據集來執行相同的查詢。效能微調的指導原則是「沒有基準沒有效能問題」。因此，請務必為相同的查詢建立基準。

步驟 2：尋找導致優化工具逾時的「隱藏」條件

詳細檢查您的查詢，以判斷其複雜性。在初始檢查時，查詢可能並不明顯，而且牽涉到許多聯結。這裡的常見案例是涉及檢視或數據表值函式。例如，在介面上，查詢看起來可能很簡單，因為它會聯結兩個檢視。但是當您檢查檢視內的查詢時，您可能會發現每個檢視都會聯結七個數據表。因此，當兩個檢視聯結時，您最終會得到 14 個數據表聯結。如果您的查詢使用下列物件，請向下切入至每個物件，以查看其內部基礎查詢的外觀：

針對所有這些案例，最常見的解決方法是重寫查詢，並將其分成多個查詢。如需詳細資訊，請參閱步驟 7：精簡查詢。

子查詢或衍生數據表

下列查詢是一個範例，每個查詢中聯結兩組個別的查詢（衍生數據表）與 4-5 個聯結。不過，在 SQL Server 剖析之後，它會編譯成已聯結八個數據表的單一查詢。

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

通用資料表表示式（CTE）

使用多個通用數據表表示式（CTE）不是簡化查詢並避免優化器逾時的適當解決方案。多個 CTE 只會增加查詢的複雜性。因此，解決優化工具逾時時，使用 CTE 會適得其反。 CTE 看起來會以邏輯方式中斷查詢，但它們會合併成單一查詢，並優化為單一大型數據表聯結。

以下是將編譯為具有許多聯結的單一查詢的 CTE 範例。看來針對my_cte的查詢是兩個對象簡單的聯結，但事實上，CTE 中還有七個其他數據表聯結。

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

檢視

請確定您已檢查檢視定義，並取得所有相關數據表。類似於 CTE 和衍生數據表，聯結可以隱藏在檢視表內。例如，兩個檢視之間的聯結最終可能是包含八個數據表的單一查詢：

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

資料表值函式（TVF）

某些聯結可能會隱藏在 TFV 內。下列範例顯示兩個 TFV 之間的聯結，而數據表可能是九個數據表聯結。

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Union

等位運算子會將多個查詢的結果合併成單一結果集。它們也會將多個查詢合併成單一查詢。然後，您可能會取得單一複雜查詢。下列範例最終會包含12個數據表的單一查詢計劃。

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

步驟 3：如果您有較快執行的基準查詢，請使用其查詢計劃

如果您判斷從步驟 1 取得的特定基準計劃較適合透過測試進行查詢，請使用下列其中一個選項來強制 QO 選取該方案：

步驟 4：減少方案選擇

若要降低優化器逾時的機會，請嘗試減少在選擇方案時需要考慮 QO 的可能性。此程式牽涉到使用不同的提示選項來測試查詢。和大部分的 QO 決策一樣，選擇不一定是表面上具決定性的，因為需要考慮許多因素。因此，沒有單一保證成功的策略，而選取的計劃可能會改善或降低所選查詢的效能。

強制加入順序

使用 OPTION (FORCE ORDER) 來消除順序排列：

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

減少 JOIN 的可能性

如果其他替代方法沒有説明，請嘗試藉由限制實體聯結運算符與聯結提示的選項來減少查詢計劃組合。例如： OPTION (HASH JOIN, MERGE JOIN)、 OPTION (HASH JOIN, LOOP JOIN) 或 OPTION (MERGE JOIN)。

注意： 使用這些提示時，您應該小心。

在某些情況下，限制具有較少聯結選項的優化器可能會導致最佳的聯結選項無法使用，而且實際上可能會讓查詢變慢。此外，在某些情況下，優化器需要特定的聯結（例如數據列目標），而且如果聯結不是選項，查詢可能無法產生計劃。因此，當您以特定查詢的聯結提示為目標之後，請檢查您是否找到可提供更佳效能的組合，並排除優化器逾時。

以下是如何使用這類提示的兩個範例：

使用 OPTION (HASH JOIN, LOOP JOIN) 只允許哈希和循環聯結，並避免查詢中的合併聯結：

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)

在兩個資料表之間強制執行特定的聯結：

SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

步驟 5：變更 CE 設定

嘗試在舊版 CE 和新 CE 之間切換來變更 CE 設定。當 SQL Server 評估並建立查詢計劃時，變更 CE 組態可能會導致 QO 挑選不同的路徑。因此，即使優化器逾時問題發生，您最終還是可能會得到比使用替代 CE 組態選取的方案更理想的執行。如需詳細資訊，請參閱如何啟用最佳查詢計劃（基數估計）。

步驟 6：啟用優化器修正

如果您尚未啟用查詢優化器修正，請考慮使用下列兩種方法之一加以啟用：

伺服器層級：使用追蹤旗標 T4199。
資料庫層級：針對 SQL Server 2016 和更新版本使用 ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON 或變更資料庫相容性層級。

QO 修正可能會導致優化器在計劃探索中採用不同的路徑。因此，它可能會選擇更理想的查詢計劃。如需詳細資訊，請參閱 SQL Server 查詢優化器 Hotfix 追蹤旗標 4199 服務模型。

步驟 7：精簡查詢

請考慮使用臨時表將單一多數據表查詢分成多個個別查詢。分割查詢只是簡化優化工具工作的其中一種方式。請參閱下列範例：

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

若要優化查詢，請嘗試將一部分聯結結果插入臨時表中，將單一查詢細分成兩個查詢：

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

共用方式為

針對受查詢優化器逾時影響的慢速查詢進行疑難解答

什麼是優化器逾時？

如何偵測優化器逾時？

造成優化器逾時的原因為何？

如何考慮因素的範例

為什麼我會看到具有簡單查詢的優化器逾時？

解決方法

步驟 1：建立基準

步驟 2：尋找導致優化工具逾時的「隱藏」條件

子查詢或衍生數據表

通用資料表表示式（CTE）

檢視

資料表值函式（TVF）

Union

步驟 3：如果您有較快執行的基準查詢，請使用其查詢計劃

步驟 4：減少方案選擇

強制加入順序

減少 JOIN 的可能性

步驟 5：變更 CE 設定

步驟 6：啟用優化器修正

步驟 7：精簡查詢

意見反應

其他資源

共用方式為

針對受查詢優化器逾時影響的慢速查詢進行疑難解答

什麼是優化器逾時？

如何偵測優化器逾時？

造成優化器逾時的原因為何？

如何考慮因素的範例

為什麼我會看到具有簡單查詢的優化器逾時？

解決方法

步驟 1：建立基準

步驟 2：尋找導致優化工具逾時的「隱藏」條件

子查詢或衍生數據表

通用資料表表示式 （CTE）

檢視

資料表值函式 （TVF）

Union

步驟 3：如果您有較快執行的基準查詢，請使用其查詢計劃

步驟 4：減少方案選擇

強制加入順序

減少 JOIN 的可能性

步驟 5：變更 CE 設定

步驟 6：啟用優化器修正

步驟 7：精簡查詢

意見反應

其他資源

通用資料表表示式（CTE）

資料表值函式（TVF）