記憶體授與意見反應

適用於：SQL Server 2017 (14.x) 和更新版本、Azure SQL 受控執行個體、Azure SQL 資料庫

執行查詢時，記憶體授與有時會過大或過小。 若記憶體授與過大，則會禁止在伺服器上的平行處理原則。 若過小則可能會溢寫至磁碟，而此作業成本高昂。 記憶體授與意見反應會嘗試記住先前執行的記憶體需求 (使用百分位數意見反應、多個過往執行)。 根據此歷程查詢資訊，記憶體授與意見反應會據以調整提供給查詢的授與，以供後續執行。

此功能至今已發行三波。 批次模式記憶體授與意見反應，以及資料列模式記憶體授與意見反應；SQL Server 2022 (16.x) 加入了使用查詢存放區、保存在磁碟上的記憶體授與意見反應，以及稱為「百分位數授與」的改良演算法。

注意

如需其他查詢意見反應功能，請參閱基數估計 (CE) 意見反應及平行處理原則程度 (DOP) 意見反應。

批次模式記憶體授與意見反應

適用於：SQL Server (版本 SQL Server 2017 (14.x) 起) 與 Azure SQL Database

查詢的執行計畫包含執行所需的最小記憶體，以及能將所有資料列納入記憶體的理想記憶體授與大小。 當調整的記憶體授與大小不正確時，就會降低效能。 授與過多會浪費記憶體並降低並行。 記憶體授與不足會佔用大量磁碟資源。 透過處理重複的工作負載，批次模式記憶體授與意見反應會重新計算查詢實際所需的記憶體，然後更新快取計劃的授與值。 執行相同的查詢陳述式時，此查詢會使用修訂過的記憶體授權大小，減少影響並行的過多記憶體授與，並修正導致佔用大量磁碟資源的記憶體授與低估。

下圖顯示使用批次模式調整記憶體授與意見反應的一個範例。 由於高溢出，第一次執行查詢的持續時間為「88 秒」 ：

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

啟用記憶體授與意見反應後，第二次執行的持續時間是「1 秒」 (從 88 秒降下)，溢出全部移除，且授與較高：

記憶體授與意見反應調整大小

針對記憶體授與過多的狀況，如果授與的記憶體超過實際使用記憶體大小的兩倍，記憶體授與回饋便會重新計算記憶體授與並更新快取計劃。 記憶體授與小於 1 MB 的方案將不會針對超額問題重新計算。

若因記憶體授與大小不足而導致批次模式運算子的磁碟溢寫，記憶體授與回饋將會觸發記憶體授與的重新計算。 溢寫事件會記錄至記憶體授與回饋，且可透過 spilling_report_to_memory_grant_feedback 擴充事件顯示。 此事件會傳回計畫的節點識別碼，以及該節點溢出的資料大小。

實際 (執行後) 方案會透過 GrantedMemory 屬性顯示調整後的記憶體授與。

在圖形化執行程序表的根運算子或執行程序表 XML 輸出中，您可看到此屬性：

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

若要讓工作負載自動符合這項改良資格，該資料庫請啟用相容性層級 140。

範例：

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

記憶體授與意見反應與參數敏感的情況

不同的參數值可能也需要不同的查詢計畫，以維持最佳狀態。 這類型的查詢即定義為「參數敏感」。

凡是參數敏感的計劃，如果記憶體授與意見反應有不穩定的記憶體需求，就會在查詢上自行停用。 記憶體授與意見反應功能會在查詢重複數輪後停用，透過監視 memory_grant_feedback_loop_disabled xEvent 可觀察到此狀況。 此狀況可透過 SQL Server 2022 (16.x) 中加入的記憶體授與意見反應持續性和百分位數模式得到改善。 記憶體授與意見反應的持續性功能需要在資料庫中啟用查詢存放區，並設為「讀取寫入」模式。

如需參數探查和參數敏感性的詳細資訊，請參閱查詢處理架構指南。

記憶體授與意見反應快取

意見反應可以儲存在快取計劃中供單次執行之用。 此為該陳述式的連續執行，但可獲得記憶體授與意見反應調整的效益。 此功能適用於重複執行的陳述式。 記憶體授與意見反應僅會變更快取的計劃。 在 SQL Server 2022 (16.x) 前，系統不會擷取查詢存放區中的變更。

若已從快取移出計畫，意見反應則不會保存。 若有容錯移轉，意見反應也會遺失。 使用 OPTION (RECOMPILE) 的陳述式會建立新的計劃，而不進行快取。 由於不進行快取，因此不會產生記憶體授與意見反應，也不會儲存以供編譯及執行。 但若已快取未使用 OPTION (RECOMPILE) 的對等陳述式 (意即查詢雜湊相同) 並重複執行，記憶體授與意見反應則可用於第二個以後的連續執行。

追蹤記憶體授與意見反應活動

您可使用 memory_grant_updated_by_feedback 擴充事件來追蹤記憶體授與意見反映事件。 此事件會追蹤目前的執行計數記錄、記憶體授與意見反應更新計劃的次數，以及記憶體授與意見反應修改快取計劃前後的理想額外記憶體授權。

記憶體授與意見反應、資源管理員和查詢提示

實際的記憶體授與會接受由資源管理員或查詢提示所決定的查詢記憶體限制。

停用批次模式記憶體授與意見反應，而不變更相容性層級

您可以在資料庫或陳述式的範圍停用記憶體授與意見反應，同時仍將資料庫相容性層級維持在 140 以上。 若來自資料庫的所有查詢執行皆要停用批次模式的記憶體授與意見反應，請在適用資料庫的內容中執行下列 SQL 陳述式：

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

啟用時，此設定在 sys.database_scoped_configurations 中會顯示為已啟用。

若來自資料庫的所有查詢執行皆要重新啟用批次模式的記憶體授與意見反應，請在適用資料庫的內容中執行下列 SQL 陳述式：

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

您也可以將 DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK 指定為 USE HINT 查詢提示，以針對特定查詢停用批次模式的記憶體授與意見反應。 例如：

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

USE HINT 查詢提示的優先順序會高於資料庫範圍設定或追蹤旗標設定。

資料列模式記憶體授與意見反應

適用於：SQL Server (版本 SQL Server 2019 (15.x) 起) 與 Azure SQL Database

調整批次和資料列模式運算子的記憶體授與大小，藉此在批次模式記憶體授與意見反應功能上展開資料列模式記憶體授與意見反應。

若要在 Azure SQL Database 中啟用資料列模式的記憶體授與意見反應，當您執行查詢時，請針對連線的資料庫啟用 150 以上的資料庫相容性層級。

範例：

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

如同批次模式的記憶體授與意見反應，資料列模式的記憶體授與意見反應活動也可透過 memory_grant_updated_by_feedback XEvent 顯示。 我們也加入了兩項新的查詢執行計畫屬性，以便更清楚瞭解資料列和批次模式記憶體授與意見反應作業的目前狀態。

記憶體授與意見反應不需要查詢存放區，但 SQL Server 2022 (16.x) 加入的持續性改善功能需要啟用資料庫的查詢存放區，以及「讀取寫入」狀態。 如需有關持續性的詳細資訊，請參閱本文後段的百分位數和持續性模式記憶體授與意見反應。

資料列模式記憶體授與意見反應活動可透過 memory_grant_updated_by_feedback 擴充事件顯示。

從資料列模式記憶體授與意見反應開始，實際的執行後計畫將會顯示兩個新的查詢計畫屬性：IsMemoryGrantFeedbackAdjusted 和 LastRequestedMemory，且已新增至 MemoryGrantInfo 查詢計劃 XML 元素。

• 在執行查詢前，LastRequestedMemory 屬性會顯示授與的記憶體 (KB)。
• IsMemoryGrantFeedbackAdjusted 屬性可讓您針對實際查詢執行計畫內的陳述式，檢查記憶體授與意見反應的狀態。

此屬性中顯示的值如下：

IsMemoryGrantFeedbackAdjusted 值	描述
否：第一次執行	若為第一次編譯及相關執行，記憶體授與意見反應不會調整記憶體。
否：精確授與	若未溢寫至磁碟，且陳述式使用至少 50% 的授與記憶體，則不會觸發記憶體授與意見反應。
否：意見反應已停用	若持續觸發記憶體授與意見反應，且記憶體增加和減少作業反覆發生，資料庫引擎將會停用該陳述式的記憶體授與意見反應。
是：調整	已套用記憶體授與意見反應，而且可能會針對下一次執行進一步調整。
是：百分位數調整	記憶體授與意見反應是使用百分位數授與演算法來套用，而該演算法會查看歷程記錄，而不只是最近的執行。
是：穩定	已套用記憶體授與意見反應，而且授與的記憶體現已穩定，表示針對上次執行授與的記憶體就是針對目前執行授與的記憶體。

百分位數和持續性模式記憶體授與意見反應

適用於：SQL Server (版本 SQL Server 2022 (16.x) 起) 與 Azure SQL Database

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

• 依預設，SQL Server 2022 (16.x) 會啟用百分位數記憶體授與意見反應，但若查詢存放區未啟用且處於「讀取寫入」狀態，則沒有任何作用。
• 依預設，SQL Server 2022 (16.x) 中的記憶體授與、CE 和 DOP 意見反應會開啟持續性，但若查詢存放區未啟用且處於「讀取寫入」狀態，則沒有任何作用。
• 記憶體授與意見反應的百分位數和持續性可在 Azure SQL 資料庫中使用，而且預設會在所有現有和新的資料庫上啟用。
• Azure SQL 受控執行個體目前無法使用記憶體授與意見反應的百分位數和持續性。

建議先具備工作負載的效能基準，再為資料庫啟用此功能。 基準數值可協助您判斷是否獲得該功能的預期效益。

記憶體授與意見反應 (MGF) 會根據過去的效能來調整配置給查詢的記憶體大小。 然而，此專案的初始階段只會將記憶體授與調整的計畫儲存在快取中；若從快取移出計畫，意見反應流程則須再次啟動，導致移出後查詢的前幾次執行效能不佳。 新的解決方案是在查詢存放區中保存授與資訊與其他查詢資訊，以便在快取移出後仍能延續效益。 記憶體授與意見反應持續性和百分位數使用一種不干擾的方式，以因應記憶體授與意見反應的現有限制。

此外，調整授與大小時只會考慮最近使用的授與值。 因此，若參數化查詢或工作負載需要在每次執行時大幅變更記憶體授與大小，最新的授與資訊可能會不正確。 執行查詢的實際需求可能有所不同。 在此案例中，記憶體授與意見反應對效能並無助益，因為我們一律會根據上次使用的授與值來調整記憶體。 下一張圖片顯示了記憶體授與意見反應的可能行為 (不含百分位數和持續性模式)。

如您所見，在這項不尋常卻可能發生的查詢行為中，若查詢執行本身改變了記憶體量，反覆變動實際所需及授與的記憶體量便會導致記憶體浪費與不足。 在此案例中，記憶體授與意見反應本身的壞處大於好處，因此自行停用。

當我們使用查詢最近歷程記錄的百分位數計算，而不是只使用上次執行，便可根據過去的執行使用量歷程記錄來縮小授與大小值的差距，並嘗試最佳化以減少溢寫。 例如相同的交替工作負載，可看到下列記憶體授與行為：

查詢最佳化工具會使用高百分位數的過去記憶體授與大小調整需求來執行快取計畫，並使用查詢存放區中保存的資料來計算記憶體授與大小。 百分位數調整會根據執行的近期歷程記錄來進行記憶體授與調整。 指定的記憶體授與會隨時間而減少記憶體溢寫及浪費。

持續性同時適用於 DOP 意見反應和 CE 意見反應。

啟用和停用記憶體授與意見反應功能

停用資料列模式記憶體授與意見反應，而不變更相容性層級

您可以在資料庫或陳述式的範圍中停用資料列模式記憶體授與意見反應，同時仍將資料庫相容性層級維持在 150 以上。 若來自資料庫的所有查詢執行皆要停用資料列模式的記憶體授與意見反應，請在適用資料庫的內容中執行下列 SQL 陳述式：

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

若要針對源自資料庫的所有查詢執行重新啟用資料列模式記憶體授與意見反應，請在適用資料庫的內容中執行下列程式碼：

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

您也可以將 DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK 指定為 USE HINT 查詢提示，以針對特定查詢停用資料列模式記憶體授與意見反應。 例如：

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

USE HINT 查詢提示的優先順序會高於資料庫範圍設定或追蹤旗標設定。

啟用記憶體授與意見反應：持續性和百分位數

Azure SQL 資料庫和 SQL Server 2022 (16.x) 預設會啟用持續性和百分位數意見反應。

當執行查詢時，請針對連線的資料庫啟用 140 以上的資料庫相容性層級。 您可以透過 ALTER DATABASE 來變更此變更：

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

使用此功能持續性部分的每個資料庫，皆須啟用查詢存放區。

停用百分位數

若來自資料庫的所有查詢執行皆要停用記憶體授與意見反應百分位數，請在適用資料庫的內容中執行下列程式碼：

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT 的預設設定為 ON。

啟用持續性

來自資料庫的所有查詢執行作業皆要停用記憶體授與意見反應持續性。

請在適用資料庫的內容中執行下列命令：

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

記憶體授與意見反應持續性停用時，也會移除目前收集的意見反應。

MEMORY_GRANT_FEEDBACK_PERSISTENCE 的預設設定為 ON。

記憶體授與意見反應的考量

您可以查詢 sys.database_scoped_configurations，進而檢視您目前的設定。

注意

若 BATCH_MODE_MEMORY_GRANT_FEEDBACK 和 ROW_MODE_MEMORY_GRANT_FEEDBACK 皆設為 OFF，此功能將無法運作。

指定的意見反應資料目前保存在查詢存放區中，查詢存放區的用量需求有些許增加。

百分位數的記憶體授與會傾向降低溢寫機率。 由於該模式不再只是根據上一次執行，而是觀察前幾次執行，因此可能會增加記憶體用量，依幾次執行時記憶體授與需求的大幅差異而反覆變動工作負載。

自 2022 SQL Server 2022 (16.x) 起，當次要複本的查詢資料存放區啟用時，記憶體授與意見反應會感知可用性群組的次要複本。 記憶體授與意見反應可以不同方式套用至主要複本和次要複本。 但記憶體授與意見反應不會保存在次要複本上，且在容錯移轉時，舊主要複本的記憶體授與意見反應會套用至新的主要複本。 當次要複本成為主要複本時，次要複本所套用的所有意見反應皆會遺失。 如需詳細資訊，請參閱次要複本的查詢存放區。

相關內容

• SQL 資料庫中的智慧查詢處理
• 智慧型查詢處理功能的詳細資料
• RECONFIGURE (Transact-SQL)
• 效能的監視與微調
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)