基數估計 (SQL Server)

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

SQL Server 查詢最佳化工具是成本型查詢最佳化工具。 這表示它會選取估計處理成本最低的查詢計畫來執行。 查詢最佳化工具根據兩個主要因素來判斷執行查詢計劃的成本：

• 在查詢計畫的每一個層級進行處理的資料列總數，此稱為計畫的基數。
• 查詢中使用的運算子所指定的演算法成本模型。

第一個因數 (基數) 會作為第二個因數 (成本模型) 的輸入參數。 因此，如果改善基數，便能產生更好的估計成本，進而可有更快的執行計畫。

SQL Server 的基數估計 (CE) 主要源自建立索引或統計資料時，手動或自動建立的長條圖。 SQL Server 有時也使用查詢的限制式資訊和邏輯重寫判斷基數。

在下列情況下，SQL Server 無法精確計算基數。 這會導致不精確的成本計算，使得產生並非最佳的查詢計畫。 避免在查詢中使用這些建構可提升查詢效能。 有時也可以使用替代的查詢公式或其他方法，它們是：

• 查詢的述詞，在相同資料表的不同資料行之間使用比較運算子。
• 查詢的述詞使用運算子，且下列任一情況為真：
  • 運算子任一邊所關聯的資料行中，沒有任何統計資料。
  • 統計資料的值分布並不平均，但查詢會搜尋高選擇性值集。 如果運算子不是等號 (=) 運算子，此情況會特別明顯。
  • 述詞使用不等於 (!=) 比較運算子或 NOT 邏輯運算子。
• 查詢使用任何 SQL Server 內建函式或純量值函式、引數非常數的使用者定義函式。
• 查詢透過算術或字串串連運算子，與聯結資料行相關聯。
• 查詢在編譯和最佳化時，比較值不明的變數。

本文說明如何評估及選擇最適合您系統的 CE 設定。 多數系統可受益於最新 CE ，因為最新 CE 最為精確。 CE 會預測您的查詢可能傳回的資料列數目。 查詢最佳化工具使用基數預測，來產生最佳查詢計劃。 由於估計較精確，因此查詢最佳化工具通常較有機會產生較佳的查詢計劃。

應用程式系統可能有項重要的查詢，因為 CE 在版本中的變更，所以該查詢的計劃將變更為速度較慢的計劃。 您可使用幾種方法與工具來辨別因為 CE 問題而執行較慢的查詢。 您也可以選擇要如何解決後續的效能問題。

CE 的版本

1998 年，CE 的重大更新是 SQL Server 7.0 隨附的服務，且相容性層級為 70。 這個版本的 CE 模型會根據四個基本假設設定：

• 獨立性： 在不同資料行散發的資料會假設為各自獨立，除非提供可用的相互關聯資訊。

• 一致性： 相異值會平均分佈，使其全都具有相同頻率。 更明確地說，在每個長條圖步驟中，相異值會平均分布且各值都具有相同頻率。

• 內含項目 (簡單)： 使用者查詢存在的資料。 舉例來說，就兩個資料表間的相等聯結而言，會在聯結長條圖以預估聯結選擇性前，將各輸入長條圖中的述詞選擇性¹ 納入考量。

• 包含詞/句： 對於 Column = Constant 的篩選述詞而言，會假設相關聯資料行的常數實際存在。 若對應的長條圖步驟為非空白，則其中一個步驟的相異值會假設為符合來自述詞的值。

  ¹ 滿足述詞的資料列計數。

從 SQL Server 2014 (12.x) 開始的後續更新相容性層級為 120 以上。 層級 120 及以上的 CE 更新併入已更新假設和演算法，適用於新式資料倉儲和 OLTP 工作負載。 根據 CE 70 假設，下列模型假設已從 CE 120 起變更：

• 獨立性變更為關聯性：不同資料行值的組合不需獨立。 這可能會更類似實際資料查詢。
• 簡單內含項目變成基本內含項目：使用者可查詢不存在的資料。 舉例來說，就兩個資料表間的相等聯結而言，我們會使用基底資料表長條圖來預估聯結選擇性，然後將述詞選擇性納入考量。

使用查詢存放區評估 CE 版本

從 SQL Server 2016 (13.x) 開始，查詢存放區即是檢查查詢效能的實用工具。 啟用查詢存放區後，即使變更執行計畫，查詢存放區也會長期追蹤查詢效能。 監視查詢存放區的高成本或迴歸查詢效能。 如需詳細資訊，請參閱使用查詢存放區來監視效能。

如果您準備升級任何 SQL Server 平台的 SQL Server 或升階資料庫相容性層級，請考慮使用查詢調整小幫手升級資料庫，協助您比較兩個不同相容性層級的查詢效能。

重要

請確定已經為資料庫和工作負載正確設定查詢存放區。 如需詳細資訊，請參閱查詢存放區的最佳做法。

使用擴充事件評估 CE 版本

追蹤基數估計處理序的另一個做法，是使用名為 query_optimizer_estimate_cardinality 的擴充事件。 下列 Transact-SQL 程式碼範例會在 SQL Server 執行。 其會將 .xel 檔案寫入 C:\Temp\ (不過您可變更路徑)。 在 Management Studio 中開啟 .xel 檔案時，系統會以方便使用的方式顯示檔案的詳細資訊。

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO  

注意

此事件 sqlserver.query_optimizer_estimate_cardinality 不適用於 Azure SQL 資料庫。

如需 SQL Database 專用擴充事件的資訊，請參閱 SQL Database 的擴充事件。

評估 CE 版本的步驟

接下來的步驟可用來評估您最重要查詢在最新 CE 下的效能是否不佳。 其中一些步驟是透過執行上一節所示的程式碼範例來進行。

1. 開啟 SQL Server Management Studio (SSMS)。 確定 SQL Server 資料庫已設為最高可用相容性層級。

2. 執行下列預備步驟：

   1. 開啟 SQL Server Management Studio (SSMS)。

   2. 執行 Transact-SQL，並確定您的 SQL Server 資料庫已設為最高可用相容性層級。

   3. 確定您的資料庫已關閉其 LEGACY_CARDINALITY_ESTIMATION 組態。

   4. 清除查詢存放區。 在資料庫中，確定查詢存放區為開啟。

   5. 執行陳述式：SET NOCOUNT OFF;

3. 執行陳述式：SET STATISTICS XML ON;

4. 執行您的重要查詢。

5. 記下結果窗格中 [訊息] 索引標籤上實際受影響的資料列數目。

6. 在結果窗格的 [結果] 索引標籤上，按兩下包含 XML 格式統計資料的資料格。 圖形查詢計劃隨即顯示。

7. 以滑鼠右鍵按一下圖形查詢計劃的第一個方塊，然後選取 [屬性]。

8. 為了在稍後比較不同的組態，請記下下列屬性的值：

   • [CardinalityEstimationModelVersion] 。

   • [估計的資料列數目] 。

   • [估計的 I/O 成本] ，以及涉及實際效能 (而不是資料列計數預測) 的幾個類似 [估計] 屬性。

   • [邏輯作業] 和 [實體作業] 。 [平行處理原則] 是正確值。

   • [實際的執行模式] 。 [批次] 是正確值，優於 [資料列] 。

9. 比較估計的資料列數目與實際的資料列數目。 CE 誤差是 1% (高或低)，或是 10%？

10. 執行：SET STATISTICS XML OFF;

11. 執行 Transact-SQL 將資料庫相容性層級降低一個層級 (例如從 130 降低為 120)。

12. 重新執行所有非預備步驟。

13. 比較兩次執行的 CE 屬性值。

    • 最新 CE 的誤差百分比是否小於舊版 CE？

14. 最後，比較這兩次執行的各種效能屬性值。

    • 您的查詢是否在這兩個不同的 CE 估計下使用不同的計劃？

    • 您的查詢在最新的 CE 下是否執行得較慢？

    • 除非您的查詢在舊版 CE 下的執行效果更佳且使用不同的計劃，否則幾乎可以確定您需要最新的 CE。

    • 不過，如果您的查詢在舊版 CE 下執行更快速的計劃，請考慮強制系統使用更快速的計劃並忽略此 CE。 如此一來，您不但可以針對所有項目使用最新的 CE，同時也可以在偶爾的情況下保留更快速的計劃。

如何啟用最佳查詢計劃

假設 CE120 或以上版本為您的查詢產生了效率較低的查詢計劃。 以下部分選項必須啟用較佳計畫，從最大排序至最小範圍：

• 您可以將整個資料庫的相容性層級，設為低於最新可用值。

  • 例如，將相容性層級設為 110 或更低來啟用 CE 70，不過這會使所有查詢受限於先前的 CE 模型。

  • 此外，設定較低的相容性層級會忽略查詢最佳化工具最新版本的一些改善，並影響資料庫所有的查詢。

• 您可以使用 LEGACY_CARDINALITY_ESTIMATION 資料庫範圍設定，讓整個資料庫使用舊版 CE，同時保留查詢最佳化工具的改善。

• 您可以使用 LEGACY_CARDINALITY_ESTIMATION 查詢提示，讓單一資料庫使用舊版 CE，同時保留查詢最佳化工具的改善。

• 您可以透過查詢存放區提示功能，強制執行 LEGACY_CARDINALITY_ESTIMATION，讓單一查詢使用舊版 CE，而不必變更查詢。

• 使用查詢存放區強制執行不同的計畫。

資料庫相容性層級

您可以使用下列 COMPATIBILITY_LEVEL 的 Transact-SQL 程式碼，確保資料庫定於特定層級。

重要

SQL Server 和 Azure SQL Database 的資料庫引擎版本號碼無法彼此相互比較，且更像是這些個別產品的內部組建編號。 Azure SQL Server 的資料庫引擎與 SQL Server 資料庫引擎建立於相同的程式碼基底。 最重要的是，Azure SQL Database 資料庫引擎一律具有最新的 SQL 資料庫引擎位元。 Azure SQL Database 版本 12 比 SQL Server 版本 15 更新。 2019 年 11 月後，在 Azure SQL Database 中，新建資料庫的預設相容性層級是 150。 Microsoft 不會更新現有資料庫的資料庫相容性層級。 這是由客戶自己決定。

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO  

針對預先存在且在較低相容性層級執行的資料庫，只要應用程式不需使用只在較高資料庫相容性層級才提供的增強功能，維持先前的資料庫相容性層級就是有效的方法。 如果是新的開發工作，或現有應用程式需要使用智慧查詢處理與一些新的 Transact-SQL 等新功能，請規劃升級資料庫相容性層級至最新可用層級。 如需詳細資訊，請參閱相容性層級和資料庫引擎升級。

警告

變更資料庫相容性層級前，請參閱升級資料庫相容性層級的最佳做法。

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO  

若是相容性層級設定為 120 或以上的 SQL Server 資料庫，啟用追蹤旗標 9481 會強制系統使用 CE 版本 70。

舊版基數估算器

如果是相容性層級設為 120 以上的 SQL Server 資料庫，您可以使用 ALTER DATABASE SCOPED CONFIGURATION，在資料庫層級啟動舊版基數估算器 (CE 版本 70)。

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

修改查詢並使用提示

從 SQL Server 2016 (13.x) SP1 開始，修改查詢可使用查詢提示 USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')。

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

設定查詢存放區提示

您可以使用查詢存放區提示，強制使用舊版基數估算器，而不必修改查詢。

1. 識別sys.query_store_query_text 和 sys.query_store_query 查詢存放區目錄檢視中的查詢。 例如，依文字片段搜尋已執行的查詢：

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt 
   INNER JOIN sys.query_store_query q ON 
   qt.query_text_id = q.query_text_id 
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
   AND query_sql_text not like N'%query_store%';
   

2. 下列範例會套用查詢存放區提示，並在 query_id 39 上強制執行舊版基數估算器，而不必修改查詢：

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

注意

如需詳細資訊，請參閱查詢存放區提示 (預覽)。 此功能目前僅適用於 Azure SQL 資料庫。

如何強制執行特定查詢計劃

若要進行最精細的控制，您可以「強制」 系統在測試期間使用透過 CE 70 所產生的計劃。 「固定」 您慣用的計劃之後，您可以將整個資料庫設定為使用最新的相容性層級和 CE。 接下來將會詳細說明這個選項。

查詢存放區提供不同的方式，所以您可以強制系統使用特定的查詢計劃：

• 執行 sys.sp_query_store_force_plan。

• 在 SQL Server Management Studio (SSMS) 中，展開 [查詢存放區] 節點，以滑鼠右鍵按一下 [熱門資源取用節點]，然後選取 [檢視熱門資源取用節點]。 這會顯示標示為 [強制執行計畫] 和 [取消強制執行計畫] 的按鈕。

如需查詢存放區的詳細資訊，請參閱使用查詢存放區監視效能。

基數估計期間的常數折疊與運算式評估

資料庫引擎會提前評估部分常數運算式，並改善查詢效能。 這個作業稱為常數摺疊 (Constant Folding)。 常數是 Transact-SQL 常值，例如3、'ABC'、'2005-12-31'、1.0e3 或 0x12345678。 如需詳細資訊，請參閱常數折疊。

此外，部分運算式不是常數摺疊，但在編譯時間已知這些運算式的引數 (無論引數是參數或常數)，而最佳化時，查詢最佳化工具的結果集大小 (基數) 估算器會評估這些運算式。 如需詳細資訊，請參閱運算式評估。

最佳做法：使用常數摺疊和編譯時間運算式評估，產生最佳查詢計畫

為確保產生最佳查詢計畫，最好設計查詢、預存程序和批次，讓查詢最佳化工具可根據資料分配的統計資料，精確估計查詢中條件的選擇性。 否則估計選擇性時，查詢最佳化工具必須使用預設估計值。

為確保查詢最佳化工具的基數估算器可提供良好的估計值，建議您先確定 AUTO_CREATE_STATISTICS 和 AUTO_UPDATE_STATISTICS 資料庫設定選項為 ON (預設值)，或已手動建立參考查詢條件中所有資料行的統計資料。 接著，設計查詢條件時，請盡量遵循以下設定：

• 避免在查詢中使用本機變數。 請改在查詢中使用參數、常值或運算式。

• 在包含參數的查詢中使用內嵌的運算子和函數時，請限制在＜基數估計的編譯時間運算式評估＞下所列出的那些運算子和函數。

• 確定查詢條件中只有常數的運算式是可摺疊常數，或可在編譯時間進行評估。

• 如果在查詢中必須使用本機變數來評估運算式，請考慮在查詢之外的不同範圍中評估它。 例如，不妨執行下列其中一個選項：

  • 將變數的值傳遞至包含您所要評估之查詢的預存程序，並且讓查詢使用程序參數，來取代本機變數。

  • 建構字串並包含以本機變數值為準的查詢，然後使用動態 SQL (EXEC 或最好是 sp_executesql) 執行此字串。

  • 使用 sp_executesql 參數化並執行查詢，然後傳遞變數的值至查詢作為參數。

CE 改善範例

本節描述因實作最新版 CE 的增強功能而受益的範例查詢。 這是背景資訊，您不必執行特定的動作。

範例 A：CE 認為最大值可能比上次收集統計資料時還要高

假設統計資料是在 OrderAddedDate 上限為 2016-04-30時，於 2016-04-30為 OrderTable 收集的狀況。 CE 120 (和更新版本) 認為包含遞增資料的 OrderTable 資料行，其值可能大於統計資料記錄的最大值。 此認知會改善 Transact-SQL SELECT 陳述式的查詢計劃，如下所示。

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';  

範例 B：CE 認為相同資料表上的篩選述詞通常相互關聯

在下列 SELECT 中，我們可看到 Model 和 ModelVariant 的篩選述詞。 我們直覺認為 Model 為「Xbox」時，ModelVariant 即「One」，因為 Xbox 有名為 One 的變數。

從 CE 120 開始，SQL Server 認為相同資料表「Model」與「ModelVariant」上的兩個資料行間可能有關聯性。 CE 可更準確地的估計查詢所要傳回的資料列數目，而且查詢最佳化工具能產生更佳的計劃。

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';  

範例 C：CE 不會再假設來自不同資料表的篩選述詞之間有任何相互關聯

新式工作負載和實際商務資料的最新大型研究顯示，不同資料表的述詞篩選條件通常沒有相互關聯。 在下列查詢中，CE 假設 s.type 和 r.date 間沒有關連性。 因此，CE 所估計的傳回資料列數目比較低。

SELECT s.ticket, s.customer, r.store  
FROM dbo.Sales AS s  
CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND  
s.type = 'toy' AND  
r.date = '2016-05-11';  

相關內容

• 基數估計 (CE) 意見反應
• 效能的監視與微調
• Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator (使用 SQL Server 2014 基數估算程式最佳化您的查詢計劃)
• 提示 (Transact-SQL) - 查詢
• USE HINT 查詢提示
• 使用查詢微調助理來升級資料庫
• 使用查詢存放區監視效能
• 查詢處理架構指南
• 查詢存放區提示
• SQL 資料庫中的智慧查詢處理
• DBCC TRACEON - 追蹤旗標 (Transact-SQL)
• sys.query_store_query_hints