メモリ許可フィードバック
適用対象: SQL Server 2017 (14.x) 以降、Azure SQL Managed Instance、Azure SQL データベース
メモリ許可が大きすぎるか小さすぎるクエリが実行される場合があります。 メモリ許可が大きすぎる場合は、サーバーでの並列処理が禁止されます。 小さすぎると、ディスクに流出する可能性があり、これはコストのかかる操作です。 メモリ許可フィードバックは、実行前のメモリニーズを記憶しようとします (パーセンタイル フィードバック、複数の過去の実行)。 この履歴のクエリ情報に基づいて、メモリ許可フィードバックにより、後続の実行に応じてクエリに与えられた許可が調整されます。
この機能は、3 つのウェーブでリリースされています。 バッチ モード メモリ許可フィードバック、続いて行モード メモリ許可フィードバック、および SQL Server 2022 (16.x) では、クエリ ストアとパーセンタイル付与と呼ばれる改良されたアルゴリズムを使用したディスク上の永続化に関するメモリ許可フィードバックが導入されました。
Note
その他のクエリ フィードバック機能については、「カーディナリティ推定 (CE) のフィードバック」と「並列処理の次数 (DOP) のフィードバック」を参照してください。
バッチ モード メモリ許可フィードバック
適用対象: SQL Server (SQL Server 2017 (14.x) 以降)、Azure SQL データベース および Azure SQL Managed Instance (データベース互換レベル 140 以降)
クエリの実行プランには、実行に最低限必要なメモリと、すべての行をメモリに収めるのに理想的なメモリ許可サイズが含まれます。 メモリ許可サイズが正しくない場合、パフォーマンスが低下します。 メモリ許可が多すぎると、メモリが無駄になり、コンカレンシーが制限されます。 メモリ許可が少なすぎると、負荷の高いディスクへの書き込みが発生する原因になります。 繰り返されるワークロードを処理することにより、バッチ モード メモリ許可フィードバックはクエリに実際に必要なメモリ量を再計算し、キャッシュされたプランの許可値を更新します。 同じクエリ ステートメントを実行するとき、クエリは、修正されたメモリ許可サイズを使うことで、コンカレンシーに影響を与える過剰なメモリ許可を減らし、負荷の高いディスクへの書き込みが発生する過少なメモリ許可を修正します。
次のグラフでは、バッチ モード アダプティブ メモリ許可フィードバックを使用する 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;
メモリ許可フィードバックを有効にした 2 番目の実行では、所要時間は "1 秒" で (88 秒から短縮)、ディスクへの書き込みはまったくなくなり、許可は高くなっています。
メモリ許可フィードバックのサイズ決定
メモリ許可条件が過剰な場合、許可されるメモリが実際に使われるメモリ サイズの 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) で導入されたメモリ許可フィードバックの永続化とパーセンタイル モードで軽減されます。 メモリ許可フィードバックの永続化機能では、データベースでクエリ ストアを有効にし、"読み取り書き込み" モードに設定する必要があります。
パラメーター スニッフィングとパラメーターの感度の詳細については、「Query Processing Architecture Guide」(クエリ処理アーキテクチャ ガイド) を参照してください。
メモリ許可フィードバックのキャッシュ
フィードバックは、1 回の実行に対してキャッシュされるプランに格納できます。 ただし、メモリ許可フィードバックのチューニングによってメリットがあるのは、そのステートメントを連続して実行する場合です。 この機能は、ステートメントの反復実行に適用されます。 メモリ許可フィードバックが変更するのはキャッシュされたプランのみです。 SQL Server 2022 (16.x) より前のバージョンでは、変更はクエリ ストアにキャプチャされませんでした。
プランがキャッシュから削除された場合、フィードバックは保持されません。 フェールオーバーが発生した場合もフィードバックは失われます。 OPTION (RECOMPILE) を使うステートメントでは、新しいプランが作成されますが、プランはキャッシュされません。 キャッシュされないため、メモリ許可フィードバックは生成されず、そのコンパイルおよび実行に対して格納されません。 ただし、OPTION (RECOMPILE) を 使わない 同等のステートメント (つまり、クエリ ハッシュが同じ) がキャッシュされて再実行された場合、連続するステートメントでメモリ許可フィードバックのメリットがある場合があります。
メモリ許可フィードバック アクティビティの追跡
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_FEEDBACK を USE 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 Managed Instance (データベース互換レベル 150 以降)
行モード メモリ許可フィードバックは、バッチ モードと行モード両方の演算子のメモリ許可サイズを調整することで、バッチ モード メモリ許可フィードバックの機能を拡張します。
Azure SQL Databaseの行モード メモリ許可フィードバックを有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 150 以上を有効にします。
例:
ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;
バッチ モードメモリ許可フィードバックと同様に、memory_grant_updated_by_feedback XEventを使用すると行モード メモリ許可フィードバック アクティビティが表示されます。 また、行モードとバッチ モードの両方に対するメモリ許可フィードバック操作の現在の状態をより適切に把握するために、2 つの新しいクエリ実行プラン属性も導入されています。
ただし、メモリ許可フィードバックは、クエリ ストアを要求しませんが、SQL Server 2022 (16.x) で導入された永続化の機能強化では、データベースと 「読み取り/書き込み」 状態に対してクエリ ストアを有効にする必要があります。 永続化の詳細については、この記事の後半の「パーセンタイルと永続化モードのメモリ許可フィードバック」を参照してください。
行モードのメモリ許可フィードバック アクティビティは、 memory_grant_updated_by_feedback 拡張イベントを介して表示されます。
行モード メモリ許可フィードバックから始まり、MemoryGrantInfo クエリ プラン XML 要素に追加される IsMemoryGrantFeedbackAdjusted と LastRequestedMemory という、2 つの新しいクエリ プラン属性が実際の実行後プランに対して表示されます。
LastRequestedMemory属性は、前のクエリの実行から許可されたメモリがキロバイト (KB) で表示されます。IsMemoryGrantFeedbackAdjusted属性を使用すると、実際のクエリ実行プラン内のステートメントに対するメモリ許可フィードバックの状態を確認できます。
この属性に表示される値は次のとおりです。
IsMemoryGrantFeedbackAdjusted 値 |
説明 |
|---|---|
| No: First Execution | メモリ許可フィードバックは、最初のコンパイルとそれに関連付けられた実行ではメモリをチューニングしません。 |
| No: Accurate Grant | ディスクへの書き込みが存在せず、許可されたメモリの少なくとも 50% がステートメントによって使用されている場合、メモリ許可フィードバックはトリガーされません。 |
| No: Feedback disabled | メモリ許可フィードバックが継続的にトリガーされ、メモリを増加させる操作と減少させる操作の間で変動している場合、データベース エンジンでは、ステートメントのメモリ許可フィードバックは無効にされます。 |
| Yes: Adjusting | メモリ許可フィードバックが適用されています。また、次の実行に向けてさらに調整される可能性があります。 |
| はい: パーセンタイルチューニングです | メモリ許可フィードバックは、パーセンタイル付与アルゴリズムを使用して適用されています。これは、最新の実行よりも多くの履歴を調べられます。 |
| Yes: Stable | メモリ許可フィードバックが適用されています。また、許可されたメモリが安定しています (つまり、前回の実行で許可されたメモリと今回の実行で許可されたメモリが同じです)。 |
パーセンタイルと永続化モードのメモリ許可フィードバック
適用対象: SQL Server (SQL Server 2022 (16.x) 以降)、Azure SQL データベース、Azure SQL Managed Instance (現在、永続化のみ)
この機能は SQL Server 2022 (16.x) で導入されましたが、このパフォーマンスの向上は、データベース互換性レベル 140 (SQL Server 2017 で導入) 以降、または QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n ヒント 140 以上で動作するクエリ、およびクエリ ストアがデータベースに対して有効になっており、"読み取り書き込み" 状態である場合に使用できます。
- パーセンタイル メモリ許可フィードバックは、SQL Server 2022 (16.x) では既定で有効になっていますが、クエリ ストアが有効になっていない場合や、クエリ ストアが "読み取り書き込み" 状態でない場合は影響しません。
- SQL Server 2022 (16.x) では、メモリ許可、CE、DOP フィードバックの永続化は既定でオンになっていますが、クエリ ストアが有効になっていない場合や、クエリ ストアが "読み取り書き込み" 状態でない場合は影響しません。
- メモリ許可フィードバックのパーセンタイルと永続化は Azure SQL Database で使用でき、既存と新規の両方のすべてのデータベースで既定で有効になっています。
- メモリ許可フィードバックのパーセンタイルと永続化は、現在、Azure SQL Managed Instance では使用できません。
データベースで機能を有効にする前に、ワークロード用のパフォーマンス ベースラインを用意することをおすすめします。 ベースラインの数値は、機能から意図した利点を得られているかどうかを判断するのに役立ちます。
メモリ許可フィードバック (MGF) は、過去のパフォーマンスに基づいてクエリに割り当てられるメモリのサイズをチューニングする既存の機能です。 ただし、このプロジェクトの初期フェーズでは、プランとのメモリ許可調整のみがキャッシュに格納されました。プランがキャッシュから削除された場合は、フィードバック プロセスを再度開始する必要があり、削除後にクエリが最初に数回実行される際はパフォーマンスが低下します。 新しい解決策は、クエリ ストアで他のクエリ情報と共に許可情報を保持し、キャッシュの削除をまたいで利点が持続するようにすることです。 メモリ許可フィードバックの永続化とパーセンタイルは、メモリ許可フィードバックの既存の制限に悪影響を与えない方法で対処します。
さらに、許可サイズの調整は、最後に使用された許可に対してのみ考慮されました。 そのため、パラメーター化クエリまたはワークロードで大幅に異なるメモリ許可サイズが各実行で必要な場合は、最後の許可情報が不正確になるおそれがありました。 実行されるクエリの実際のニーズとずれるおそれもありました。 このシナリオでのメモリ許可フィードバックは、最後に使用された許可値に基づいて常にメモリをチューニングしているため、パフォーマンスに役立ちません。 次の図は、パーセンタイルモードと永続化モードを使用しないメモリ許可フィードバックで可能な動作を示しています。
ご覧のように、この通常とは異なる可能性のあるクエリ動作では、実際に必要なメモリ量と許可されたメモリ量の間の振動により、メモリの量の点でクエリの実行自体が代替される場合、メモリが無駄になり、メモリが不足します。 このシナリオでは、メモリ許可フィードバックは自分自身を無効にします。メリットよりもデメリットの方が大きくなることを認識するためです。
クエリの最近の履歴に対してパーセンタイルベースの計算を使用すると、単に最後の実行ではなく、過去の実行の使用履歴に基づいて許可サイズの値をスムーズにし、スピルを最小限に抑えるために最適化を試みることができます。 たとえば、同じ交互のワークロードでは、次のメモリ許可動作が表示されます。
クエリ オプティマイザーは、キャッシュされたプランの実行に関する過去のメモリ許可のサイズ設定要件の高いパーセンタイルを使用して、クエリ ストアに保持されているデータを使用してメモリ許可サイズを計算します。 最近の実行の履歴 に基づいてメモリ許可のチューニングを実行するパーセンタイルチューニング。 時間の経過と同時に、与えられたメモリ許可によって、スピルと無駄なメモリが削減されます。
永続化は、DOP フィードバックと CE フィードバックにも適用されます。
メモリ許可フィードバック機能を有効または無効にする
互換性レベルを変更せず、行モード メモリ許可フィードバックを無効にする
行モード メモリ許可フィードバックは、データベースの互換性レベル 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_FEEDBACK を USE HINT クエリ ヒントとして指定することで、特定のクエリの行モード メモリ許可フィードバックを無効にすることもできます。 次に例を示します。
SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));
USE HINT クエリ ヒントは、 データベース スコープ構成 またはトレース フラグ設定に優先します。
メモリ許可フィードバックの永続化とパーセンタイルを有効にする
永続化とパーセンタイルフィードバックは、 Azure SQL Database と SQL Server 2022 (16.x) で既定 で有効になっています。
メモリ許可フィードバックの永続化とパーセンタイルを有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換レベル 140 以上を使用します。 これを変更するには、 ALTER DATABASEを使用します:
ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER
この機能の永続化部分が使用されるすべてのデータベースで、クエリ ストアを有効にする必要があります。
パーセンタイルを無効にする
データベースを発生源とするすべてのクエリ実行に対して行モード メモリ許可フィードバックパーセンタイルを無効にするには、該当するデータベースとの関連で次を実行します。
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;
MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT の既定の設定は ON です。
永続化を無効にする
データベースに端を発するあらゆるクエリ実行に対し、メモリ許可フィードバック永続化を無効にする場合。
該当するデータベースのコンテキスト内で次を実行します。
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;
メモリ許可フィードバック永続化を無効にすると、収集された既存のフィードバックも削除されます。
MEMORY_GRANT_FEEDBACK_PERSISTENCE の既定の設定は ON です。
メモリ許可フィードバックに関する考慮事項
sys.database_scoped_configurationsクエリを実行すると、現在の設定を表示できます。
Note
BATCH_MODE_MEMORY_GRANT_FEEDBACK と ROW_MODE_MEMORY_GRANT_FEEDBACK が両方とも OFF に設定されている場合、この機能は機能しません。
フィードバック データがクエリ ストアに保持されるようになったため、クエリ ストアの使用量の要件が若干増加しています。
パーセンタイルベースのメモリ許可は、スピルを減らしすぎて失敗する傾向があります。 最後の実行だけでなく、過去のいくつかの実行の観察に基づくようになったため、変動するワークロードのメモリ使用量が増加し、実行間のメモリ許可要件の違いが大きくなる可能性があります。
SQL Server 2022 (16.x) 以降では、セカンダリ レプリカのクエリ ストアが有効になっている場合、可用性グループ内のセカンダリ レプリカに対してメモリ許可フィードバックがレプリカ対応になります。 メモリ許可フィードバックは、プライマリ レプリカとセカンダリ レプリカで異なる方法でフィードバックを適用できます。 ただし、メモリ許可フィードバックはセカンダリ レプリカでは保持されず、フェールオーバー時には、古いプライマリ レプリカからのメモリ許可フィードバックが新しいプライマリ レプリカに適用されます。 セカンダリ レプリカがプライマリ レプリカになったときにセカンダリ レプリカに適用されたフィードバックはすべて失われます。 詳細については、「セカンダリ レプリカ のクエリ ストア」を参照してください。