智慧型查詢處理功能的詳細資訊
適用於： 
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體
本文會深入說明各種 智慧型查詢處理 (IQP) 的功能、版本資訊等詳細資訊。 智慧查詢處理 (IQP) 功能系列包含具有廣泛影響的功能，能夠以最少的實作投入量來採用，以改善現有工作負載的效能。
您可以為資料庫啟用適用的資料庫相容性層級，讓工作負載能自動滿足智慧查詢處理的資格。 您可以使用 Transact-SQL 設定此項目。 例如，若要將資料庫的相容性層級設定為 SQL Server 2022 (16.x)：
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 160;
如須進一步了解新版本所引進的變更，請參閱：
批次模式自適性聯結
適用於：SQL Server (版本 SQL Server 2017 (14.x) 起) 與 Azure SQL Database
批次模式自適性聯結功能可讓選擇的雜湊聯結或巢狀迴圈聯結方法，延後到已掃描的第一個輸入之後，方法是使用單一快取計畫。 自適性聯結運算子定義的閾值是用於決定何時要切換至巢狀迴圈計劃。 因此，您的計劃可在執行期間動態切換至較佳的聯結策略。
如需詳細資訊，包括如何在不變更相容性層級的情況下停用自適性聯結，請參閱了解自適性聯結。
MSTVF 交錯執行
適用於：SQL Server (版本 SQL Server 2017 (14.x) 起) 與 Azure SQL Database
多重陳述式資料表值函式 (MSTVF) 是一種使用者定義的函式類型，可接受參數、執行多個 T-SQL 陳述式和 RETURN 資料表。
交錯執行有利於處理因為固定基數估計值與 MSTVF 建立關聯而引起的工作負載效能問題。 利用交錯執行，我們可以使用函式的實際資料列計數制定更明智的下游查詢計劃決策。
MSTVF 在 SQL Server 2014 (12.x) 的固定基數估計值為 100，更舊的 SQL Server 版本則為 1。
交錯執行會變更單次查詢執行的最佳化和執行階段之間的單向界限，並讓計劃根據修改過的基數估計值調整。 在最佳化期間，如果資料庫引擎到交錯執行的候選項目，且使用多重陳述式資料表值函式 (MSTVF)，則會暫停最佳化、執行適用的樹狀子目錄、擷取精確的基數估計值，然後繼續下游作業的最佳化。
下圖說明即時查詢統計資料輸出，是整體執行計畫的子集，可顯示 MSTVF 固定基數估計值的影響
您可以查看實際的資料列流程與估計的資料列。 此計劃有三個重要區域 (流向為由右至左)：
- MSTVF 資料表掃描的固定估計值是 100 個資料列。 但此範例有 527,597 個資料列流經此 MSTVF 資料表掃描 (如即時查詢統計資料中所見的 527597 of 100 估計實值)，所以固定的估計值將會大幅扭曲。
- 至於巢狀迴圈作業，假定外部端聯結只會傳回 100 個資料列。 如果 MSTVF 實際傳回大量的資料列，使用完全不同的聯結演算法可能會更好。
- 至於雜湊比對作業，請注意小型警告符號，它在本例中表示溢出到磁碟。
對比之前的計劃與啟用交錯執行所產生的實際計劃：
- 請注意，MSTVF 資料表掃描現在反映精確的基數估計值。 亦請注意此資料表掃描的重新排序和其他作業。
- 而關於聯結演算法，我們已改從巢狀迴圈作業切換到雜湊比對作業，如果涉及大量的資料列，這樣更接近最佳狀態。
- 另請注意，我們不再顯示溢出警告，因為我們要根據 MSTVF 資料表掃描的實際資料列計數，授與更多記憶體。
符合交錯執行的陳述式
在交錯執行中參考陳述式的 MSTVF，目前必須是唯讀的，且不為資料修改作業的一部分。 此外，如果 MSTVF 未使用執行階段常數，則不適用於交錯執行。
交錯執行的優點
一般情況下，預估和實際資料列數目間的扭曲愈高，加上下游計劃作業的數目，對效能的影響就愈大。
一般而言，交錯執行有益於下列情況的查詢：
- 中繼結果集的預估和實際資料列數目間有很大的扭曲 (本例中為 MSTVF)。
- 而整體查詢對中繼結果的大小變更十分敏感。 這通常發生在查詢計劃有樹狀子目錄的複雜樹狀結構時。
僅僅 MSTVF 的
SELECT *不會受益於交錯執行。
交錯執行的負擔
負擔應該最小，甚至完全沒有。 在導入交錯執行前，MSTVF 已被具體化，不過差異在於，現在我們要允許延遲最佳化，然後運用具體化資料列集的基數估計值。 就像任何會影響變更的計劃一樣，有些計劃的變更，會識我們以較佳的樹狀子目錄基數，得到整體查詢更差的計劃。 風險降低可以包括還原相容性層級，或使用查詢存放區強制執行非迴歸版的計畫。
交錯執行和連續執行
快取交錯執行計畫後，第一次執行即修改過估計值的計畫會用於連續執行，不必將交錯執行重新具現化。
追蹤交錯執行活動
您可以在實際的查詢執行計劃中看到使用方式屬性：
| 執行計劃屬性 | 描述 |
|---|---|
| ContainsInterleavedExecutionCandidates | 適用於 QueryPlan 節點。 為 true 時，表示計劃包含交錯執行候選項目。 |
| IsInterleavedExecuted | 位於 TVF 節點 RelOp 之下 RuntimeInformation 元素的屬性。 為 true 時，這表示作業已具體化為交錯執行作業的一部分。 |
您也可以透過下列擴充事件追蹤交錯執行項目：
| XEvent | 描述 |
|---|---|
interleaved_exec_status |
交錯執行進行時會引發這個事件。 |
interleaved_exec_stats_update |
此事件會描述由交錯執行更新的基數估計值。 |
Interleaved_exec_disabled_reason |
當具有交錯執行可能候選項目的查詢不會實際取得交錯執行時，就會引發這個事件。 |
必須執行查詢，才能讓交錯執行修改 MSTVF 基數估計值。 不過，有透過 ContainsInterleavedExecutionCandidates 執行程序表屬性的交錯執行候選項目時，仍會顯示預估執行計劃。
交錯執行快取
如果從快取清除或收回計劃，就會在執行查詢時重新整理使用交錯執行的編譯。
使用 OPTION (RECOMPILE) 的陳述式會建立使用交錯執行的新計畫，且不會對它進行快取。
交錯執行和 查詢存放區互操作性
使用交錯執行的計劃可以強制執行。 此計劃是根據初始執行更正基數估計值的版本。
停用交錯執行而不變更相容性層級
您可以在資料庫或陳述式的範圍停用交錯執行，同時仍將資料庫相容性層級維持在 140 以上。 若要針對源自資料庫的所有查詢執行停用交錯執行，請在適用資料庫的內容中執行下列程式碼：
-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;
-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = OFF;
啟用時，此設定在 sys.database_scoped_configurations 中會顯示為已啟用。 若要針對源自資料庫的所有查詢執行重新啟用交錯執行，請在適用資料庫的內容中執行下列程式碼：
-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;
-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = ON;
您也可以將 DISABLE_INTERLEAVED_EXECUTION_TVF 指定為 USE HINT 查詢提示，以針對特定查詢停用交錯執行。 例如：
SELECT [fo].[Order Key], [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
'1-01-2013',
'10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
AND [fo].[City Key] = [foo].[City Key]
AND [fo].[Customer Key] = [foo].[Customer Key]
AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
AND [fo].[Order Date Key] = [foo].[Order Date Key]
AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
AND [fo].[Picker Key] = [foo].[Picker Key]
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));
USE HINT 查詢提示的優先順序會高於資料庫範圍設定或追蹤旗標設定。
純量 UDF 內嵌
適用於：SQL Server (版本 SQL Server 2019 (15.x) 起) 與 Azure SQL Database
純量 UDF 內嵌會自動將純量 UDF 轉換成關聯運算式。 將它們內嵌在呼叫的 SQL 查詢中。 此轉換可改善利用純量 UDF 的工作負載效能。 純量 UDF 內嵌有益於 UDF 內的成本型最佳化作業。 其結果會是有效率、集合導向的平行處理，而不是效率不彰、反覆執行的序列執行計畫。 根據預設，若資料庫相容性層級為 150 以上，就會啟用此功能。
如需詳細資訊，請參閱純量 UDF 內嵌。
資料表變數延後編譯
適用於：SQL Server (版本 SQL Server 2019 (15.x) 起) 與 Azure SQL Database
資料表變數延遲編譯可針對參考資料表變數的查詢，提升計劃品質和整體效能。 在最佳化和初始計畫編譯期間，此功能會根據實際資料表變數的資料列計數，傳播基數估計值。 這個確切的資料列計數資訊接著將用於最佳化下游計畫作業。
使用資料表變數延後編譯時，會延遲編譯參考資料表變數的陳述式，直到第一次實際執行陳述式為止。 此延後編譯行為與暫存資料表的行為相同。 這項變更會導致使用實際基數，而不使用原始的單一資料列猜測。
若要啟用資料表變數延遲編譯，請在查詢執行時，針對您所連線的資料庫，啟用 150 以上的資料庫相容性層級。
資料表變數延遲編譯不會變更資料表變數的任何其他特性。 例如，此功能不會在資料表變數中新增資料行統計資料。
資料表變數延遲編譯不會增加重新編譯頻率， 而是會在初始編譯的位置移位。 產生的快取計畫是根據初始延遲編譯資料表變數的資料列計數所產生。 快取計畫是由連續查詢重複使用。 將會重複使用計畫，直到該計畫被收回或重新編譯為止。
用於初始計畫編譯的資料表變數資料列計數，代表一個可能不同於固定資料列計數猜測的一般值。 如果不同，則下游作業將會受益。 若資料表變數資料列計數在每次執行時都大幅相異，則此功能可能無法改善效能。
停用資料表變數的延遲編譯，而無須變更相容性層級
請在資料庫或陳述式的範圍停用資料表變數延遲編譯，同時仍將資料庫相容性層級維持在 150 以上。 若要針對源自資料庫的所有查詢執行停用資料表變數延遲編譯，請在適用資料庫的內容中執行下列程式碼：
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;
若要針對源自資料庫的所有查詢執行重新啟用資料表變數延遲編譯，請在適用資料庫的內容中執行下列程式碼：
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;
您也可以透過將 DISABLE_DEFERRED_COMPILATION_TV 指派為 USE HINT 查詢提示，為特定查詢停用資料表變數延遲編譯。 例如：
DECLARE @LINEITEMS TABLE
(L_OrderKey INT NOT NULL,
L_Quantity INT NOT NULL
);
INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;
SELECT O_OrderKey,
O_CustKey,
O_OrderStatus,
L_QUANTITY
FROM
ORDERS,
@LINEITEMS
WHERE O_ORDERKEY = L_ORDERKEY
AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));
參數敏感度計劃最佳化
適用於：SQL Server 2022 (16.x) Azure SQL 資料庫 Azure SQL 受控執行個體
參數敏感度計畫 (PSP) 最佳化是智慧型查詢處理系列功能的一部分。 在參數化查詢採用單一快取計畫，但對於所有可能的傳入參數值而言並非最佳選項，則上述功能可處理此種案例。 這種情況涉及非統一資料散發。
- 如需 PSP 最佳化的詳細資訊，請參閱參數敏感度計畫最佳化 (機器翻譯)。
- 如需參數化和參數敏感度的詳細資訊，請參閱參數敏感度和參數和執行計畫重複使用。
近似查詢處理
近似查詢處理是新的功能系列。 它會在回應性比絕對精確度更重要的大型資料集間執行彙總作業。 例如，在 10 億筆資料列中計算 COUNT(DISTINCT()) ，以顯示在儀表板上。 在此情況下，絕對精確度不重要，但回應性非常重要。
近似的相異計數
適用於：SQL Server (版本 SQL Server 2019 (15.x) 起) 與 Azure SQL Database
新的 APPROX_COUNT_DISTINCT 彙總函式會傳回群組中唯一非 Null 值的近似數目。
此功能從 SQL Server 2019 (15.x) 開始提供，不論相容性層級為何皆然。
如需詳細資訊，請參閱 APPROX_COUNT_DISTINCT (Transact-SQL)。
近似百分位數
適用於：SQL Server (版本 SQL Server 2022 (16.x) 起) 與 Azure SQL Database
這些彙總函式會計算具有可接受排名型錯誤界限的大型資料集百分位數，以協助使用近似百分位數彙總函式，來做出快速決策。
如需詳細資訊，請參閱 APPROX_PERCENTILE_DISC (Transact-SQL) 和 APPROX_PERCENTILE_CONT (Transact-SQL) (均為機器翻譯)
資料列存放區上的批次模式
適用於：SQL Server (版本 SQL Server 2019 (15.x) 起) 與 Azure SQL Database
資料列存放區上的批次模式可針對分析工作負載啟用批次模式執行功能，而不需要資料行存放區索引。 這項功能支援用於磁碟上堆積和 B 型樹狀結構索引的批次模式執行和點陣圖篩選。 資料列存放區上的批次模式可支援所有現有具備批次模式功能的運算子。
注意
文件通常會使用「B 型樹狀結構」一詞來指稱索引。 在資料列存放區索引中，資料庫引擎會實作 B+ 樹狀結構。 這不適用於資料行存放區索引或經記憶體最佳化的資料表。 如需詳細資訊，請參閱 SQL Server 及 Azure SQL 索引架構與設計指南。
批次模式執行概觀
SQL Server 2012 (11.x) 引進了新功能，可加速分析工作負載：資料行存放區索引。 每個 SQL Server 後續版本的資料行存放區索引，其使用案例和效能都會增加。 如在資料表上建立資料行存放區索引，可以改善分析工作負載的效能。 但使用了兩種相關卻相異的技術：
- 使用資料行存放區索引，分析查詢只存取其所需資料行中的資料。 使用資料行存放區格式的頁面壓縮，比傳統的資料列存放區索引壓縮更有效。
- 使用批次模式處理，查詢運算子處理資料更有效率。 它們會批次處理資料列，而不是一次處理一筆資料列。 許多其他可擴縮性改善也與批次模式處理繫結。 如需批次模式的詳細資訊，請參閱執行模式。
這兩組功能一同使用，改善輸入/輸出 (I/O) 和 CPU 使用率：
- 使用資料行存放區索引，可在記憶體中放置更多資料。 這會減少 I/O 工作負載。
- 批次模式處理可更有效率的使用 CPU。
這兩項技術會盡可能地利用彼此的優勢。 例如，批次模式彙總可作為資料行存放區索引的一部分進行評估。 壓縮過的資料行存放區資料也會透過更有效率地搭配批次模式聯結和批次模式彙總，使用執行長度限制編碼來處理。
但是，請務必了解這兩個功能是獨立的：
- 您可以取得使用資料行存放區索引的資料列模式計畫。
- 您可以取得只使用資料列存放區索引的批次模式計畫。
兩種功能一起使用，通常會取得最佳結果。 在 SQL Server 2019 (15.x) 推出前，SQL Server 查詢最佳化工具仍認為，批次模式處理只適用於「包含至少一個具有資料行存放區索引」的資料表查詢。
某些應用程式可能不適合資料行存放區索引。 應用程式可能使用資料行存放區索引不支援的一些其他功能。 例如，就地修改與資料行存放區壓縮不相容。 因此，具有叢集資料行存放區索引的資料表不支援觸發程序。 更重要的是，資料行存放區索引會增加 DELETE 和 UPDATE 陳述式的額外負荷。
針對某些混合式交易分析工作負載，交易工作負載的額外負荷仍超過資料行存放區索引的好處。 此類情節可以透過單獨採用批次模式處理，而受益於改善的 CPU 使用率。 這就是為什麼資料列存放區上的批次模式功能考慮所有查詢的批次模式，而不管涉及哪種索引類型。
可從資料列存放區批次模式受益的工作負載
下列工作負載可從資料列存放區的批次模式受益：
- 工作負載有很大部分是由分析查詢構成。 通常，這些查詢會使用例如聯結或彙總的運算子，可處理數十萬筆或更多的資料列。
- CPU 繫結的工作負載。 如果瓶頸為 I/O，仍建議您盡可能考慮資料行存放區索引。
- 建立資料行存放區索引會將過多的額外負荷新增至您工作負載的交易式部分。 或者，建立資料行存放區索引不可行，因為您應用程式的相依功能尚不支援資料行存放區索引。
注意
資料列存放區上的批次模式僅協助減少 CPU 使用量。 瓶頸若與 I/O 相關，且資料尚未快取 (「冷」快取)，則資料列存放區上的批次模式將無法改善查詢已耗用時間。 同樣的，若電腦上沒有足夠記憶體可供快取所有資料，則效能也不太可能獲得改善。
資料列存放區上的批次模式有哪些變更？
資料列存放區上的批次模式需要資料庫相容性層級達到 150。
即使查詢並未存取任何具有資料行存放區索引的資料表，查詢處理器也會使用啟發學習法來決定是否要考慮使用批次模式。 啟發學習法包含下列檢查：
- 對資料表大小、使用的運算子，以及輸入查詢中估計基數的初始檢查。
- 最佳化工具為查詢探索更便宜的新計劃時所帶來的額外檢查點。 若這些替代方案並未大量使用批次模式，則最佳化工具會停止探索批次模式的替代項目。
如果使用資料列存放區上的批次模式，您在查詢計畫中看到的實際執行模式如同批次模式。 掃描運算子會使用批次模式處理磁碟上的堆積和 B 型樹狀結構索引。 此批次模式掃描可評估批次模式點陣圖篩選。 您也可能會在計畫中看到其他批次模式運算子。 例如雜湊聯結、雜湊式彙總、排序、Window 彙總、篩選、串連和計算純量運算子。
備註
查詢計畫並非一律使用批次模式。 查詢最佳化工具可能會判定批次模式對查詢沒有幫助。
查詢最佳化工具的搜尋空間正在變更。 因此，如果收到資料列模式計畫，它可能和您在較低相容性層級中取得的計畫不一樣。 而如果您收到批次模式計畫，它可能和您以資料行存放區索引取得的計畫不一樣。
針對混合資料行存放區和資料列存放區索引的查詢，計畫可能也會因為新的批次模式資料列存放區掃描而變更。
資料列存放區掃描上新批次模式的目前限制：
- 對於記憶體內部 OLTP 資料表，或是磁碟上堆積與 B 型樹狀結構以外的任何索引，它無法生效。
- 它也無法在擷取或篩選大型物件 (LOB) 資料行時生效。 這項限制包含疏鬆資料行集和 XML 資料行。
甚至有具備資料行存放區索引但不使用批次模式的查詢。 例如包含資料指標的查詢。 這些相同排除項目也會擴及資料列存放區上的批次模式。
設定資料列存放區上的批次模式
BATCH_MODE_ON_ROWSTORE 資料庫範圍設定預設為「開啟」。
您可以在資料列存放區上停用批次模式，而不需要變更資料庫相容性層級：
-- Disabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;
-- Enabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;
您可以透過資料庫範圍設定，停用資料列存放區上的批次模式。 但使用 ALLOW_BATCH_MODE 查詢提示仍可覆寫查詢層級的設定。 下列範例會啟用資料列存放區上的批次模式，即使已透過資料庫範圍設定停用該項功能：
SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));
您也可以使用 DISALLOW_BATCH_MODE 查詢提示，針對特定查詢停用資料列存放區上的批次模式。 請參閱下列範例：
SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));
查詢處理意見反應功能
查詢處理意見反應功能屬於智慧查詢處理系列功能的一部分。
查詢處理意見反應為一項流程，當 SQL Server、Azure SQL Database 中的查詢處理器及 Azure SQL 受控執行個體使用查詢執行相關歷程資料，以判斷查詢是否要接收一或多項編譯和執行方式變更的協助。 效能數據會在 查詢存放區 中收集，並有各種建議來改善查詢執行。 如果成功，我們會將這些修改保存在記憶體中的磁碟和/或 查詢存放區 中，以供日後使用。 若建議提供的改善不足，則會予以捨棄，且查詢會繼續執行而不納入該意見反應。
如需不同 SQL Server 版本或 Azure SQL 資料庫或 Azure SQL 受控執行個體中有哪些查詢處理意見反應功能的相關資訊，請參閱 SQL Database 中的智慧型查詢處理，或針對每個意見反應功能參閱下列文章。
記憶體授與意見反應
過去 SQL Server 主要版本中已分波引進了記憶體授與意見反應。
批次模式記憶體授與意見反應
如需 Batch 模式記憶體授與意見反應的相關資訊，請參閱批次模式記憶體授與意見反應 (機器翻譯)。
資料列模式記憶體授與意見反應
如需資料列模式記憶體授與意見反應的相關資訊，請參閱資料列模式記憶體授與意見反應。
百分位數和持續性模式記憶體授與意見反應
如需百分位數和持續性模式記憶體授與意見反應的相關資訊，請參與百分位數和持續性模式記憶體授與意見反應 (機器翻譯)。
平行處理原則程度 (DOP) 意見反應
如需 DOP 意見反應的相關資訊，請參閱平行處理原則程度 (DOP) 意見反應 (機器翻譯)。
基數估計 (CE) 意見反應
如需 CE 意見反應的相關資訊，請參閱基數估計 (CE) 意見反應 (機器翻譯)。
使用查詢存放區強制執行最佳化計畫
如需使用查詢存放區強制執行最佳化計畫的相關資訊，請參閱使用查詢存放區強制執行最佳化計畫 (機器翻譯)。