クエリ処理フィードバック機能

この記事では、さまざまなインテリジェント クエリ処理 (IQP) フィードバック機能について詳しく説明します。 クエリ処理フィードバック機能は、インテリジェント クエリ処理機能ファミリの一部です。 クエリ処理フィードバックは、SQL Server、Azure SQL Database、および Azure SQL Managed Instance のクエリ プロセッサがクエリの実行に関する履歴データを使用して、クエリのコンパイルおよび実行方法に対する 1 つ以上の変更からヘルプを受け取る可能性があるかどうかを判断するプロセスです。 パフォーマンス データは クエリ ストアに収集され、クエリの実行を改善するためのさまざまな提案が表示されます。 成功した場合は、今後使用するために、これらの変更をメモリやクエリ ストアのディスクに保持します。 提案によって十分な改善が得られない場合は破棄され、クエリはそのフィードバックなしで引き続き実行されます。

この記事で説明するフィードバック機能は次のとおりです。

メモリ許可フィードバック

クエリが、大きすぎるか小さすぎるメモリ許可で実行される場合があります。 メモリ許可が大きすぎる場合は、サーバーでの並列処理が禁止されます。 サイズが小さすぎると、ディスクに流出する可能性があります。これはコストのかかる操作です。 メモリ許可フィードバックは、クエリの以前の実行 (SQL Server 2022 (16.x 以降) のメモリニーズを記憶し、それに応じてクエリに与えられた許可を調整しようとします。 この機能は、3 つのウェーブでリリースされています。 バッチ モードメモリ許可フィードバック、行モードメモリ許可フィードバック、および SQL Server 2022 (16.x) では、クエリ ストアとパーセンタイル許可と呼ばれる改良されたアルゴリズムを使用して、ディスク上の永続化に関するメモリ許可フィードバックを導入しています。

バッチ モード メモリ許可フィードバック

適用対象: SQL Server (SQL Server 2017 (14.x) 以降)、Azure SQL データベース

クエリの実行プランには、実行に必要な最小メモリと、すべての行をメモリに収めるのに最適なメモリ許可サイズが含まれています。 メモリ許可サイズが正しくない場合、パフォーマンスが低下します。 メモリ許可が多すぎると、メモリが無駄になり、コンカレンシーが制限されます。 メモリ許可が少なすぎると、負荷の高いディスクへの書き込みが発生する原因になります。 繰り返されるワークロードを処理することにより、バッチ モード メモリ許可フィードバックはクエリに実際に必要なメモリ量を再計算し、キャッシュされたプランの許可値を更新します。 同じクエリ ステートメントを実行するとき、クエリは、修正されたメモリ許可サイズを使うことで、コンカレンシーに影響を与える過剰なメモリ許可を減らし、負荷の高いディスクへの書き込みが発生する過少なメモリ許可を修正します。

次のグラフでは、バッチ モード アダプティブ メモリ許可フィードバックを使用する 1 つの例を示します。 最初のクエリ実行の場合、ディスクへの書き込みが多いため所要時間は "88 秒" でした。

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;

メモリの許可された MB とスピルされた MB のグラフ。高いスピルを示します。

メモリ許可フィードバックを有効にした 2 番目の実行では、所要時間は "1 秒" で (88 秒から短縮)、ディスクへの書き込みはまったくなくなり、許可は高くなっています。

メモリの許可された MB とスピルされた MB のグラフ。スピルがないことを示します。

メモリ許可フィードバックのサイズ決定

メモリ許可条件が過剰な場合、許可されるメモリが実際に使われるメモリ サイズの 2 倍より多いと、メモリ許可フィードバックはメモリ許可を再計算して、キャッシュされるプランを更新します。 メモリ許可が 1 MB を超えるプランは、超過分については再計算されません。

バッチ モード演算子のディスクへのスピルが発生するメモリ許可条件のサイズが不十分な場合、メモリ許可フィードバックによってメモリ許可の再計算がトリガーされます。 スピル イベントはメモリ許可フィードバックに報告され、拡張イベントを spilling_report_to_memory_grant_feedback 介して表示できます。 このイベントは、プランのノード ID と、そのノードの書き込まれたデータ サイズを返します。

調整されたメモリ許可は、 プロパティを介して実際の (実行後) プランに GrantedMemory 表示されます。

このプロパティは、グラフィカルプラン表示のルート演算子またはプラン表示 XML 出力で確認できます。

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

ワークロードにこの改善の対象を自動的に設定するには、データベースの互換性レベル 140 を有効にします。

例:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

メモリ許可フィードバックとパラメーター依存シナリオ

最適化のためには、クエリ プランによってパラメーター値を変えることが必要な場合もあります。 この種のクエリは "パラメーター依存" と定義されます。

パラメーター依存プランでは、メモリ要件が不安定な場合、メモリ許可フィードバック自体がクエリで無効になります。 メモリ許可フィードバック機能は、クエリを繰り返し実行した後に無効になり、拡張イベントを memory_grant_feedback_loop_disabled 監視することで確認できます。 この条件は、SQL Server 2022 (16.x) で導入されたメモリ許可フィードバックの永続性とパーセンタイル モードで軽減されます。 メモリ許可フィードバックの永続化機能では、データベースでクエリ ストアを有効にし、"読み取り書き込み" モードに設定する必要があります。

パラメーター スニッフィングとパラメーターの秘密度の詳細については、「 クエリ処理アーキテクチャ ガイド」を参照してください。

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

フィードバックは、1 回の実行に対してキャッシュされるプランに格納できます。 ただし、メモリ許可フィードバック調整の恩恵を受けるのは、そのステートメントの連続した実行です。 この機能は、ステートメントの反復実行に適用されます。 メモリ許可フィードバックが変更するのはキャッシュされたプランのみです。 SQL Server 2022 (16.x) より前のバージョンでは、変更はクエリ ストアにキャプチャされませんでした。

プランがキャッシュから削除された場合、フィードバックは保持されません。 フェールオーバーがある場合、フィードバックも失われます。 を使用する OPTION (RECOMPILE) ステートメントでは、新しいプランが作成され、キャッシュされません。 キャッシュされないため、メモリ許可フィードバックは生成されません。また、そのコンパイルと実行には格納されません。 ただし、使用 しなかった 同等のステートメント (つまり、同じクエリ ハッシュを使用 OPTION (RECOMPILE) ) がキャッシュされてから再実行された場合、2 回目以降の連続する実行はメモリ許可フィードバックの恩恵を受けることができます。

メモリ許可フィードバック アクティビティを追跡する

拡張イベントを使用して、メモリ許可フィードバック イベントを memory_grant_updated_by_feedback 追跡できます。 このイベントは、現在の実行カウント履歴、メモリ許可フィードバックによってプランが更新された回数、変更前の最適な追加メモリ許可、およびメモリ許可フィードバックによってキャッシュされたプランが変更された後の最適な追加メモリ許可を追跡します。

メモリ許可フィードバック、リソース ガバナー、クエリ ヒント

実際に許可されるメモリは、リソース ガバナーまたはクエリ ヒントによって決定されるクエリ メモリ制限に従います。

互換性レベルを変更せずにバッチ モード メモリ許可フィードバックを無効にする

メモリ許可フィードバックは、データベースの互換性レベル 140 以上を維持しながら、データベースまたはステートメント範囲で無効にできます。 データベースからのすべてのクエリ実行に対してバッチ モード メモリ許可フィードバックを無効にするには、該当するデータベースのコンテキスト内で以下の SQL ステートメントを実行します。

-- 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 で有効として表示されます。

データベースからのすべてのクエリ実行に対してバッチ モード メモリ許可フィードバックを再度有効にするには、該当するデータベースのコンテキスト内で SQL ステートメントを実行します。

-- 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 クエリ ヒントとして指定することで、特定のクエリのバッチ モード メモリ許可フィードバックを無効にすることもできます。 次に例を示します。

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

USE HINT クエリ ヒントは、 データベース スコープの構成 またはトレース フラグ設定よりも優先されます。

行モード メモリ許可フィードバック

適用対象: SQL Server (SQL Server 2019 (15.x) 以降)、Azure SQL データベース

行モード メモリ許可フィードバックは、バッチ モードと行モード両方の演算子のメモリ許可サイズを調整することで、バッチ モード メモリ許可フィードバックの機能を拡張します。

Azure SQL Database で行モード メモリ許可フィードバックを有効にするには、クエリの実行時に接続しているデータベースに対してデータベース互換性レベル 150 以上を有効にします。

例:

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

バッチ モード メモリ許可フィードバックと同様に、行モード メモリ許可フィードバック アクティビティは XEvent を memory_grant_updated_by_feedback 介して表示されます。 また、行モードとバッチ モードの両方に対するメモリ許可フィードバック操作の現在の状態をより適切に表示するために、2 つの新しいクエリ実行プラン属性も導入されています。

メモリ許可フィードバックでは、クエリ ストアは必要ありませんが、SQL Server 2022 (16.x) で導入された永続化の機能強化では、データベースと "読み取り書き込み" 状態でクエリ ストアを有効にする必要があります。 永続化の詳細については、この記事で後述する 「パーセンタイルと永続化モードのメモリ許可フィードバック 」を参照してください。

行モードメモリ許可フィードバック アクティビティは、拡張イベントを memory_grant_updated_by_feedback 介して表示されます。

行モード のメモリ許可フィードバック以降では、実際の実行後プランに対して 2 つの新しいクエリ プラン属性が表示されます。 と LastRequestedMemoryIsMemoryGrantFeedbackAdjusted、クエリ プラン XML 要素にMemoryGrantInfo追加されます。

  • 属性には LastRequestedMemory 、以前のクエリ実行からの許可されたメモリがキロバイト (KB) で表示されます。
  • IsMemoryGrantFeedbackAdjusted属性を使用すると、実際のクエリ実行プラン内の ステートメントのメモリ許可フィードバックの状態を確認できます。

この属性に表示される値は次のとおりです。

IsMemoryGrantFeedbackAdjusted 説明
No:First Execution メモリ許可フィードバックでは、最初のコンパイルと関連付けられた実行のメモリは調整されません。
No:Accurate Grant ディスクにスピルがなく、ステートメントで許可されたメモリの少なくとも 50% が使用されている場合、メモリ許可フィードバックはトリガーされません。
No:Feedback disabled メモリ許可フィードバックが継続的にトリガーされ、メモリの増加操作とメモリ減少操作の間で変動する場合、データベース エンジンは ステートメントのメモリ許可フィードバックを無効にします。
Yes:Adjusting メモリ許可フィードバックが適用されています。また、次の実行に向けてさらに調整される可能性があります。
Yes:Stable メモリ許可フィードバックが適用されています。また、許可されたメモリが安定しています (つまり、前回の実行で許可されたメモリと今回の実行で許可されたメモリが同じです)。

互換性レベルを変更せずに行モードメモリ許可フィードバックを無効にする

行モード メモリ許可フィードバックは、データベースの互換性レベル 150 以上を維持しながら、データベースまたはステートメント範囲で無効にできます。 データベースから発生するすべてのクエリ実行に対して行モードメモリ許可フィードバックを無効にするには、該当するデータベースのコンテキスト内で SQL ステートメントを実行します。

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

データベースを発生源とするすべてのクエリ実行に対して行モード メモリ許可フィードバックを再び有効にするには、該当するデータベースとの関連で次を実行します。

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACKUSE HINT クエリ ヒントとして指定することで、特定のクエリの行モード メモリ許可フィードバックを無効にすることもできます。 次に例を示します。

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

USE HINT クエリ ヒントは、 データベース スコープの構成 またはトレース フラグ設定よりも優先されます。

パーセンタイルと永続化モードのメモリ許可フィードバック

適用対象: SQL Server 2022 (16.x) 以降

この機能は、SQL Server 2022 (16.x) で導入されましたが、このパフォーマンスの向上は、データベース互換性レベル 140 (SQL Server 2017 で導入) 以上、または 140 以上のQUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n ヒント、およびデータベースに対してクエリ ストアが有効になっており、"読み取り書き込み" 状態にあるクエリで使用できます。

  • パーセンタイル メモリ許可フィードバックは、SQL Server 2022 (16.x) では既定で有効になっていますが、クエリ ストアが有効でなく、"読み取り書き込み" 状態の場合は無効になります。
  • メモリ許可、CE、DOP フィードバックの永続化は、SQL Server 2022 (16.x) では既定でオンになっていますが、クエリ ストアが有効でなく、"読み取り書き込み" 状態の場合は影響しません。
  • パーセンタイル メモリ許可フィードバックは、現在、Azure SQL Database および Azure SQL Managed Instance では使用できません。
  • 永続化は現在、Azure SQL Database と Azure SQL Managed Instance では使用できません。

データベースに対して機能が有効になる前に、ワークロードのパフォーマンス ベースラインを用意することをお勧めします。 ベースライン番号は、この機能から意図した利点を得ているかどうかを判断するのに役立ちます。

メモリ許可フィードバック (MGF) は、過去のパフォーマンスに基づいてクエリに割り当てられたメモリのサイズを調整する既存の機能です。 ただし、このプロジェクトの初期フェーズでは、プランとのメモリ許可調整のみがキャッシュに格納されました。プランがキャッシュから削除された場合は、フィードバック プロセスを再度開始する必要があり、削除後にクエリが最初に数回実行される際はパフォーマンスが低下します。 新しい解決策は、クエリ ストア内の他のクエリ情報と共に許可情報を保持し、キャッシュの削除全体で利点が持続するようにすることです。 メモリ許可フィードバックの永続化とパーセンタイルは、メモリ許可フィードバックの既存の制限に悪影響を与えない方法で対処します。

さらに、許可サイズの調整は、最後に使用された許可に対してのみ考慮されました。 そのため、パラメーター化されたクエリまたはワークロードで、実行ごとに大幅に異なるメモリ許可サイズが必要な場合は、最新の許可情報が不正確になる可能性があります。 クエリの実際のニーズが満たされていない可能性があります。 このシナリオのメモリ許可フィードバックは、最後に使用された許可値に基づいて常にメモリを調整しているため、パフォーマンスに役立ちません。 次の図は、パーセンタイルモードと永続化モードを使用しないメモリ許可フィードバックで可能な動作を示しています。

パーセンタイルと永続化モードのメモリ許可フィードバックを使用しないメモリ許可フィードバックの、許可されたメモリ動作と実際に必要なメモリ動作のグラフ。

ご覧のように、この通常とは異なる可能性のあるクエリ動作では、実際に必要なメモリ量と許可されたメモリ量の間の振動により、クエリの実行自体がメモリの量の観点から交互に変化すると、メモリが無駄になり、メモリが不足します。 このシナリオでは、メモリ許可フィードバックはそれ自体を無効にし、良いことよりも害を及ぼしていると認識します。

クエリの最近の履歴に対してパーセンタイルベースの計算を使用すると、単に最後の実行ではなく、過去の実行の使用履歴に基づいて許可サイズの値を滑らかにし、スピルを最小限に抑えるために最適化を試みることができます。 たとえば、同じ交互のワークロードでは、次のメモリ許可動作が表示されます。

パーセンタイルと永続化モードのメモリ許可フィードバックを含むメモリ許可フィードバックの、許可されたメモリ動作と実際に必要なメモリ動作のグラフ。

クエリ オプティマイザーは、キャッシュされたプランの実行に対して過去のメモリ許可サイズ設定要件の高いパーセンタイルを使用して、クエリ ストアに永続化されたデータを使用してメモリ許可サイズを計算します。 メモリ許可の調整を実行するパーセンタイル調整は、実行の最近の履歴に基づいています。 時間の経過と同時に、与えられたメモリ許可によって、スピルと無駄なメモリが削減されます。

永続化は 、DOP フィードバックCE フィードバックにも適用されます。この記事でも詳しく説明します。

メモリ許可フィードバックを有効にする: 永続化とパーセンタイル

メモリ許可フィードバックの永続化とパーセンタイルを有効にするには、クエリの実行時に接続しているデータベースに対してデータベース互換性レベル 140 以上を使用します。 Peristence フィードバックとパーセンタイル フィードバックは 、既定で有効になっています

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

この機能の永続化部分が使用されるすべてのデータベースで、クエリ ストアを有効にする必要があります。

パーセンタイルを無効にする

データベースから発生するすべてのクエリ実行に対してメモリ許可フィードバック パーセンタイルを無効にするには、該当するデータベースのコンテキスト内で次を実行します。

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE = OFF;

MEMORY_GRANT_FEEDBACK_PERCENTILE の既定の設定は OFF です。

永続化を無効にする

データベースに端を発するあらゆるクエリ実行に対し、メモリ許可フィードバック永続化を無効にする場合。

該当するデータベースのコンテキスト内で次を実行します。

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

メモリ許可フィードバック永続化を無効にすると、収集された既存のフィードバックも削除されます。

MEMORY_GRANT_FEEDBACK_PERSISTENCE の既定の設定は ON です。

メモリ付与に関するフィードバックに関する考慮事項

sys.database_scoped_configurations にクエリを実行すると、現在の設定を表示できます。

Note

と の両方BATCH_MODE_MEMORY_GRANT_FEEDBACKROW_MODE_MEMORY_GRANT_FEEDBACKが OFF に設定されている場合、この機能は機能しません。

フィードバック データがクエリ ストアに保持されるようになったため、クエリ ストアの使用要件が若干増加しています。

パーセンタイルベースのメモリ許可は、スピルを減らしすぎて失敗する傾向があります。 最後の実行だけでなく、過去のいくつかの実行の観察に基づくようになったため、変動するワークロードのメモリ使用量が増加し、実行間のメモリ許可要件の違いが大きくなる可能性があります。

SQL Server 2022 (16.x) 以降では、セカンダリ レプリカのクエリ ストアが有効になると、可用性グループ内のセカンダリ レプリカに対してメモリ許可フィードバックがレプリカ対応になります。 メモリ許可フィードバックは、プライマリ レプリカとセカンダリ レプリカで異なる方法でフィードバックを適用できます。 ただし、メモリ許可フィードバックはセカンダリ レプリカでは保持されず、フェールオーバー時には、古いプライマリ レプリカからのメモリ許可フィードバックが新しいプライマリ レプリカに適用されます。 セカンダリ レプリカがプライマリ レプリカになったときにセカンダリ レプリカに適用されたフィードバックはすべて失われます。 詳細については、「セカンダリ レプリカのクエリ ストア」を参照してください。

並列処理の次数 (DOP) のフィードバック

適用対象: SQL Server 2022 (16.x) 以降

SQL Server 2022 (16.x) では、経過時間と待機に基づいて、クエリを繰り返す並列処理の非効率性を特定することでクエリのパフォーマンスを向上させるために、並列処理の程度 (DOP) フィードバックと呼ばれる新しい機能が導入されました。 DOP フィードバックは、インテリジェント クエリ処理ファミリ機能の一部であり、反復するクエリに対する並列処理の最適化されていない使用に対応するものです。 このシナリオは、過剰な並列処理がパフォーマンスの問題を引き起こす可能性のある場合に、ワークロードのリソース使用量の最適化とスケーラビリティの向上に役立ちます。 DOP フィードバックでは、各クエリに対する包括的な既定の調整や手動による調整の問題が発生する代わりに、DOP を自己調整して、上記の問題を回避します。

DOP フィードバックでは、各クエリに対する包括的な既定の調整や手動による調整の問題が発生する代わりに、DOP を自己調整して過剰な並列処理を回避します。 並列処理の使用が非効率的と見なされる場合、DOP フィードバックは、構成されている DOP とは関係なく、クエリの次回の実行に対する DOP を低下させ、それが役立つかどうかを確認します。

多くの場合、並列処理は、レポートや分析のクエリ、または大量のデータを処理するクエリに役立ちます。 逆に、並列で実行される OLTP 中心のクエリでは、すべてのスレッドの調整にかかる時間が、並列プランを使う場合の利点を上回る場合、パフォーマンスの問題が発生する可能性があります。 詳細については、並列プランの実行に関する記事を参照してください。

  • DOP フィードバックを有効にするには、データベースで DOP_FEEDBACKデータベース スコープの構成 を有効にします。

  • DOP フィードバックを使うすべてのデータベースでクエリ ストアを有効にし、"読み取り書き込み" 状態にしておく必要があります。 フィードバックは、安定した並列処理のフィードバック値に達すると 、sys.query_store_plan_feedback カタログ ビューに保持されます。

  • DOP フィードバックは、データベース互換性レベル 160 (SQL Server 2022 (16.x) で導入) 以降で動作するクエリで使用できます。

  • 検証済みのフィードバックのみが保持されます。 調整された DOP の結果、パフォーマンスが低下した場合、DOP フィードバックは最後に確認された良好な DOP に戻されます。 このコンテキストでは、ユーザーが取り消したクエリも回帰として認識されます。 DOP フィードバックでは、プランは再コンパイルされません。

  • プランの再コンパイル時に安定したフィードバックが評価され、アップまたはダウンを再調整できますが、MAXDOP 設定 (MAXDOP ヒントを含む) を超えてはいけません。

  • データベース レベルで DOP フィードバックを無効にするには、データベース スコープの構成ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFF使用します。

  • クエリ レベルで DOP フィードバックを無効にするには、DISABLE_DOP_FEEDBACK クエリ ヒントを使用します。

  • SQL Server 2022 (16.x) 以降では、セカンダリ レプリカのクエリ ストアが有効になっている場合、DOP フィードバックは可用性グループ内のセカンダリ レプリカでもレプリカ対応になります。 DOP フィードバックは、プライマリ レプリカとセカンダリ レプリカで異なる方法でフィードバックを適用できます。 ただし、DOP フィードバックはセカンダリ レプリカでは保持されず、フェールオーバー時には、古いプライマリ レプリカからの DOP フィードバックは新しいプライマリ レプリカには適用されません。 フェールオーバー時に、プライマリ レプリカまたはセカンダリ レプリカに適用されたフィードバックが失われます。 詳細については、「セカンダリ レプリカのクエリ ストア」を参照してください。

DOP フィードバックの実装

DOP フィードバックは、経過時間と待機に基づいて、クエリを繰り返す並列処理の非効率性を特定します。 並列処理の使用が非効率的と見なされる場合、DOP フィードバックは、構成されている DOP とは関係なく、クエリの次回の実行に対する DOP を下げ、それが役立つかどうかを確認します。

クエリの適格性を評価するために、調整されたクエリ経過時間は、数回の実行に対して測定されます。 各クエリの合計経過時間は、並列クエリ実行の外部にあるバッファー ラッチ、バッファー IO、ネットワーク IO の待機時間を無視して調整されます。 DOP フィードバック機能の目的は、クエリの経過時間がわずかに増加した場合でも、全体的なコンカレンシーを増やし、待機時間を大幅に減らすことです。

検証済みのフィードバックのみが保持されます。 調整された DOP の結果、パフォーマンスが低下した場合、DOP フィードバックは最後に確認された良好な DOP に戻されます。 このコンテキストでは、ユーザーが取り消したクエリも回帰として認識されます。

Note

DOP フィードバックでは、プランは再コンパイルされません。

DOP フィードバックに関する考慮事項

DOP フィードバックで調整されたクエリの最小 DOP は 2 です。 シリアル実行は DOP フィードバックの範囲外です。

フィードバック情報は、 sys.query_store_plan_feedback カタログ ビューを使用して追跡できます。

クエリ ストアを通じて強制されたクエリ プランがクエリに含まれている場合でも、そのクエリに DOP フィードバックを使用できます。

クエリで MAXDOP ヒントをハードコーディングされたクエリ ヒントとして使用するか、クエリ ストアヒント メカニズムを使用して使用し、MAXDOP ヒントが 2 より大きい場合、DOP フィードバックではヒント値を上限として使用して DOP が低下します。 詳細については、「ヒント (Transact-SQL) - Query」と「クエリ ストア ヒント」を参照してください。

DOP フィードバックの拡張イベント

この機能では、次の XE を使用できます。

  • dop_feedback_eligible_query: クエリ プランが DOP フィードバックの対象となる場合に発生します。 再コンパイルまたは SQL Server インスタンスの再起動が発生した場合、追加のイベントが発生することがあります。
  • dop_feedback_provided: DOP フィードバックにより、特定のクエリのデータが提供されたときに発生します。 このイベントには、初めてフィードバックが提供されたときのベースライン統計情報と、それ以降のフィードバックが提供されたときの以前のフィードバック統計情報が含まれます。
  • dop_feedback_validation: 基準値または以前のフィードバックの統計情報に対に対してクエリ ランタイム統計情報の検証が行われたときに発生します。
  • dop_feedback_stabilized: クエリに対する DOP フィードバックが安定したときに発生します。
  • dop_feedback_reverted: DOP フィードバックが元に戻されたときに発生します。 このイベントは、最初に提供されたフィードバックでフィードバックの検証が失敗したときに発生します。 システムによって、フィードバックなしの状態に戻されます。
  • dop_feedback_analysis_stopped: あるクエリに対する DOP フィードバック分析が停止したときに発生します。

カーディナリティ推定 (CE) フィードバック

適用対象: SQL Server 2022 (16.x) 以降。

SQL Server 2022 (16.x) 以降では、カーディナリティ推定 (CE) フィードバックは、インテリジェントなクエリ処理ファミリの機能の一部であり、これらの問題が正しくない CE モデルの想定に起因する場合にクエリを繰り返す最適でないクエリ実行プランに対処します。 このシナリオは、古いバージョンのデータベース エンジンからアップグレードするときに、既定の CE に関連する回帰リスクを軽減するのに役立ちます。

単一セットの CE モデルと前提条件で膨大なワークロードとデータ分散に対応することはできないため、CE フィードバックによって、クエリ ランタイムの特性に基づいた、適応可能なソリューションが提供されます。 CE フィードバックでは、クエリ実行プランの品質を向上させるために、特定のクエリとデータ分散に適合するモデルの前提条件が特定され、使用されます。 フィードバックは、パフォーマンスの低下に起因する重大なモデル推定エラーが見つかった場合に適用されます。

  • SQL Server 2022 (16.x) 以降では、セカンダリ レプリカのクエリ ストアが有効になっている場合、CE フィードバックは可用性グループ内のセカンダリ レプリカに対してレプリカ対応ではありません。 現在、CE フィードバックはプライマリ レプリカにのみメリットがあります。 詳細については、「セカンダリ レプリカのクエリ ストア」を参照してください。

カーディナリティ推定について

カーディナリティ推定 (CE) は、クエリ プランの各レベルで処理される行の合計数を見積もるためにクエリ オプティマイザーで使用される方法です。 SQL Server でのカーディナリティ推定は、インデックスまたは統計を作成するときに手動か自動で作成されたヒストグラムから主に取得されます。 また、SQL Server では、クエリの制約情報および論理再書き込みを使用して、カーディナリティが決定されることもあります。

データベース エンジンの各バージョンでは、データの分散方法とクエリ方法に基づいて、異なる CE モデルの前提条件が使用されます。 詳細については、「CE のバージョン」を参照してください。

CE フィードバックの実装

CE フィードバックでは、どの CE モデル前提条件が最適であるかが時間の経過に応じて学習され、履歴から見て最も正しい前提条件が適用されます。

  1. CE フィードバックによって、モデル関連の前提条件が識別され、それらがクエリの繰り返しに対して正確であるかどうかが評価されます。

  2. 前提条件が正しくないと思われる場合は、影響を受ける CE モデル前提条件を調整し、それが役立つかどうかを確認するクエリ プランを使用して、同じクエリの後続の実行がテストされます。

  3. プランの品質が向上した場合、古いクエリ プランは、推定モデルを調整する適切な USE HINT クエリ ヒント (クエリ ストア ヒント メカニズムを通じて実装されます) を使用するクエリ プランに置き換えられます

検証済みのフィードバックのみが保持されます。 調整されたモデル前提条件によってパフォーマンスの低下が生じる場合、そのクエリには CE フィードバックは使用されません。 このコンテキストでは、ユーザーが取り消したクエリも回帰として認識されます。

CE フィードバックのシナリオ

CE フィードバックは、既定の CE (CE120 以上) を使用する場合に、不適切な CE モデル前提条件によって発生する回帰の問題に対処するものです。これにより、異なるモデル前提条件を選択的に使用できます。 シナリオには、関連付け、結合包含、オプティマイザー行の目標が含まれます。

Correlation

クエリ オプティマイザーでは、特定のテーブルやビューに対する述語の選択性、またはその述語を満たす行数を見積もる際に、相関モデルの前提条件が使用されます。 これらの前提条件では、次の述語が考えられます。

  • 完全に独立 (CE70 の既定値)。カーディナリティは、すべての述語の選択性を乗算して計算されます。

  • 部分的に相関 (CE120 以降の既定値)。カーディナリティは指数バックオフの変動を使用して計算されます。選択性は、最も選択的な述語から最も選択的でない述語への順で並べ替えられます。

  • 完全に相関。カーディナリティは、すべての述語に対して最小の選択性を使用してが計算されます。

次の例では、データベースの互換性が 120 以上に設定されている場合に、部分的相関が使用されます。

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

データベースの互換性が 160 に設定されていて、既定の相関関係が使用されている場合、CE フィードバックでは、推定カーディナリティが実際の行数と比較して過小評価されたか過大評価されたかに基づいて、相関を一度に 1 ステップずつ正しい方向に移動することが試行されます。 実際の行数が推定カーディナリティを超える場合は、完全相関を使用します。 実際の行数が推定カーディナリティよりも小さい場合は、完全独立を使用します。

詳細については、「CE のバージョン」を参照してください。

結合含有

クエリ オプティマイザーでは、結合述語と適用可能なフィルター述語の選択性を推定する際、包含モデルの前提条件が使用されます。 前提条件は次のとおりです。

  • 単純包含 (CE70 の既定値)。結合述語が完全に相関していることが前提とされます。最初にフィルターの選択性が計算され、その後結合の選択性が考慮されます。

  • 基本包含 (CE120 以降の既定)。結合述語とダウンストリーム フィルターの間に相関関係がないことが前提とされます。

最初に結合の選択性が計算され、その後フィルターの選択性が考慮されます。

次の例では、データベースの互換性が 120 以上に設定されている場合に、基本包含が使用されます。

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

詳細については、「CE のバージョン」を参照してください。

オプティマイザーの行の目標

クエリ オプティマイザーで実行プランのカーディナリティが推定される際、通常は、すべてのテーブルのすべての修飾行を処理する必要があると想定されます。 ただし、一部のクエリ パターンでは、I/O を削減するために、より少ない数の行を返すプランがクエリ オプティマイザーによって検索されます。 クエリで、実行時に予期される行数 (行の目標) が、TOPIN、または EXISTS キーワード、FAST クエリ ヒント、または SET ROWCOUNT ステートメントを使用して指定された場合は、その行目標が、次の例のようにクエリ最適化プロセスの一部として使用されます。

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

行目標プランが適用された場合、クエリ オプティマイザーでは、行目標を達成するために処理する必要がある行の数が少なくなると想定されるため、クエリ プラン内の推定行数が減ります。

行目標は特定のクエリ パターンについては有益な最適化戦略ですが、データが均一に分散されていない場合は、予測よりも多くのページがスキャンされる可能性もあります。つまり、行目標が非効率なものとなります。 CE フィードバックでは、この非効率性が検出されたときに行目標スキャンを無効にし、シークを有効にすることができます。

実行プランには CE フィードバックに固有の属性はありませんが、クエリ ストア ヒントの属性が一覧表示されます。 を 探しますQueryStoreStatementHintSourceCE feedback

CE フィードバックに関する考慮事項

CE フィードバックを有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 160 を有効にします。 CE フィードバックが使用されるすべてのデータベースに対して、クエリ ストアを有効にし、READ_WRITE モードにする必要があります。

CE フィードバック アクティビティは、query_feedback_analysis および query_feedback_validation XEvent を介して表示できます。

CE フィードバックによって設定されたヒントは、sys.query_store_query_hints カタログ ビューを使用して追跡できます。

フィードバック情報は、 sys.query_store_plan_feedback カタログ ビューを使用して追跡できます。

データベース レベルで CE フィードバックを無効にするには、データベース スコープ構成ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF使用します。

クエリ レベルで CE フィードバックを無効にするには、DISABLE_CE_FEEDBACK クエリ ヒントを使用します。

クエリ ストアを通じて強制されたクエリ プランがクエリに含まれている場合、そのクエリに CE フィードバックを使用することはできません。

クエリで、ハードコーディングされたクエリ ヒントが使用されている場合や、ユーザーによって設定されたクエリ ストア ヒントが使用されている場合、そのクエリに CE フィードバックを使用することはできません。 詳細については、「ヒント (Transact-SQL) - Query」と「クエリ ストア ヒント」を参照してください。

フィードバックとレポートの問題

フィードバックまたは質問については、電子メール CEFfeedback@microsoft.com

次の手順