SQL データベースでのインテリジェントなクエリ処理Intelligent query processing in SQL databases

適用対象: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

インテリジェントなクエリ処理 (QP) 機能ファミリには、最小限の労力で実装できる、既存のワークロードのパフォーマンスを広範に改善する機能が含まれています。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) あり (互換性レベル 140 未満)Yes, under compatibility level 140 あり (SQL Server 2017 (14.x)SQL Server 2017 (14.x) 以降、互換性レベル 140 未満)Yes, 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 あり (SQL Server 2019 (15.x)SQL Server 2019 (15.x) 以降)Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) 高パフォーマンスと小さいメモリ占有領域の利点がある、ビッグ データシナリオに対して、おおよその 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 あり (SQL Server 2019 (15.x)SQL Server 2019 (15.x) 以降、互換性レベル 150 未満)Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) under compatibility level 150 列ストア インデックスを必要としない、CPU にバインドされたリレーショナル DW ワークロードに対してバッチ モードを指定します。Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
インターリーブ実行Interleaved Execution あり (互換性レベル 140 未満)Yes, under compatibility level 140 あり (SQL Server 2017 (14.x)SQL Server 2017 (14.x) 以降、互換性レベル 140 未満)Yes, 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) あり (互換性レベル 140 未満)Yes, under compatibility level 140 あり (SQL Server 2017 (14.x)SQL Server 2017 (14.x) 以降、互換性レベル 140 未満)Yes, 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 あり (SQL Server 2019 (15.x)SQL Server 2019 (15.x) 以降、互換性レベル 150 未満)Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) under compatibility level 150 行モード クエリにディスクへの書き込み操作がある場合は、連続実行のためにさらにメモリを追加します。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) 以降、互換性レベル 150 未満)Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) under compatibility level 150 スカラー 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 あり (SQL Server 2019 (15.x)SQL Server 2019 (15.x) 以降、互換性レベル 150 未満)Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) under compatibility level 150 固定推定値ではなく、最初のコンパイルで発生したテーブル変数の実際のカーディナリティを使用します。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. 次のグラフでは、バッチ モード アダプティブ メモリ許可フィードバックを使用する 1 つの例を示します。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;

ディスクへの書き込みが多い

メモリ許可フィードバックを有効にした 2 番目の実行では、所要時間は "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

メモリ許可条件が過剰な場合、許可されるメモリが実際に使われるメモリ サイズの 2 倍より多いと、メモリ許可フィードバックはメモリ許可を再計算して、キャッシュされるプランを更新します。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. このイベントは、プランのノード ID と、そのノードの書き込まれたデータ サイズを返します。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. パラメーター スニッフィングとパラメーターの感度の詳細については、「Query Processing Architecture Guide」(クエリ処理アーキテクチャ ガイド) を参照してください。For more information about parameter sniffing and parameter sensitivity, refer to the Query Processing Architecture Guide.

メモリ許可フィードバックのキャッシュMemory grant feedback caching

フィードバックは、1 回の実行に対してキャッシュされるプランに格納できます。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_FEEDBACKUSE 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 データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL データベースAzure SQL Database

行モード メモリ許可フィードバックは、バッチ モードと行モード両方の演算子のメモリ許可サイズを調整することで、バッチ モード メモリ許可フィードバックの機能を拡張します。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 データベースAzure SQL Database で行モード メモリ許可フィードバックを有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 150 を有効にします。To enable row mode memory grant feedback in Azure SQL データベースAzure 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.

行モード メモリ許可フィードバック以降では、実際の実行プランのために 2 つの新しいクエリ プラン属性 (IsMemoryGrantFeedbackAdjusted および LastRequestedMemory) が表示されます。これらは 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 [説明]Description
No:First ExecutionNo: First Execution メモリ許可フィードバックは、最初のコンパイルとそれに関連付けられた実行ではメモリを調整しません。Memory grant feedback does not adjust memory for the first compile and associated execution.
No:Accurate GrantNo: 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 disabledNo: 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:AdjustingYes: Adjusting メモリ許可フィードバックが適用されています。また、次の実行に向けてさらに調整される可能性があります。Memory grant feedback has been applied and may be further adjusted for the next execution.
Yes:StableYes: 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.

互換性レベルを変更せず、行モード メモリ許可フィードバックを無効にする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_FEEDBACKUSE 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. プランの 3 つの注目すべき領域があります (フローは右から左)。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. ただし、この例では、この MSTVF テーブル スキャンを通過したのはライブ クエリ統計が示すように 527,597 であるのに対し、実際の推定は 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. Nested Loops 操作では、結合の外側によって返されるものと推定されたのは 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. Hash Match 操作では、小さい警告シンボルに注意してください。これはここではディスクへの書き込みを示します。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. 結合アルゴリズムに関しては、Nested Loop 操作から Hash Match 操作に切り替えました。これは、多くの行が関係する場合に、より適しています。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 [説明]Description
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 [説明]Description
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 showplan 属性によるインターリーブ実行候補がある場合は、推定実行プランがまだ表示されます。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;

USE HINT クエリ ヒントとして DISABLE_INTERLEAVED_EXECUTION_TVF を指定することで、特定のクエリでインターリーブ実行を無効にすることもできます。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 データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL データベースAzure SQL Database

テーブル変数の遅延コンパイルを使用すると、テーブル変数を参照するクエリのプランの品質および全体的なパフォーマンスが向上します。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. この変更によって、元の 1 行の推定値ではなく、実際のカーディナリティを使用できるようになります。This change results in the use of actual cardinality instead of the original one-row guess.

テーブル変数の遅延コンパイルは、Azure SQL Database で有効にすることができます。You can enable 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) 以降)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))

スカラー 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 データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL データベースAzure SQL Database

概数クエリ処理は新しい機能ファミリです。Approximate query processing is a new feature family. 絶対的な精度よりも応答性が重要となる場合に、大規模なデータ セット全体が集計されます。It aggregates across large datasets where responsiveness is more critical than absolute precision. たとえば、ダッシュボードに表示するために、100 億の行に対する 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 データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Azure SQL データベースAzure SQL Database

行ストアのバッチ モードでは、列ストア インデックスを要求せず、分析ワークロードをバッチ モードで実行できます。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. これまでは、これらすべての機能を 1 つの機能として浮上させ、文書化しました。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. ただし、関連しているが異なる 2 つのテクノロジ セットがあります。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. 一度に 1 行ずつではなく、複数行がバッチ処理されます。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.

2 セットの機能が連携して、入力/出力 (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.

2 つのテクノロジは可能な限り相互活用されます。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. また、バッチ モード結合とバッチ モード集計により、Run-length エンコードを使用して圧縮された列ストア データをはるかに効率的に処理できます。We also process columnstore data that's compressed by using run-length encoding much more efficiently with batch mode joins and batch mode aggregates.

2 つの機能は独立し使用できます。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.

通常、2 つの機能を一緒に使用すると、最適な結果が得られます。You usually get the best results when you use the two features together. そのため、これまで SQL Server のクエリ オプティマイザーは、列ストア インデックスのあるテーブルが少なくとも 1 つ関係するクエリに対してのみバッチ モード処理を検討しました。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. さらに重要なことに、列ストア インデックスによって DELETE および UPDATE ステートメントにオーバーヘッドが増えます。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. ボトルネックが IO の場合は、可能であれば列ストア インデックスを検討することを引き続きお勧めします。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. scan 演算子では、ディスク上のヒープと 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. たとえば、ハッシュ結合、ハッシュ ベースの集計、並べ替え、ウィンドウ集計、フィルター、連結、Compute Scalar 演算子などです。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