SQL 資料庫中的智慧查詢處理Intelligent query processing in SQL databases

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

智慧查詢處理 (IQP) 功能系列包含具有廣泛影響的功能,能夠以最少的實作投入量來採用,以改善現有工作負載的效能。The intelligent query processing (IQP) feature family includes features with broad impact that improve the performance of existing workloads with minimal implementation effort to adopt.

智慧查詢處理

您可以為資料庫啟用適用的資料庫相容性層級,讓工作負載能自動滿足智慧查詢處理的資格。You can make workloads automatically eligible for intelligent query processing by enabling the applicable database compatibility level for the database. 您可以使用 Transact-SQLTransact-SQL 設定此項目。You can set this using Transact-SQLTransact-SQL. 例如:For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

下表詳述了所有智慧查詢處理功能,以及這些功能對於資料庫相容性層級的任何要求。The following table details all intelligent query processing features, along with any requirement they have for database compatibility level.

IQP 功能IQP Feature Azure SQL Database 支援Supported in Azure SQL Database SQL Server 支援Supported in SQL Server 說明Description
自適性聯結 (批次模式)Adaptive Joins (Batch Mode) 是,屬於相容性層級 140Yes, under compatibility level 140 是,自 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始屬於相容性層級 140Yes, starting in SQL Server 2017 (14.x)SQL Server 2017 (14.x) under compatibility level 140 自適性聯結會在執行階段,依據實際輸入列而機動選取聯結類型。Adaptive joins dynamically select a join type during runtime based on actual input rows.
近似的相異計數Approximate Count Distinct 是,公開預覽Yes, public preview 是,從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 開始Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 為巨量資料案例提供約略的 COUNT DISTINCT,享有高效能及低磁碟使用量的好處。Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint.
資料列存放區上的批次模式Batch Mode on Rowstore 是,屬於相容性層級 150,公開預覽Yes, under compatibility level 150, public preview 是,自 SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 開始屬於相容性層級 150,公開預覽Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 under compatibility level 150, public preview 為耗用大量 CPU 的關聯式 DW 工作負載提供批次模式,而且不需要資料行存放區索引。Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
交錯執行Interleaved Execution 是,屬於相容性層級 140Yes, under compatibility level 140 是,自 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始屬於相容性層級 140Yes, starting in SQL Server 2017 (14.x)SQL Server 2017 (14.x) under compatibility level 140 使用在第一次編譯時遇到的多重陳述式資料表值函式實際基數,而不是定點猜測。Use the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.
記憶體授與意見反應 (批次模式)Memory Grant Feedback (Batch Mode) 是,屬於相容性層級 140Yes, under compatibility level 140 是,自 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 開始屬於相容性層級 140Yes, starting in SQL Server 2017 (14.x)SQL Server 2017 (14.x) under compatibility level 140 若批次模式查詢有作業會溢出到磁碟,請新增記憶體以防執行中斷。If a batch mode query has operations that spill to disk, add more memory for consecutive executions. 若查詢耗用了 > 50% 配置給它的記憶體,請縮減記憶體授與端,以防執行中斷。If a query wastes > 50% of the memory allocated to it, reduce the memory grant side for consecutive executions.
記憶體授與意見反應 (資料列模式)Memory Grant Feedback (Row Mode) 是,屬於相容性層級 150,公開預覽Yes, under compatibility level 150, public preview 是,自 SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 開始屬於相容性層級 150,公開預覽Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 under compatibility level 150, public preview 若資料列模式查詢有作業會溢出到磁碟,請新增記憶體以防執行中斷。If a row mode query has operations that spill to disk, add more memory for consecutive executions. 若查詢耗用了 > 50% 配置給它的記憶體,請縮減記憶體授與端,以防執行中斷。If a query wastes > 50% of the memory allocated to it, reduce the memory grant side for consecutive executions.
純量 UDF 內嵌Scalar UDF Inlining No 是,自 SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.1 開始屬於相容性層級 150,公開預覽Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.1 under compatibility level 150, public preview 純量 UDF 會被轉換成「內嵌」在呼叫查詢中的對等關聯運算式,而這通常可讓效能大幅提升。Scalar UDFs are transformed into equivalent relational expressions that are "inlined" into the calling query, often resulting in significant performance gains.
資料表變數延後編譯Table Variable Deferred Compilation 是,屬於相容性層級 150,公開預覽Yes, under compatibility level 150, public preview 是,自 SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 開始屬於相容性層級 150,公開預覽Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 under compatibility level 150, public preview 使用在第一次編譯時遇到的資料表值函式實際基數,而不是定點猜測。Use the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.

批次模式自適性聯結Batch mode Adaptive joins

批次模式自適性聯結功能可讓選擇的雜湊聯結或巢狀迴圈聯結方法,延後到已掃描的第一個輸入之後,方法是使用單一快取計畫。The batch mode Adaptive Joins feature enables the choice of a Hash Join or Nested Loops Join method to be deferred until after the first input has been scanned, by using a single cached plan. 自適性聯結運算子定義的閾值是用於決定何時要切換至巢狀迴圈計劃。The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. 因此,您的計劃可在執行期間動態切換至較佳的聯結策略。Your plan can therefore dynamically switch to a better join strategy during execution.

如需詳細資訊,包括如何在不變更相容性層級的情況下停用自適性聯結,請參閱了解自適性聯結For more information, including how to disable Adaptive joins without changing the compatibility level, see Understanding Adaptive joins.

批次模式記憶體授與意見反應Batch mode memory grant feedback

SQL ServerSQL Server 中的查詢後續執行計劃,包含執行所需的最小記憶體,以及能將所有資料列納入記憶體的理想記憶體授與大小。A query's post-execution plan in SQL ServerSQL Server includes the minimum required memory needed for execution and the ideal memory grant size to have all rows fit in memory. 當調整的記憶體授與大小不正確時,就會降低效能。Performance suffers when memory grant sizes are incorrectly sized. 授與過多會浪費記憶體並降低並行。Excessive grants result in wasted memory and reduced concurrency. 記憶體授與不足會佔用大量磁碟資源。Insufficient memory grants cause expensive spills to disk. 透過處理重複的工作負載,批次模式記憶體授與意見反應會重新計算查詢實際所需的記憶體,然後更新快取計劃的授與值。By addressing repeating workloads, batch mode memory grant feedback recalculates the actual memory required for a query and then updates the grant value for the cached plan. 執行相同的查詢陳述式時,此查詢會使用修訂過的記憶體授權大小,減少影響並行的過多記憶體授與,並修正導致佔用大量磁碟資源的記憶體授與低估。When an identical query statement is executed, the query uses the revised memory grant size, reducing excessive memory grants that impact concurrency and fixing underestimated memory grants that cause expensive spills to disk. 下圖顯示使用批次模式調整記憶體授與意見反應的一個範例。The following graph shows one example of using batch mode adaptive memory grant feedback. 由於高溢出,第一次執行查詢的持續時間為「88 秒」 :For the first execution of the query, duration was 88 seconds due to high spills:

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 秒降下),溢出全部移除,且授與較高:With memory grant feedback enabled, for the second execution, duration is 1 second (down from 88 seconds), spills are removed entirely, and the grant is higher:

沒有溢出

記憶體授與意見反應調整大小Memory grant feedback sizing

針對記憶體授與過多的狀況,如果授與的記憶體超過實際使用記憶體大小的兩倍,記憶體授與回饋便會重新計算記憶體授與並更新快取計劃。For an excessive memory grant condition, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant and update the cached plan. 記憶體授與小於 1 MB 的計劃不會重新計算處理超額問題。Plans with memory grants under 1 MB will not be recalculated for overages. 針對會導致批次模式運算子磁碟溢出的記憶體授與大小不足狀況,記憶體授與回饋會觸發記憶體授與的重新計算。For an insufficiently sized memory grant condition, that result in a spill to disk for batch mode operators, memory grant feedback will trigger a recalculation of the memory grant. 溢出事件會報告到記憶體授與回饋,且可以透過 spilling_report_to_memory_grant_feedback xEvent 顯示。Spill events are reported to memory grant feedback and can be surfaced via the spilling_report_to_memory_grant_feedback xEvent. 此事件會傳回計畫的節點識別碼,以及該節點溢出的資料大小。This event returns the node ID from the plan and spilled data size of that node.

記憶體授與意見反應與參數敏感的情況Memory grant feedback and parameter sensitive scenarios

不同的參數值可能也需要不同的查詢計劃,以維持最佳狀態。Different parameter values may also require different query plans in order to remain optimal. 這類型的查詢即定義為「參數敏感」。This type of query is defined as "parameter-sensitive." 凡是參數敏感的計劃,如果記憶體授與意見反應有不穩定的記憶體需求,就會在查詢上自行停用。For parameter-sensitive plans, memory grant feedback will disable itself on a query if it has unstable memory requirements. 計劃會在查詢重複數輪後停用,透過監視 memory_grant_feedback_loop_disabled xEvent 可觀察到此狀況。The plan is disabled after several repeated runs of the query and this can be observed by monitoring the memory_grant_feedback_loop_disabled xEvent. 如需參數探查和參數敏感性的詳細資訊,請參閱查詢處理架構指南For more information about parameter sniffing and parameter sensitivity, refer to the Query Processing Architecture Guide.

記憶體授與意見反應快取Memory grant feedback caching

意見反應可以儲存在快取計劃中供單次執行之用。Feedback can be stored in the cached plan for a single execution. 這是該陳述式的連續執行,但得益自記憶體授與意見反應的調整。It is the consecutive executions of that statement, however, that benefit from the memory grant feedback adjustments. 此功能適用於重複執行的陳述式。This feature applies to repeated execution of statements. 記憶體授與意見反應僅會變更快取的計劃。Memory grant feedback will change only the cached plan. 查詢存放區目前並未擷取變更。Changes are currently not captured in the Query Store. 如已從快取收回計劃,則不保存意見反應。Feedback is not persisted if the plan is evicted from cache. 如有容錯移轉,也會遺失意見反應。Feedback will also be lost if there is a failover. 使用 OPTION (RECOMPILE) 的陳述式會建立新的計劃,而不會對它進行快取。A statement using OPTION (RECOMPILE) creates a new plan and does not cache it. 因為它不是快取而來,所以不會產生記憶體授與意見反應,也不會儲存供編譯及執行。Since it is not cached, no memory grant feedback is produced and it is not stored for that compilation and execution. 不過,如果有快取並重複執行沒有使用 OPTION (RECOMPILE) 的對等陳述式 (亦即使用相同的查詢雜湊),則連續的陳述式可得益於記憶體授與回饋。However, if an equivalent statement (that is, with the same query hash) that did not use OPTION (RECOMPILE) was cached and then re-executed, the consecutive statement can benefit from memory grant feedback.

追蹤記憶體授與意見反應活動Tracking memory grant feedback activity

您可以使用 memory_grant_updated_by_feedback xEvent 來追蹤記憶體授與回饋事件。You can track memory grant feedback events using the memory_grant_updated_by_feedback xEvent. 此事件會追蹤目前的執行計數記錄、記憶體授與意見反應更新計劃的次數,以及記憶體授與意見反應修改快取計劃前後的理想額外記憶體授權。This event tracks the current execution count history, the number of times the plan has been updated by memory grant feedback, the ideal additional memory grant before modification and the ideal additional memory grant after memory grant feedback has modified the cached plan.

記憶體授與意見反應、資源管理員和查詢提示Memory grant feedback, resource governor and query hints

實際的記憶體授與會接受由資源管理員或查詢提示所決定的查詢記憶體限制。The actual memory granted honors the query memory limit determined by the resource governor or query hint.

停用批次模式記憶體授與意見反應,而不變更相容性層級Disabling batch mode memory grant feedback without changing the compatibility level

您可以在資料庫或陳述式的範圍停用記憶體授與意見反應,同時仍將資料庫相容性層級維持在 140 以上。Memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. 若要針對源自資料庫的所有查詢執行停用批次模式的記憶體授與意見反應,請在適用資料庫的內容中執行下列程式碼:To disable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

-- 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 中會顯示為已啟用。When enabled, this setting will appear as enabled in sys.database_scoped_configurations.

若要針對源自資料庫的所有查詢執行重新啟用批次模式的記憶體授與意見反應,請在適用資料庫的內容中執行下列程式碼:To re-enable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

-- 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 查詢提示,以針對特定查詢停用批次模式的記憶體授與意見反應。You can also disable batch mode memory grant feedback for a specific query by designating DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. 例如:For example:

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

USE HINT 查詢提示的優先順序高於資料庫範圍設定或追蹤旗標設定。A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

資料列模式記憶體授與意見反應Row mode memory grant feedback

適用於: SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL Database (公開預覽)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL DatabaseAzure SQL Database (public preview)

注意

資料列模式記憶體授與回應是公開預覽版功能。Row mode memory grant feedback is a public preview feature.

調整批次和資料列模式運算子的記憶體授與大小,藉此在批次模式記憶體授與意見反應功能上展開資料列模式記憶體授與意見反應。Row mode memory grant feedback expands on the batch mode memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators.

若要在 Azure SQL DatabaseAzure SQL Database 中啟用資料列模式記憶體授與意見反應的公開預覽功能,請在執行查詢時,針對您所連線的資料庫,啟用資料庫相容性層級 150。To enable the public preview of row mode memory grant feedback in Azure SQL DatabaseAzure SQL Database, enable database compatibility level 150 for the database you are connected to when executing the query.

透過 memory_grant_updated_by_feedback XEvent 將可以看到資料列模式記憶體授與意見反應活動。Row mode memory grant feedback activity will be visible via the memory_grant_updated_by_feedback XEvent.

從資料列模式記憶體授與意見反應開始,針對實際執行後計畫將會顯示兩個新的查詢計畫屬性:IsMemoryGrantFeedbackAdjustedLastRequestedMemory,它們會新增至 MemoryGrantInfo 查詢計畫 XML 元素。Starting with row mode memory grant feedback, two new query plan attributes will be shown for actual post-execution plans: IsMemoryGrantFeedbackAdjusted and LastRequestedMemory, which are added to the MemoryGrantInfo query plan XML element.

LastRequestedMemory 會在查詢執行之前,顯示授與的記憶體 (KB)。LastRequestedMemory shows the granted memory in Kilobytes (KB) from the prior query execution. IsMemoryGrantFeedbackAdjusted 屬性可讓您針對實際查詢執行計劃內的陳述式,檢查記憶體授與意見反應的狀態。IsMemoryGrantFeedbackAdjusted attribute allows you to check the state of memory grant feedback for the statement within an actual query execution plan. 此屬性中顯示的值如下:Values surfaced in this attribute are as follows:

IsMemoryGrantFeedbackAdjusted 值IsMemoryGrantFeedbackAdjusted Value DescriptionDescription
否:第一次執行No: First Execution 記憶體授與意見反應不會針對第一次編譯和相關聯的執行,調整記憶體。Memory grant feedback does not adjust memory for the first compile and associated execution.
否:精確授與No: Accurate Grant 如果沒有溢出到磁碟,而且陳述式使用至少 50% 的授與的記憶體,則不會觸發記憶體授與意見反應。If there is no spill to disk and the statement uses at least 50% of the granted memory, then memory grant feedback is not triggered.
否:意見反應已停用No: Feedback disabled 如果記憶體授與意見反應持續遭到觸發,而且在記憶體增加和減少記憶體的作業之間波動,我們將會停用陳述式的記憶體授與意見反應。If memory grant feedback is continually triggered and fluctuates between memory-increase and memory-decrease operations, we will disable memory grant feedback for the statement.
是:調整Yes: Adjusting 已套用記憶體授與意見反應,而且可能會針對下一次執行進一步調整。Memory grant feedback has been applied and may be further adjusted for the next execution.
是:穩定Yes: Stable 已套用記憶體授與意見反應,而且授與的記憶體現已穩定,表示針對上次執行授與的記憶體就是針對目前執行授與的記憶體。Memory grant feedback has been applied and granted memory is now stable, meaning that what was last granted for the previous execution is what was granted for the current execution.

注意

在 17.9 版與更新版本中,公開預覽版資料列模式記憶體授與意見反應計劃屬性在 SQL Server Management StudioSQL Server Management Studio 圖形化查詢執行計劃中是可見的。The public preview row mode memory grant feedback plan attributes are visible in SQL Server Management StudioSQL Server Management Studio graphical query execution plans in versions 17.9 and higher.

停用資料列模式記憶體授與意見反應,而不變更相容性層級Disabling row mode memory grant feedback without changing the compatibility level

您可以在資料庫或陳述式的範圍中停用資料列模式記憶體授與意見反應,同時仍將資料庫相容性層級維持在 150 以上。Row mode memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 150 and higher. 若要針對源自資料庫的所有查詢執行停用資料列模式記憶體授與意見反應,請在適用資料庫的內容中執行下列程式碼:To disable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

若要針對源自資料庫的所有查詢執行重新啟用資料列模式記憶體授與意見反應,請在適用資料庫的內容中執行下列程式碼:To re-enable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

您也可以將 DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK 指定為 USE HINT 查詢提示,以針對特定查詢停用資料列模式記憶體授與意見反應。You can also disable row mode memory grant feedback for a specific query by designating DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. 例如:For example:

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

USE HINT 查詢提示的優先順序高於資料庫範圍設定或追蹤旗標設定。A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

MSTVF 交錯執行Interleaved execution for MSTVFs

利用交錯執行,我們可以使用函式的實際資料列計數制定更明智的下游查詢計劃決策。With interleaved execution, the actual row counts from the function are used to make better-informed downstream query plan decisions. 如需多重陳述式資料表值函式 (MSTVF) 的詳細資訊,請參閱資料表值函式For more information on multi-statement table-valued functions (MSTVFs), see Table-valued functions.

交錯執行會變更單次查詢執行的最佳化和執行階段之間的單向界限,並讓計劃根據修改過的基數估計值調整。Interleaved execution changes the unidirectional boundary between the optimization and execution phases for a single-query execution and enables plans to adapt based on the revised cardinality estimates. 在最佳化期間,如果我們遇到交錯執行的候選項目,目前是多重陳述式資料表值函式 (MSTVF) ,我們會暫停最佳化、執行適用的樹狀子目錄、擷取精確的基數估計值,然後繼續下游作業的最佳化。During optimization if we encounter a candidate for interleaved execution, which is currently multi-statement table-valued functions (MSTVFs), we will pause optimization, execute the applicable subtree, capture accurate cardinality estimates, and then resume optimization for downstream operations.

MSTVF 從 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始具有固定的基數估計值 100,在舊版 SQL ServerSQL Server 中則為 1。MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 1 for earlier SQL ServerSQL Server versions. 交錯執行有利於處理因為這些固定基數估計值與 MSTVF 建立關聯而引起的工作負載效能問題。Interleaved execution helps workload performance issues that are due to these fixed cardinality estimates associated with MSTVFs. 如需 MSTVF 的詳細資訊,請參閱建立使用者定義函式 (資料庫引擎)For more information on MSTVFs, see Create User-defined Functions (Database Engine).

下圖說明即時查詢統計資料輸出,它是整體執行計劃的子集,顯示 MSTVF 固定基數估計值的影響。The following image depicts a Live Query Statistics output, a subset of an overall execution plan that shows the impact of fixed cardinality estimates from MSTVFs. 您可以查看實際的資料列流程與估計的資料列。You can see the actual row flow vs. estimated rows. 此計劃有三個重要區域 (流向為由右至左):There are three noteworthy areas of the plan (flow is from right to left):

  1. MSTVF 資料表掃描的固定估計值是 100 個資料列。The MSTVF Table Scan has a fixed estimate of 100 rows. 但此範例有 527,597 個資料列流經此 MSTVF 資料表掃描 (如即時查詢統計資料中所見的 527597 of 100 估計實值),所以固定的估計值將會大幅扭曲。For this example, however, there are 527,597 rows flowing through this MSTVF Table Scan, as seen in Live Query Statistics via the 527597 of 100 actual of estimated - so the fixed estimate is significantly skewed.
  2. 至於巢狀迴圈作業,假定外部端聯結只會傳回 100 個資料列。For the Nested Loops operation, only 100 rows are assumed to be returned by the outer side of the join. 如果 MSTVF 實際傳回大量的資料列,使用完全不同的聯結演算法可能會更好。Given the high number of rows actually being returned by the MSTVF, you are likely better off with a different join algorithm altogether.
  3. 至於雜湊比對作業,請注意小型警告符號,它在本例中表示溢出到磁碟。For the Hash Match operation, notice the small warning symbol, which in this case is indicating a spill to disk.

資料列流程與估計的資料列

對比之前的計劃與啟用交錯執行所產生的實際計劃:Contrast the prior plan with the actual plan generated with interleaved execution enabled:

交錯的計劃

  1. 請注意,MSTVF 資料表掃描現在反映精確的基數估計值。Notice that the MSTVF table scan now reflects an accurate cardinality estimate. 亦請注意此資料表掃描的重新排序和其他作業。Also notice the re-ordering of this table scan and the other operations.
  2. 而關於聯結演算法,我們已改從巢狀迴圈作業切換到雜湊比對作業,如果涉及大量的資料列,這樣更接近最佳狀態。And regarding join algorithms, we have switched from a Nested Loop operation to a Hash Match operation instead, which is more optimal given the large number of rows involved.
  3. 另請注意,我們不再顯示溢出警告,因為我們要根據 MSTVF 資料表掃描的實際資料列計數,授與更多記憶體。Also notice that we no longer have spill-warnings, as we're granting more memory based on the true row count flowing from the MSTVF table scan.

符合交錯執行的陳述式Interleaved execution eligible statements

在交錯執行中參考陳述式的 MSTVF,目前必須是唯讀的,且不為資料修改作業的一部分。MSTVF referencing statements in interleaved execution must currently be read-only and not part of a data modification operation. 此外,如果 MSTVF 未使用執行階段常數,則不適用於交錯執行。Also, MSTVFs are not eligible for interleaved execution if they do not use runtime constants.

交錯執行的優點Interleaved execution benefits

一般情況下,預估和實際資料列數目間的扭曲愈高,加上下游計劃作業的數目,對效能的影響就愈大。In general, the higher the skew between the estimated vs. actual number of rows, coupled with the number of downstream plan operations, the greater the performance impact. 一般而言,交錯執行有益於下列情況的查詢:In general, interleaved execution benefits queries where:

  1. 中繼結果集的預估和實際資料列數目間有很大的扭曲 (本例中為 MSTVF)。There is a large skew between the estimated vs. actual number of rows for the intermediate result set (in this case, the MSTVF).
  2. 而整體查詢對中繼結果的大小變更十分敏感。And the overall query is sensitive to a change in the size of the intermediate result. 這通常發生在查詢計劃有樹狀子目錄的複雜樹狀結構時。This typically happens when there is a complex tree above that subtree in the query plan. 僅僅 MSTVF 的 SELECT * 不會受益於交錯執行。A simple SELECT * from an MSTVF will not benefit from interleaved execution.

交錯執行的負擔Interleaved execution overhead

負擔應該最小,甚至完全沒有。The overhead should be minimal-to-none. 在導入交錯執行前,MSTVF 已被具體化,不過差異在於,現在我們要允許延遲最佳化,並利用具體化資料列集的基數估計值。MSTVFs were already being materialized prior to the introduction of interleaved execution, however the difference is that now we're now allowing deferred optimization and are then leveraging the cardinality estimate of the materialized row set. 就像任何會影響變更的計劃一樣,有些計劃的變更,會識我們以較佳的樹狀子目錄基數,得到整體查詢更差的計劃。As with any plan affecting changes, some plans could change such that with better cardinality for the subtree we get a worse plan for the query overall. 風險降低可以包括還原相容性層級,或使用查詢存放區強制執行非迴歸版的計劃。Mitigation can include reverting the compatibility level or using Query Store to force the non-regressed version of the plan.

交錯執行和連續執行Interleaved execution and consecutive executions

一旦快取交錯執行計劃,第一次執行即修改過估計值的計劃會用於連續執行,不必重新具現化交錯執行。Once an interleaved execution plan is cached, the plan with the revised estimates on the first execution is used for consecutive executions without re-instantiating interleaved execution.

追蹤交錯執行活動Tracking interleaved execution activity

您可以在實際的查詢執行計劃中看到使用方式屬性:You can see usage attributes in the actual query execution plan:

執行計劃屬性Execution Plan attribute DescriptionDescription
ContainsInterleavedExecutionCandidatesContainsInterleavedExecutionCandidates 適用於 QueryPlan 節點。Applies to the QueryPlan node. true 時,表示計劃包含交錯執行候選項目。When true, means the plan contains interleaved execution candidates.
IsInterleavedExecutedIsInterleavedExecuted 位於 TVF 節點 RelOp 之下 RuntimeInformation 元素的屬性。Attribute of the RuntimeInformation element under the RelOp for the TVF node. true 時,這表示作業已具體化為交錯執行作業的一部分。When true, means the operation was materialized as part of an interleaved execution operation.

您也可以透過下列 xEvent 追蹤交錯執行項目:You can also track interleaved execution occurrences via the following xEvents:

xEventxEvent DescriptionDescription
interleaved_exec_statusinterleaved_exec_status 交錯執行進行時會引發這個事件。This event fires when interleaved execution is occurring.
interleaved_exec_stats_updateinterleaved_exec_stats_update 此事件會描述由交錯執行更新的基數估計值。This event describes the cardinality estimates updated by interleaved execution.
Interleaved_exec_disabled_reasonInterleaved_exec_disabled_reason 當具有交錯執行可能候選項目的查詢不會實際取得交錯執行時,就會引發這個事件。This event fires when a query with a possible candidate for interleaved execution does not actually get interleaved execution.

必須執行查詢,才能讓交錯執行修改 MSTVF 基數估計值。A query must be executed in order to allow interleaved execution to revise MSTVF cardinality estimates. 不過,有透過 ContainsInterleavedExecutionCandidates 執行程序表屬性的交錯執行候選項目時,仍會顯示預估執行計劃。However, the estimated execution plan still shows when there are interleaved execution candidates via the ContainsInterleavedExecutionCandidates showplan attribute.

交錯執行快取Interleaved execution caching

如果從快取清除或收回計劃,就會在執行查詢時重新整理使用交錯執行的編譯。If a plan is cleared or evicted from cache, upon query execution there is a fresh compilation that uses interleaved execution. 使用 OPTION (RECOMPILE) 的陳述式會建立使用交錯執行的新計劃,且不會對它進行快取。A statement using OPTION (RECOMPILE) will create a new plan using interleaved execution and not cache it.

交錯執行和查詢存放區互通性Interleaved execution and query store interoperability

使用交錯執行的計劃可以強制執行。Plans using interleaved execution can be forced. 此計劃是根據初始執行更正基數估計值的版本。The plan is the version that has corrected cardinality estimates based on initial execution.

停用交錯執行而不變更相容性層級Disabling interleaved execution without changing the compatibility level

您可以在資料庫或陳述式的範圍停用交錯執行,同時仍將資料庫相容性層級維持在 140 以上。Interleaved execution can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. 若要針對源自資料庫的所有查詢執行停用交錯執行,請在適用資料庫的內容中執行下列程式碼:To disable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

-- 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 中會顯示為已啟用。When enabled, this setting will appear as enabled in sys.database_scoped_configurations. 若要針對源自資料庫的所有查詢執行重新啟用交錯執行,請在適用資料庫的內容中執行下列程式碼:To re-enable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

-- 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 查詢提示,以針對特定查詢停用交錯執行。You can also disable interleaved execution for a specific query by designating DISABLE_INTERLEAVED_EXECUTION_TVF as a USE HINT query hint. 例如:For example:

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 查詢提示的優先順序高於資料庫範圍設定或追蹤旗標設定。A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

資料表變數延後編譯Table variable deferred compilation

適用於: SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL Database (公開預覽)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL DatabaseAzure SQL Database (public preview)

資料表變數延後編譯可針對參考資料表變數的查詢,提升計畫品質和整體效能。Table variable deferred compilation improves plan quality and overall performance for queries that reference table variables. 在最佳化和初始編譯期間,此功能會根據實際資料表變數的資料列計數,傳播基數估計值。During optimization and initial compilation, this feature propagates cardinality estimates that are based on actual table variable row counts. 這項精確的資料列計數資訊會最佳化下游計畫作業。This accurate row count information optimizes downstream plan operations.

資料表變數延後編譯會延後編譯參考資料表變數的陳述式,直到第一次實際執行陳述式為止。Table variable deferred compilation defers compilation of a statement that references a table variable until the first actual run of the statement. 此延後編譯行為與暫存資料表相同。This deferred compilation behavior is the same as that of temporary tables. 這項變更會導致使用實際基數,不使用原始的單一資料列猜測。This change results in the use of actual cardinality instead of the original one-row guess.

您可在 Azure SQL Database 中公開預覽資料表變數延後編譯。You can enable the public preview of table variable deferred compilation in Azure SQL Database. 若要這樣做,請啟用執行查詢時所連線資料庫的相容性層級 150。To do that, enable compatibility level 150 for the database you're connected to when you run the query.

如需詳細資訊,請參閱資料表變數延遲編譯.For more information, see Table variable deferred compilation.

純量 UDF 內嵌Scalar UDF inlining

適用於: SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL Database (公開預覽)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL DatabaseAzure SQL Database (public preview)

純量 UDF 內嵌會自動將純量 UDF 轉換成關聯運算式。Scalar UDF inlining automatically transforms scalar UDFs into relational expressions. 將它們內嵌在呼叫的 SQL 查詢中。It embeds them in the calling SQL query. 此轉換可改善利用純量 UDF 的工作負載效能。This transformation improves the performance of workloads that take advantage of scalar UDFs. 純量 UDF 內嵌有益於 UDF 內的成本型最佳化作業。Scalar UDF inlining facilitates cost-based optimization of operations inside UDFs. 其結果會是有效率、集合導向的平行處理,而不是效率不彰、反覆執行的序列執行計畫。The results are efficient, set-oriented, and parallel instead of inefficient, iterative, serial execution plans. 根據預設,資料庫相容性層級 150 會啟用此功能。This feature is enabled by default under database compatibility level 150.

如需詳細資訊,請參閱純量 UDF 內嵌For more information, see Scalar UDF inlining.

近似查詢處理Approximate query processing

適用於: SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL Database (公開預覽)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL DatabaseAzure SQL Database (public preview)

近似查詢處理是新的功能系列。Approximate query processing is a new feature family. 它會在回應性比絕對精確度更重要的大型資料集間執行彙總作業。It aggregates across large datasets where responsiveness is more critical than absolute precision. 例如,在 10 億筆資料列中計算 COUNT(DISTINCT()) ,以顯示在儀表板上。An example is calculating a COUNT(DISTINCT()) across 10 billion rows, for display on a dashboard. 在此情況下,絕對精確度不重要,但回應性非常重要。In this case, absolute precision isn't important, but responsiveness is critical. 新的 APPROX_COUNT_DISTINCT 彙總函式會傳回群組中唯一非 Null 值的近似數目。The new APPROX_COUNT_DISTINCT aggregate function returns the approximate number of unique non-null values in a group.

如需詳細資訊,請參閱 APPROX_COUNT_DISTINCT (Transact-SQL)For more information, see APPROX_COUNT_DISTINCT (Transact-SQL).

資料列存放區上的批次模式Batch mode on rowstore

適用於: SQL ServerSQL Server (從 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始) 和 Azure SQL DatabaseAzure SQL Database (公開預覽)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL DatabaseAzure SQL Database (public preview)

資料列存放區上的批次模式可針對分析工作負載啟用批次模式執行功能,而不需要資料行存放區索引。Batch mode on rowstore enables batch mode execution for analytic workloads without requiring columnstore indexes. 這項功能支援用於磁碟上堆積和 B 型樹狀結構索引的批次模式執行和點陣圖篩選。This feature supports batch mode execution and bitmap filters for on-disk heaps and B-tree indexes. 資料列存放區上的批次模式可支援所有現有具備批次模式功能的運算子。Batch mode on rowstore enables support for all existing batch mode-enabled operators.

背景Background

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 引進了新功能,可加速分析工作負載:資料行存放區索引。introduced a new feature to accelerate analytical workloads: columnstore indexes. 我們已擴展使用案例,並改善每一個後續版本的資料行存放區索引效能。We expanded the use cases and improved the performance of columnstore indexes in each subsequent release. 到目前為止,我們已以單一功能的形式呈現及記載所有這些功能。Until now, we surfaced and documented all these capabilities as a single feature. 您在資料表上建立資料行存放區索引。You create columnstore indexes on your tables. 而且您的分析工作負載會更快。And your analytical workload goes faster. 但使用了兩種相關卻相異的技術:However, there are two related but distinct sets of technologies:

  • 使用資料行存放區索引,分析查詢只存取其所需資料行中的資料。With columnstore indexes, analytical queries access only the data in the columns they need. 使用資料行存放區格式的頁面壓縮,比傳統的資料列存放區索引壓縮更有效。Page compression in the columnstore format is also more effective than compression in traditional rowstore indexes.
  • 使用批次模式處理,查詢運算子處理資料更有效率。With batch mode processing, query operators process data more efficiently. 它們會批次處理資料列,而不是一次處理一筆資料列。They work on a batch of rows instead of one row at a time. 其他數項延展性改善也與批次模式處理繫結。A number of other scalability improvements are tied to batch mode processing. 如需批次模式的詳細資訊,請參閱執行模式For more information on batch mode, see Execution modes.

這兩組功能一同使用,改善輸入/輸出 (I/O) 和 CPU 使用率:The two sets of features work together to improve input/output (I/O) and CPU use:

  • 使用資料行存放區索引,可在記憶體中放置更多資料。By using columnstore indexes, more of your data fits in memory. 減少 I/O 的需要。That reduces the need for I/O.
  • 批次模式處理可更有效率的使用 CPU。Batch mode processing uses CPU more efficiently.

這兩項技術會盡可能地利用彼此的優勢。The two technologies take advantage of each other whenever possible. 例如,批次模式彙總可作為資料行存放區索引的一部分進行評估。For example, batch mode aggregates can be evaluated as part of a columnstore index scan. 我們也會更有效率的搭配批次模式聯結和批次模式彙總,使用執行長度限制編碼來處理壓縮過的資料行存放區資料。We also process columnstore data that's compressed by using run-length encoding much more efficiently with batch mode joins and batch mode aggregates.

這兩項功能可獨立使用:The two features are independently usable:

  • 您取得使用資料行存放區索引的資料列模式計畫。You get row mode plans that use columnstore indexes.
  • 您取得只使用資料列存放區索引的批次模式計畫。You get batch mode plans that use only rowstore indexes.

兩種功能一起使用,通常會取得最佳結果。You usually get the best results when you use the two features together. 因此到目前為止,SQL Server 查詢最佳化工具仍認為批次模式處理只適用於包含至少一個具有資料行存放區索引的資料表查詢。So until now, the SQL Server query optimizer considered batch mode processing only for queries that involve at least one table with a columnstore index.

資料行存放區索引不適合某些應用程式。Columnstore indexes aren't a good option for some applications. 應用程式可能使用資料行存放區索引不支援的一些其他功能。An application might use some other feature that isn't supported with columnstore indexes. 例如,就地修改與資料行存放區壓縮不相容。For example, in-place modifications aren't compatible with columnstore compression. 因此,具有叢集資料行存放區索引的資料表不支援觸發程序。So triggers aren't supported on tables with clustered columnstore indexes. 更重要的是,資料行存放區索引會增加 DELETEUPDATE 陳述式的額外負荷。More important, columnstore indexes add overhead for DELETE and UPDATE statements.

針對某些混合式交易分析工作負載,工作負載交易部分所帶來額外負荷仍超過資料行存放區索引的好處。For some hybrid transactional-analytical workloads, the overhead on a workload's transactional aspects outweighs the benefits of columnstore indexes. 這種狀況可以改善只使用批次模式處理的 CPU 使用量。Such scenarios can improve CPU use from batch mode processing alone. 這就是為什麼資料列存放區功能的批次模式會考慮用批次模式處理所有查詢。That's why the batch mode on rowstore feature considers batch mode for all queries. 涉及哪些索引並不重要。It doesn't matter which indexes are involved.

可從資料列存放區批次模式受益的工作負載Workloads that might benefit from batch mode on rowstore

下列工作負載可從資料列存放區的批次模式受益:The following workloads might benefit from batch mode on rowstore:

  • 工作負載有很大部分是由分析查詢構成。A significant part of the workload consists of analytical queries. 通常,這些查詢會有例如聯結或彙總的運算子,可處理數十萬筆或更多的資料列。Usually, these queries have operators like joins or aggregates that process hundreds of thousands of rows or more.
  • CPU 繫結的工作負載。The workload is CPU bound. 如果瓶頸為 I/O,我們仍建議您盡可能考慮資料行存放區索引。If the bottleneck is I/O, we still recommend that you consider a columnstore index, if possible.
  • 建立資料行存放區索引會將過多的額外負荷新增至您工作負載的交易式部分。Creating a columnstore index adds too much overhead to the transactional part of your workload. 或者,建立資料行存放區索引不可行,因為您應用程式的相依功能尚不支援資料行存放區索引。Or, creating a columnstore index isn't feasible because your application depends on a feature that's not yet supported with columnstore indexes.

注意

資料列存放區上的批次模式僅協助減少 CPU 使用量。Batch mode on rowstore helps only by reducing CPU consumption. 瓶頸若與 IO 相關,且資料尚未快取 (「冷」快取),則資料列存放區上的批次模式將無法改善已耗用時間。If your bottleneck is I/O related, and data isn't already cached ("cold" cache), batch mode on rowstore won't improve elapsed time. 同樣的,若電腦上沒有足夠記憶體可供快取所有資料,則效能也不太可能獲得改善。Similarly, if there isn't enough memory on the machine to cache all the data, a performance improvement is unlikely.

資料列存放區上的批次模式有哪些變更?What changes with batch mode on rowstore?

除了將相容性層級移動到 150 級之外,您無須變更任何項目,也能為候選工作負載啟用資料列存放區上的批次模式。Other than moving to compatibility level 150, you don't have to change anything on your side to enable batch mode on rowstore for candidate workloads.

即使查詢並未涉及任何具有資料行存放區索引的資料表,查詢處理器現在也會使用啟發學習法來決定是否要考慮使用批次模式。Even if a query doesn't involve any table with a columnstore index, the query processor now uses heuristics to decide whether to consider batch mode. 啟發學習法包含下列檢查:The heuristics consist of these checks:

  1. 對資料表大小、使用的運算子,以及輸入查詢中估計基數的初始檢查。An initial check of table sizes, operators used, and estimated cardinalities in the input query.
  2. 最佳化工具為查詢探索更便宜的新計劃時所帶來的額外檢查點。Additional checkpoints, as the optimizer discovers new, cheaper plans for the query. 若這些替代方案並未大量使用批次模式,則最佳化工具會停止探索批次模式的替代項目。If these alternative plans don't make significant use of batch mode, the optimizer stops exploring batch mode alternatives.

如果使用資料列存放區上的批次模式,您在查詢計畫中看到的實際執行模式如同批次模式If batch mode on rowstore is used, you see the actual run mode as batch mode in the query plan. 掃描運算子會使用批次模式處理磁碟上的堆積和 B 型樹狀結構索引。The scan operator uses batch mode for on-disk heaps and B-tree indexes. 此批次模式掃描可評估批次模式點陣圖篩選。This batch mode scan can evaluate batch mode bitmap filters. 您也可能會在計畫中看到其他批次模式運算子。You might also see other batch mode operators in the plan. 例如雜湊聯結、雜湊式彙總、排序、Window 彙總、篩選、串連和計算純量運算子。Examples are hash joins, hash-based aggregates, sorts, window aggregates, filters, concatenation, and compute scalar operators.

RemarksRemarks

查詢計畫並非一律使用批次模式。Query plans don't always use batch mode. 查詢最佳化工具可能會判定批次模式對查詢沒有幫助。The Query Optimizer might decide that batch mode isn't beneficial for the query.

查詢最佳化工具的搜尋空間正在變更。The Query Optimizer's search space is changing. 因此,如果收到資料列模式計畫,它可能和您在較低相容性層級中取得的計畫不一樣。So if you get a row mode plan, it might not be the same as the plan you get in a lower compatibility level. 而如果您收到批次模式計畫,它可能和您以資料行存放區索引取得的計畫不一樣。And if you get a batch mode plan, it might not be the same as the plan you get with a columnstore index.

針對混合資料行存放區和資料列存放區索引的查詢,計畫可能也會因為新的批次模式資料列存放區掃描而變更。Plans might also change for queries that mix columnstore and rowstore indexes because of the new batch mode rowstore scan.

資料列存放區掃描上新批次模式的目前限制:There are current limitations for the new batch mode on rowstore scan:

  • 對於記憶體內部 OLTP 資料表,或是磁碟上堆積與 B 型樹狀結構以外的任何索引,它無法生效。It won't kick in for in-memory OLTP tables or for any index other than on-disk heaps and B-trees.
  • 它也無法在擷取或篩選大型物件 (LOB) 資料行時生效。It also won't kick in if a large object (LOB) column is fetched or filtered. 這項限制包含疏鬆資料行集和 XML 資料行。This limitation includes sparse column sets and XML columns.

甚至有具備資料行存放區索引但不使用批次模式的查詢。There are queries that batch mode isn't used for even with columnstore indexes. 例如包含資料指標的查詢。Examples are queries that involve cursors. 這些相同排除項目也會擴及資料列存放區上的批次模式。These same exclusions also extend to batch mode on rowstore.

設定資料列存放區上的批次模式Configure batch mode on rowstore

預設開啟 BATCH_MODE_ON_ROWSTORE 資料庫範圍設定。The BATCH_MODE_ON_ROWSTORE database scoped configuration is on by default. 它會停用資料列存放區上的批次模式,但不要求變更資料庫相容性層級:It disables batch mode on rowstore without requiring a change in database compatibility level:

-- 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;

您可以透過資料庫範圍設定停用資料列存放區上的批次模式。You can disable batch mode on rowstore via database scoped configuration. 但使用 ALLOW_BATCH_MODE 查詢提示仍可覆寫查詢層級的設定。But you can still override the setting at the query level by using the ALLOW_BATCH_MODE query hint. 下列範例會啟用資料列存放區上的批次模式,即使已透過資料庫範圍設定停用該項功能:The following example enables batch mode on rowstore even with the feature disabled via database scoped configuration:

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 查詢提示,針對特定查詢停用資料列存放區上的批次模式。You can also disable batch mode on rowstore for a specific query by using the DISALLOW_BATCH_MODE query hint. 請參閱下列範例:See the following example:

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'));

另請參閱See also

SQL Server 資料庫引擎和 Azure SQL Database 的效能中心 Performance Center for SQL Server Database Engine and Azure SQL Database
查詢處理架構指南 Query processing architecture guide
邏輯和實體運算子參考執行程序表 Showplan logical and physical operators reference
聯結 Joins
示範自適性查詢處理 Demonstrating Adaptive Query Processing
示範智慧查詢處理 (英文)Demonstrating Intelligent Query Processing