基數估計 (CE) 意見反應

文章
06/24/2024

適用於：適用於：SQL Server 2022 （16.x）和更新版本、Azure SQL 資料庫、Azure SQL 受控執行個體。

自 SQL Server 2022 (16.x) 起，基數估計 (CE) 意見反應屬於智慧型查詢處理系列功能的一部分，當重複查詢的查詢執行計畫欠佳，因而產生錯誤的 CE 模型假設，此功能便有助於解決該問題。將舊版資料庫引擎升級時，此案例有助於降低與預設 CE 相關的迴歸風險。

由於沒有任何一個 CE 模型和假設的集合可容納大量的客戶工作負載和資料散發，因此 CE 意見反應會根據查詢執行階段特性來提供可調整的解決方案。 CE 意見反應會識別並使用更符合指定查詢和資料分散的模型假設，以改善查詢執行計畫品質。目前 CE 意見反應可以識別計畫運算子，其中估計資料列數目和實際資料列數目大不相同。當發生模型估計重大錯誤，且有可行的替代模型可供嘗試時，便會套用意見反應。

如需其他查詢意見反應功能，請參閱記憶體授與意見反應及平行處理原則程度 (DOP) 意見反應。

了解基數估計 (CE) 摘意見反應

基數估計 (CE) 是查詢最佳化工具估計查詢計畫各層級所處理資料列總數的方式。 SQL Server 中的基數估計主要來自建立索引或統計資料時 (手動或自動) 建立的長條圖。 SQL Server 有時也使用查詢的限制式資訊和邏輯重寫判斷基數。

資料庫引擎的不同版本會根據資料的散發和查詢方式，而使用不同的 CE 模型假設。如需詳細資訊，請參閱 CE 版本。

基數估計 (CE) 意見反應實作

基數估計 (CE) 意見反應會了解在一段時間內最佳的 CE 模型假設，並套用過去最正確的假設：

CE 意見反應會識別與模型相關的假設，並評估對重複查詢是否正確。
若假設看起來不正確，則會在查詢計劃中調整有效的 CE 模型假設，以此測試相同查詢的後續執行，並驗證是否有所助益。我們可藉由查看計畫運算子的實際與估計資料列來識別不正確。 CE 意見反應中可用的模型變體無法校正所有錯誤。
若可改善計畫品質，便會透過查詢存放區提示的實作機制來調整估計模型，以使用適當USE HINT 查詢提示的查詢計劃取代舊的查詢計劃。

系統只會保存已驗證的意見反應。若調整後的模型假設導致效能迴歸，CE 意見反應便不會用於該查詢。在此狀況下，使用者取消的查詢也會視為迴歸。

基數估計 (CE) 意見反應案例

基數估計 (CE) 意見反應能解決使用預設 CE (CE120 或更高版本) 時，不正確的 CE 模型假設所產生的認知迴歸問題，而且可以選擇是否使用不同的模型假設。這些案例包含相互關聯、聯結內含項目和最佳化工具資料列等目標。

基數估計 (CE) 意見反應關聯性

在特定資料表或檢視表上估計述詞選擇性、或符合上述述詞的資料列數目時，查詢最佳化工具會使用相互關聯模型假設。這些假設可為下列類型的述詞：

完全獨立 (預設值 CE70)，將所有述詞的選擇性相乘以計算基數。
部分相互關聯 (預設值 CE120 以上)，使用指數輪詢的變化來計算基數，述詞依序從最多到最少選擇性而排列。
完全相互關聯，使用所有述詞的最少選取性來計算基數。

當資料庫相容性設為 120 以上時，下列範例會使用部分相互關聯：

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

當資料庫相容性設為 160，且使用預設相互關聯時，根據估計基數比實際資料列數目低估或高估，CE 意見反應會嘗試將相互關聯逐步移往正確方向。若實際的資料列數目大於估計基數，請使用完全相互關聯。若實際資料列數目小於估計基數，請使用完全獨立。

如需詳細資訊，請參閱 CE 版本。

基數估計 (CE) 意見反應聯結內含項目

當查詢最佳化工具估計聯結述詞及適用篩選述詞的選取性時，則使用內含項目模型假設。假設如下：

簡單內含項目 (預設為 CE70) 假設聯結述詞完全相互關聯，先計算篩選選擇性，再考慮聯結選擇性。
基底內含項目 (預設為 CE120 或以上) 假設聯結述詞與下游篩選間沒有相互關聯性，且會先計算聯結選擇性，再考慮篩選選擇性。

當資料庫相容性設為 120 以上時，下列範例會使用基底內含項目：

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

如需詳細資訊，請參閱 CE 版本。

基數估計 (CE) 意見反應和查詢最佳化工具資料列目標

當查詢最佳化工具估計執行計畫的基數時，通常會假設所有資料表中的所有合格資料列皆須進行處理。然而，某些查詢模式會導致查詢最佳化工具搜尋會傳回較少資料列數目的計畫，以便減少 I/O。若查詢使用 TOP、IN 或 EXISTS 關鍵字、FAST 查詢提示或 SET ROWCOUNT 陳述式，以指定資料列在執行階段的預期目標數目 (資料列目標)，查詢最佳化流程便會使用該資料列目標，如下列範例所示：

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

套用資料列目標計劃時，查詢計劃中估計的資料列數目會減少，因為查詢最佳化工具假設必須處理較少資料列，才能達到資料列目標。

資料列目標雖是特定查詢模式的有效最佳化策略，但若未以統一方式散發資料，掃描的頁數則可能會超過估計，表示資料列目標效率不彰。 CE 意見反應可停用資料列目標掃描，且會在偵測到效率不佳時啟用搜尋。

在執行計畫中，CE 意見反應沒有特定屬性，但會列出查詢存放區提示的屬性。請注意 QueryStoreStatementHintSource 將為 CE feedback。

基數估計 (CE) 意見反應的考量

若要啟用基數估計 (CE) 意見反應，請在執行查詢時，請針對連線的資料庫啟用資料庫相容性層級 160。使用 CE 意見反應的每個資料庫皆須啟用查詢存放區，並處於 READ_WRITE 模式。
若要停用資料庫層級的 CE 意見反應，請使用 CE_FEEDBACK 資料庫範圍設定。例如，在使用者資料庫中：
```
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
```
若要在查詢層級停用 CE 意見反應，請使用 DISABLE_CE_FEEDBACK 查詢提示。

CE 意見反應活動可透過 query_feedback_analysis 和 query_feedback_validation XEvents 顯示。

CE 意見反應所設定的提示可使用 sys.query_store_query_hints 目錄檢視來追蹤。

您可使用 sys.query_store_plan_feedback 目錄檢視來追蹤意見反應資訊。

若某查詢的查詢計劃強制透過查詢存放區，CE 意見反應將不會用於該查詢。

若某查詢使用硬式編碼的查詢提示，或使用由使用者設定的查詢存放區提示，CE 意見反應將不會用於該查詢。如需更多資訊，請參閱查詢提示和查詢存放區提示。

自 2022 SQL Server 2022 (16.x) 起，當次要複本的查詢資料存放區啟用時，CE 意見反應不會感知可用性群組的次要複本。目前 CE 意見反應只對主要複本有效益。在容錯移轉時，主要或次要複本所套用的意見反應會遺失。如需詳細資訊，請參閱次要複本的查詢存放區。

基數估計 (CE) 意見反應的持續性

適用於：適用於：SQL Server 2022 （16.x）和更新版本、Azure SQL 資料庫、Azure SQL 受控執行個體。

基數估計（CE）意見反應可以偵測應該保存數據列目標優化的情況，並藉由以查詢存放區提示的形式將它保存在查詢存放區來保留此變更。新的最佳化會用於查詢的未來執行。 CE 意見反應會在資料列目標最佳化查詢模式之外保存其他案例，如意見反應案例中所述。 CE 意見反應目前會處理 CE 相互關聯模型使用的述詞選擇性案例，以及 CE 內含項目模型處理的聯結述詞案例。

這項功能已在 SQL Server 2022 (16.x) 中加入，但該項效能增強功能適用於資料庫相容性層級 160 以上、或 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 提示 160 以上所運作的查詢，且資料庫已啟用查詢存放區，並處於「讀取寫入」狀態時。

基數估計 (CE) 意見反應的已知問題

問題	探索日期	狀態	解決日期
在特定條件下套用 SQL Server 2022 (16.x) 的累積更新 8 之後，SQL Server 效能會變慢。啟用 CE 意見反應時，您可能會遇到計畫快取記憶體使用率大幅提升，以及 CPU 使用量意外增加。	2023 年 12 月	已解決	2024 年 4 月 22 日 (CU 12)

已知問題詳細資料

在特定條件下套用 SQL Server 2022 的累積更新 8 之後，SQL Server 效能會變慢

從 SQL Server 2022 (16.x) 累積更新 8 開始，SQL Server 可能會顯示 CPU 和記憶體使用率意外增加。此外，可能會觀察到 RESOURCE_SEMAPHORE_QUERY_COMPILE 等候增加。您也可能注意到，使用中的計畫快取物件數目會穩定增加，該方法是計畫快取限制，且以 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE、DBCC FREESYSTEMCACHE 或 DBCC FREEPROCCACHE 等技術手動清除計畫快取並不能提供協助。只有部分客戶才會觀察到此行為。

此問題不會影響所有工作負載，並取決於已產生的不同計畫數目，以及符合 CE 意見反應功能參與的計畫數目。雖然 CE 意見反應正在分析計畫運算元是否有重大模型誤判，但有一個案例可在此分析階段期間取值參考計畫。這種情況可防止系統使用通常最近最少使用的 (LRU) 演算法將計畫從記憶體移除。 LRU 機制是 SQL Server 強制執行計畫收回原則的其中一種方式。如果系統承受記憶體壓力，SQL Server 也會從記憶體中移除計畫。當 SQL Server 嘗試移除未正確取值的計畫時，無法從計畫快取中移除這些計畫，這會導致快取持續成長。增長的快取可能會開始造成最終會使用更多 CPU 和記憶體的額外編譯。如需詳細資訊，請參閱計畫快取內部。

徵狀：SQL 計畫或物件計畫中正在使用並標示為已變更的計畫快取項目數目，會隨著時間增加至 50,000 個以上。如果您觀察到計畫快取項目開始接近此層級，以及 CPU 使用率意外增加，您的系統可能會遇到此問題。 SQL Server 2022 (16.x) 累積更新 12 中提供了修正。請參閱 KB5033663。

若要監視系統使用的計畫快取項目數目，下列範例可用來作為存在之計畫快取項目數目的時間點檢視。例如，觀察標示為已變更的計畫快取項目數目，是監視這種現象的其中一種方式。

SELECT
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END AS PlanType,
  COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp
  ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
AND ecp.bucketid is NULL
GROUP BY
  CASE
    WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
    WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
    ELSE '[All other cache stores]'
  END;

另一組查詢也會提供與上述範例相同的資訊，同時可讓您觀察其他效能計量。計畫快取命中率，以及與批次要求數/秒相關的編譯數目，將會減少。下列查詢可用來監視系統隨時間的變化情況。請留意快取命中率 (非預期下降)、使用中的快取物件 (數量會增加至近 50,000 而不會減少），以及低於預期的次要求數/秒比率 (相較於編譯/秒增加)。

--SQL Plan (Adhoc and Prepared plans)
SELECT
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (SQL Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value
        FROM sys.dm_os_performance_counters WHERE
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
        WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
        WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
        WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
    END AS [SQLServer:Plan Cache (Object Plans)],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
        ELSE FORMAT(cntr_value, '#,###')
    END AS [Counter Value],
    CASE
        WHEN [counter_name] = 'Cache Hit Ratio' THEN
            FORMAT(TRY_CONVERT(DECIMAL(5, 2), (cntr_value * 1.0 / NULLIF((SELECT cntr_value
        FROM sys.dm_os_performance_counters WHERE
        [object_name] LIKE '%:Plan Cache%' AND [counter_name] = 'Cache Hit Ratio Base'
        AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
    END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
    AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
    AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT
    CASE
        WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
        WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
    END AS [SQLServer:SQL Statistics],
    FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec'
);

因應措施

如果您的系統持續遇到先前所述的徵兆，在套用累積更新 12 KB5033663之後，可以在資料庫層級停用 CE 意見反應功能。

若要回收此問題所佔用的計畫快取記憶體，則需要重新啟動 SQL Server 執行個體。停用 CE 意見反應功能之後，即可採取此重新啟動動作。若要停用資料庫層級的 CE 意見反應，請使用 CE_FEEDBACK 資料庫範圍設定。例如，在使用者資料庫中：

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

意見反應和報告問題

如需意見反應或問題，請傳送電子郵件至 CEFfeedback@microsoft.com

分享方式：

基數估計 (CE) 意見反應

了解基數估計 (CE) 摘意見反應

基數估計 (CE) 意見反應實作

基數估計 (CE) 意見反應案例

基數估計 (CE) 意見反應關聯性

基數估計 (CE) 意見反應聯結內含項目

基數估計 (CE) 意見反應和查詢最佳化工具資料列目標

基數估計 (CE) 意見反應的考量

基數估計 (CE) 意見反應的持續性

基數估計 (CE) 意見反應的已知問題

已知問題詳細資料

在特定條件下套用 SQL Server 2022 的累積更新 8 之後，SQL Server 效能會變慢

因應措施

意見反應和報告問題

意見反映

更多資源

分享方式：

基數估計 (CE) 意見反應

了解基數估計 (CE) 摘意見反應

基數估計 (CE) 意見反應實作

基數估計 (CE) 意見反應案例

基數估計 (CE) 意見反應關聯性

基數估計 (CE) 意見反應聯結內含項目

基數估計 (CE) 意見反應和查詢最佳化工具資料列目標

基數估計 (CE) 意見反應的考量

基數估計 (CE) 意見反應的持續性

基數估計 (CE) 意見反應的已知問題

已知問題詳細資料

在特定條件下套用 SQL Server 2022 的累積更新 8 之後，SQL Server 效能會變慢

因應措施

意見反應和報告問題

相關內容

意見反映

更多資源