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

適用対象: ○SQL Server ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

インテリジェントなクエリ処理機能ファミリには、実装の労力は最小限で既存のワークロードのパフォーマンスを改善する、広範な影響がある機能が含まれています。The Intelligent query processing feature family includes features with broad impact that improve the performance of existing workloads with minimal implementation effort.

インテリジェントなクエリ処理の機能

アダプティブ クエリ処理Adaptive Query Processing

アダプティブ クエリ処理の機能ファミリには、最適化戦略をアプリケーション ワークロードの実行時条件に適用するクエリ処理の改善が含まれます。The adaptive query processing feature family includes query processing improvements that adapt optimization strategies to your application workload's runtime conditions. これらの改善には、以下が含まれます。These improvements included:

  • バッチ モード アダプティブ結合Batch mode adaptive joins
  • メモリ許可フィードバックMemory grant feedback
  • 複数ステートメントのテーブル値関数 (MSTVF) のインターリーブ実行Interleaved execution for multi-statement table-valued functions (MSTVFs)

バッチ モード アダプティブ結合Batch mode adaptive joins

この機能により、キャッシュされている単独プランの実行中に、プランをより優れた結合戦略に動的に切り替えることができます。This feature allows your plan to dynamically switch to a better join strategy during execution using a single cached plan.

バッチ モード アダプティブ結合の詳細については、「Microsoft SQL データベースでのアダプティブ クエリの処理」をご覧ください。For more information on batch mode adaptive joins, see Adaptive query processing in SQL databases.

行モードとバッチ モードのメモリ許可フィードバックRow and batch mode memory grant feedback

注意

行モード メモリ許可フィードバックはパブリック プレビューの機能です。Row mode memory grant feedback is a public preview feature.

この機能はクエリに必要な実際のメモリを再計算して、キャッシュされているプランで許可されている値を更新するため、コンカレンシーに影響する過大なメモリ許可が少なくなり、過剰なディスク書き込みの原因となる過小評価されたメモリ許可が修正されます。This feature recalculates the actual memory required for a query and then updates the grant value for the cached plan, reducing excessive memory grants that impact concurrency and fixing underestimated memory grants that cause expensive spills to disk.

メモリ許可フィードバックの詳細については、「Microsoft SQL データベースでのアダプティブ クエリの処理」をご覧ください。For more information on memory grant feedback, see Adaptive query processing in SQL databases.

複数ステートメントのテーブル値関数 (MSTVF) のインターリーブ実行Interleaved execution for multi-statement table-valued functions (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.

インターリーブ実行の詳細については、「Microsoft SQL データベースでのアダプティブ クエリの処理」をご覧ください。For more information on interleaved execution, see Adaptive query processing in SQL databases.

テーブル変数の遅延コンパイルTable variable deferred compilation

注意

テーブル変数の遅延コンパイルは、パブリック プレビュー機能です。Table variable deferred compilation is a public preview feature.

テーブル変数の遅延コンパイルを使用すると、テーブル変数を参照するクエリのプランの品質および全体的なパフォーマンスが向上します。Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. 最適化と最初のコンパイルの実行中に、この機能は実際テーブル変数の行数に基づくカーディナリティの推定を反映します。During optimization and initial compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. この正確な行数の情報は、ダウンストリーム プラン操作を最適化するために使用されます。This accurate row count information will be used for optimizing downstream plan operations.

テーブル変数の遅延コンパイルを使用すると、テーブル変数を参照するステートメントのコンパイルは、そのステートメントが最初に実際に実行されるまで遅延されます。With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. この遅延コンパイルの動作は、一時テーブルの動作と同じです。この変更によって、元の 1 行の推定値ではなく、実際のカーディナリティを使用できるようになります。This deferred compilation behavior is identical to the behavior of temporary tables, and this change results in the use of actual cardinality instead of the original one-row guess. Azure SQL Database でテーブル変数の遅延コンパイルのパブリック プレビューを有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 150 を有効にします。To enable the public preview of table variable deferred compilation in Azure SQL Database, enable database compatibility level 150 for the database you are connected to when executing the query.

詳細については、「テーブル変数の遅延コンパイル」をご覧ください。For more information, see Table variable deferred compilation.

スカラー UDF のインライン化Scalar UDF inlining

注意

スカラー UDF のインライン化は、パブリック プレビュー機能です。Scalar UDF inlining is a public preview feature.

スカラー UDF のインライン化では、スカラー ユーザー定義関数 (UDF) が関係式に変換され、それらが呼び出し元の SQL クエリに埋め込まれます。これにより、スカラー UDF を利用するワークロードのパフォーマンスが向上します。Scalar UDF inlining automatically transforms scalar user-defined functions (UDF) into relational expressions and embeds them in the calling SQL query, thereby improving the performance of workloads that leverage scalar UDFs. スカラー UDF のインライン化によって、UDF 内の操作に対するコストに基づく最適化が促進され、その結果として、非効率な、反復的および直列的な実行プランではなく、セット指向で並列的である効率的なプランが提供されます。Scalar UDF inlining facilitates cost-based optimization of operations inside UDFs, and results in efficient plans that are set-oriented and parallel as opposed to 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

注意

APPROX_COUNT_DISTINCT はパブリック プレビューの機能です。APPROX_COUNT_DISTINCT is a public preview feature.

概数クエリ処理とは、絶対的な精度よりも応答性が重要となる場合に、大規模なデータ セット全体の集計を提供するために設計された、新しい機能ファミリです。Approximate query processing is a new family of features that are designed to provide aggregations across large data sets where responsiveness is more critical than absolute precision. たとえば、ダッシュボードに表示するために、100 億の行に対する COUNT(DISTINCT()) を計算する場合などです。An example might be calculating a COUNT(DISTINCT()) across 10 billion rows, for display on a dashboard. この場合、重要なのは絶対的な精度ではなく、応答性です。In this case, absolute precision is not 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

注意

行ストアでのバッチ モードは、パブリック プレビュー機能です。Batch Mode on Rowstore is a public preview feature.

背景情報Background

SQL Server 2012 (11.x)SQL Server 2012 (11.x) では、分析ワークロードを促進する新機能である列ストア インデックスが導入されました。introduced a new feature for accelerating analytical workloads: columnstore indexes. 後続の各リリースでユース ケースを拡張し、列ストア インデックスのパフォーマンスを向上させました。We have expanded the use cases and improved the performance of columnstore indexes in each subsequent release. これまでは、これらすべての機能を 1 つの機能として浮上させ、文書化しました。テーブルに対して列ストア インデックスを作成すると、分析ワークロードが "高速化" します。Until now, we have surfaced and documented all these capabilities as a single feature: You create columnstore indexes on your tables, and your analytical workload "just goes faster". ただし実際には、関連しているが異なる 2 つのテクノロジ セットがあります。Under the covers, however, there are two related but distinct sets of technologies:

  • 列ストア インデックスでは、分析クエリは必要な列のデータにのみアクセスできます。Columnstore indexes allow analytical queries to access only the data in the columns they need. 列ストア形式では、従来の "行ストア" インデックスのページ圧縮よりもはるかに効果的な圧縮も可能です。The columnstore format also allows much more effective compression than what you get with page compression in traditional "rowstore" indexes.
  • バッチ モード処理では、一度に 1 行ではなく行のバッチを処理することで、クエリ演算子によるデータ処理が効率的になります。Batch mode processing allows query operators to process data more efficiently by working on a batch of rows at a time, 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 I/O and CPU utilization:

  • 列ストア インデックスを使用すると、より多くのデータがメモリに収まるので、I/O の必要性が低減されます。Columnstore indexes allow more of your data to fit in memory, and thus reduce 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 compressed using run-length encoding much more efficiently with batch mode joins and batch mode aggregates.

2 つの機能は既に独立して使用できます。列ストア インデックスを使用する行モード プランを取得でき、行ストア インデックスのみを使用するバッチ モード プランを取得できます。The two features are already usable independently: you can get row mode plans that use columnstore indexes, and you can get batch mode plans that use only rowstore indexes. しかし、ほとんどの場合に、2 つの機能が一緒に使用されると最高の結果が得られるので、SQL のクエリ オプティマイザーはこれまで、列ストア インデックスのあるテーブルが少なくとも 1 つ関係するクエリに対してのみバッチ モード処理を検討しました。But since in most cases you get the best results when the two features are used together, SQL's query optimizer has until now considered batch mode processing only for queries that involve at least one table with a columnstore index.

一部のアプリケーションでは、列ストア インデックスは実行可能なオプションではありません。列ストア インデックスでサポートされていない他のいくつかの機能がアプリケーションで使用されるからです (たとえばトリガーは、クラスター化列ストア インデックスのあるテーブルではサポートされていません)。For some applications, columnstore indexes are not a viable option because the application uses some other feature that is not supported with columnstore indexes (triggers are not supported on tables with clustered columnstore indexes, for example). さらに重要なのは、インプレース変更は列ストア圧縮と互換性がないので、列ストア インデックスによって DELETE および UPDATE ステートメントにオーバーヘッドが追加されることです。More importantly, columnstore indexes adds overhead for DELETE and UPDATE statements, because in-place modifications are not compatible with columnstore compression. 一部のハイブリッド トランザクション分析ワークロードでは、列ストア インデックスによって分析クエリにもたらされるメリットをワークロードのトランザクション面でのオーバーヘッドが上回ります。For some hybrid transactional-analytical workloads, the benefit that columnstore indexes would bring for analytical queries is outweighed by the overhead on a workload's transactional aspects. このようなシナリオでは、バッチ モード処理だけでも CPU 使用率を向上させることができます。そのため、行ストアでのバッチ モード機能では、関連するインデックスに関係なく、すべてのクエリに対してバッチ モードが考慮されます。Such scenarios can get improved CPU utilization from batch mode processing alone, which is why the batch mode on rowstore feature will consider batch mode for all queries, regardless of the indexes involved.

行ストアでのバッチ モードの恩恵を受ける可能性があるワークロードWhat workloads may benefit from batch mode on rowstore

次のワークロードは、行ストアでのバッチ モードの恩恵を受ける可能性があります。The following workloads may benefit from batch mode on rowstore:

  1. ワークロードの大部分が分析クエリで構成される (目安として、数十万行以上の結合または集計処理などの演算子を使用したクエリ)。かつA significant part of the workload consists of analytical queries (as a rule of thumb, queries with operators such as joins or aggregates processing hundreds of thousands of rows or more), AND
  2. ワークロードが CPU バウンドである (ボトルネックが IO の場合は、可能であれば列ストア インデックスを検討することを引き続きお勧めします)。かつThe workload is CPU bound (if the bottleneck is IO, it is still recommended to consider a columnstore index, if possible), AND
  3. 列ストア インデックスを作成すると、ワークロードのトランザクション部分に対するオーバーヘッドが増えすぎる、または列ストア インデックスでまだサポートされていない機能にアプリケーションが依存しているため列ストアインデックスの作成が現実的でない。Creating a columnstore index adds too much overhead to the transactional part of your workload OR creating a columnstore index is not feasible because your application depends on a feature that is not yet supported with columnstore indexes.

注意

行ストアでのバッチ モードは、CPU 使用量を減らすことでのみ支援できます。Batch mode on rowstore can only help by reducing CPU consumption. ボトルネックが IO に関連し、データがまだキャッシュされていない場合 ("コールド" キャッシュ)、行ストアでのバッチ モードでは経過時間が向上しません。If your bottleneck is IO-related, and data is not already cached ("cold" cache), batch mode on rowstore will NOT improve elapsed time. 同様に、すべてのデータをキャッシュするための十分なメモリがマシンにない場合、パフォーマンスは向上しない可能性があります。Similarly, if there is not enough memory on the machine to cache all 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 in order to enable batch mode on rowstore for candidate workloads.

クエリが列ストア インデックスのあるテーブルに関係しない場合でも、クエリ プロセッサではヒューリスティックを使用して、バッチ モードを検討するかどうかが決定されます。Even if a query does not involve any table with a columnstore index, the query processor now uses heuristics to decide whether to consider batch mode. ヒューリスティックは以下から構成されます。The heuristics consist of:

  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 do not make significant use of batch mode, the optimizer will stop exploring batch mode alternatives.

行ストアでのバッチ モードを使用する場合、クエリ実行プランでは、実際の実行モードが、ディスク上のヒープと B ツリー インデックスに対してスキャン演算子によって使用される "バッチ モード" として表示されます。If batch mode on rowstore is used, in the query execution plan you will see the actual execution mode as "batch mode" used by the scan operator for on-disk heaps and B-tree indexes. このバッチ モード スキャンでは、バッチ モードのビットマップ フィルターを評価できます。This batch mode scan can evaluate batch mode bitmap filters. プランでは、ハッシュ結合、ハッシュ ベースの集計、並べ替え、ウィンドウ集計、フィルター、連結、Compute Scalar 演算子などの他のバッチ モード演算子も表示されることがあります。You may also see other batch mode operators in the plan, such as hash joins, hash-based aggregates, sorts, window aggregates, filters, concatenation, and compute scalar operators.

RemarksRemarks

  1. クエリ プランによってバッチ モードが使用されるという保証はありません。There is no guarantee that query plans will use batch mode. クエリ オプティマイザーでは、クエリがバッチ モードの恩恵を受けないと判断される場合があります。The query optimizer may decide that batch mode does not look beneficial for the query.
  2. クエリ オプティマイザーの検索スペースは変化するので、行モード プランを取得した場合に、それがより低い互換性レベルで取得するプランと同じであるという保証はありません。As the query optimizer's search space is changing, there is no guarantee that if you get a row mode plan, it will be the same as the plan you get in a lower compatibility level. また、バッチ モード プランを取得する場合に、それが列ストア インデックスを使用して取得したプランと同じであるという保証はありません。There is also no guarantee that if you get a batch mode plan, it will be the same as the plan you'd get with a columnstore index.
  3. 新しいバッチ モード行ストア スキャンが原因で、プランは、列ストア インデックスと行ストア インデックスが混在するクエリに対してわずかに変化する可能性もあります。Plans may also change in subtle ways for queries that mix columnstore and rowstore indexes, because of the new batch mode rowstore scan.
  4. 新しい行ストア スキャンでのバッチ モードに対する現在の制限事項: インメモリ OLTP テーブル、またはディスク上のヒープと B ツリー以外のインデックスに対しては開始されません。Current limitations for the new batch mode on rowstore scan: It will not kick in for in-memory OLTP tables, or for any index other than on-disk heaps and B-trees. LOB 列がフェッチまたはフィルター処理された場合にも開始しません。It will also not kick in if a LOB column is fetched or filtered. この制限には、スパース列セットと XML 列が含まれます。This limitation includes sparse column sets, and XML columns.
  5. バッチ モードが列ストア インデックスと共に使用されないクエリ (たとえばカーソルに関連するクエリ) があり、この同じ除外が行ストアでのバッチ モードにも拡張されます。There are queries for which batch mode is not used even with columnstore indexes (for example queries involving cursors), and these same exclusions extend to batch mode on rowstore as well.

行ストアでのバッチ モードの構成Configuring batch mode on rowstore

BATCH_MODE_ON_ROWSTORE データベース スコープ構成は、既定でオンになり、データベース互換性レベルを変更する必要なく、行ストアでのバッチ モードを無効にするために使用できます。The BATCH_MODE_ON_ROWSTORE database scoped configuration is on by default and can be used to disable 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;

データベース スコープ構成を使用して行ストアでのバッチ モードを無効にできますが、その場合も ALLOW_BATCH_MODE クエリ ヒントを使用してクエリ レベルで設定をオーバーライドできます。You can disable batch mode on rowstore via database scoped configuration but still override the setting at the query level 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. 例 :For 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
インテリジェントな QP のデモDemonstrating Intelligent QP