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

``````ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;
``````

IQP 功能IQP Feature Azure SQL Database 支援Supported in Azure SQL Database SQL Server 支援Supported in SQL Server 說明Description

• 如果組建聯結輸入的資料列計數小到巢狀迴圈聯結會比雜湊聯結更佳的情況，則您的計劃就會切換成巢狀迴圈演算法。If the row count of the build join input is small enough that a nested loop join would be more optimal than a Hash Join, your plan switches to a Nested Loops algorithm.
• 如果組建聯結輸入超過特定的資料列計數閾值，則不會切換，且您的計劃會繼續執行雜湊聯結。If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash Join.

``````SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 360;
``````

1. 我們使用了資料行存放區索引掃描，為雜湊聯結建置階段提供資料列。We have a columnstore index scan used to provide rows for the hash join build phase.
2. 我們有新的自適性聯結運算子。We have the new Adaptive Join operator. 此運算子定義的閾值是用於決定何時要切換至巢狀迴圈計劃。This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. 本例中的閾值是 78 個資料列。For our example, the threshold is 78 rows. 凡是 >= 78 個資料列的計劃都會使用雜湊聯結。Anything with >= 78 rows will use a Hash Join. 如果小於該閾值，則會使用巢狀迴圈聯結。If less than the threshold, a Nested Loops Join will be used.
3. 因為我們傳回 336 個資料列 (超過閾值)，所以第二個分支會表示標準雜湊聯結作業的探查階段。Since we return 336 rows, we are exceeding the threshold and so the second branch represents the probe phase of a standard Hash Join operation. 請注意，即時查詢統計資料會顯示流經運算子的資料列，本例中為 "672 of 672"。Notice that Live Query Statistics shows rows flowing through the operators - in this case "672 of 672".
4. 而最後一個分支是我們的叢集索引搜尋，供未超過閾值的巢狀迴圈聯結所使用。And the last branch is our Clustered Index Seek for use by the nested loop join had the threshold not been exceeded. 請注意，我們看到的顯示是"0 of 336" 資料列 (分支未使用)。Notice that we see "0 of 336" rows displayed (the branch is unused). 現在對比使用相同查詢的計劃，但這次的 Quantity 值在資料表中只有一個資料列：Now contrast the plan with the same query, but this time for a Quantity value that only has one row in the table:
``````SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 361;
``````

• 因為傳回一個資料列，現在叢集索引搜尋中有資料列通過。With one row returned, the Clustered Index Seek now has rows flowing through it.
• 而且，因為雜湊聯結建置階段並未繼續，所以不會有任何資料列通過第二個分支。And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.

AdaptiveThresholdRowsAdaptiveThresholdRows 顯示從雜湊聯結切換至巢狀迴圈聯結所使用的閾值。Shows the threshold use to switch from a hash join to nested loop join.
EstimatedJoinTypeEstimatedJoinType 可能的聯結類型。What the join type is likely to be.
ActualJoinTypeActualJoinType 在實際的計劃中，顯示根據閾值最後選擇的聯結演算法。In an actual plan, shows what join algorithm was ultimately chosen based on the threshold.

自適性整聯結和查詢存放區互通性Adaptive join and Query Store interoperability

• 資料庫相容性層級是 140。The database compatibility level is 140.
• 查詢是 SELECT 陳述式 (資料修改陳述式目前不適合)。The query is a SELECT statement (data modification statements are currently ineligible).
• 聯結能夠由索引巢狀迴圈聯結或雜湊聯結實體演算法執行。The join is eligible to be executed both by an indexed Nested Loops Join or a Hash Join physical algorithm.
• 雜湊聯結使用批次模式，不論是透過存在於整體查詢中的資料行存放區索引，或是由聯結直接參考的資料行存放區索引資料表。The Hash Join uses batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
• 產生的巢狀迴圈聯結和雜湊聯結替代解決方案應該有相同的第一個子系 (外部參考)。The generated alternative solutions of the Nested Loops Join and Hash Join should have the same first child (outer reference).

停用自適性聯結而不變更相容性層級Disabling adaptive joins without changing the compatibility level

``````ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;
``````

``````ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;
``````

``````SELECT s.CustomerID,
s.CustomerName,
sc.CustomerCategoryName
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID
``````

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

批次模式記憶體授與意見反應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;
``````

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

``````ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
``````

``````ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
``````

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

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:

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

``````ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
``````

``````ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
``````

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

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).

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.

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 benefits

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.

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

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.

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.

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

``````ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;
``````

``````ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;
``````

``````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.

近似查詢處理Approximate query processing

APPROX_COUNT_DISTINCT 是公開預覽功能。APPROX_COUNT_DISTINCT is a public preview feature.

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

背景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.

• 使用資料行存放區索引，可在記憶體中放置更多資料。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.

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

可從資料列存放區批次模式受益的工作負載Workloads that 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.

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

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.

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

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

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

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