適用対象:
SQL Server 2017 (14.x) 以降のバージョン Azure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース
クエリが実行される際に、許可されるメモリが大きすぎたり小さすぎたりすることがあります。 許可されるメモリが大きすぎる場合、サーバー上での並列処理が抑制されます。 逆に小さすぎると、ディスクへのスピルが発生し、これには高いコストがかかります。 メモリ許可フィードバックでは、前回実行時 (パーセンタイル フィードバックの場合は複数回分) に必要だったメモリ量が記録されます。 その履歴データに基づいて、次回以降の実行でクエリに許可されるメモリが自動的に調整されます。
この機能は 3 段階でリリースされました。 まず Batch モードのメモリ許可フィードバック、次に行モードのメモリ許可フィードバックが導入されました。そして SQL Server 2022 (16.x) では、クエリ ストアによるディスク上の永続化と、パーセンタイル許可と呼ばれる改良アルゴリズムを採用した、メモリ許可フィードバックが導入されました。
注
その他のクエリ フィードバック機能については、「カーディナリティ推定 (CE) フィードバック」および「並列処理の次数 (DOP) フィードバック」を参照してください。
Batch モード メモリ許可フィードバック
適用対象: SQL Server 2017 (14.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance (データベース互換性レベル 140 以降)。
クエリの実行プランには、実行に最低限必要なメモリと、すべての行をメモリに収めるのに理想的なメモリ許可サイズが含まれます。 メモリ許可サイズが正しくない場合、パフォーマンスが低下します。 許可されるメモリが大きすぎると、メモリが無駄になり、コンカレンシーが制限されます。 逆に小さすぎると、負荷の高いディスクへのスピルが発生する原因になります。 繰り返されるワークロードを処理することにより、Batch モード メモリ許可フィードバックはクエリに実際に必要なメモリ量を再計算し、キャッシュされたプランの許可値を更新します。 同じクエリ ステートメントを実行するとき、クエリは、修正されたメモリ許可サイズを使うことで、コンカレンシーに影響を与える過剰なメモリ許可を減らし、負荷の高いディスクへのスピルが発生する過少なメモリ許可を修正します。
次のグラフでは、Batch モード アダプティブ メモリ許可フィードバックを使用する 1 つの例を示します。 最初のクエリ実行の場合、ディスクへのスピルが多いため所要時間は "88 秒" でした。
DECLARE @EndTime AS DATETIME = '2016-09-22 00:00:00.000';
DECLARE @StartTime AS 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 未満のプランについては、超過分の再計算は行われません。
メモリ許可条件が過少な場合、Batch モード演算子でディスクへのスピルが発生すると、メモリ許可フィードバックはメモリ許可の再計算をトリガーします。 スピルの発生はメモリ許可フィードバックに報告され、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 拡張イベントを使用すれば、メモリ許可フィードバックの動作を追跡できます。 このイベントは、現在の実行カウント履歴、メモリ許可フィードバックによってプランが更新された回数、変更前の最適な追加メモリ許可、およびメモリ許可フィードバックによってキャッシュされたプランが変更された後の最適な追加メモリ許可を追跡します。
メモリ許可フィードバック、リソース ガバナー、クエリ ヒント
実際に許可されるメモリは、リソース ガバナーまたはクエリ ヒントによって決定されるクエリ メモリ制限に従います。
互換性レベルを変更せず、Batch モード メモリ許可フィードバックを無効にする
メモリ許可フィードバックは、データベースの互換性レベル 140 以上を維持しながら、データベースまたはステートメント範囲で無効にできます。 データベースから送信されるすべてのクエリ実行に対してバッチ モードメモリ許可フィードバックを無効にするには、該当するデータベースのコンテキスト内で以下の Transact-SQL ステートメントを実行します。
SQL Server 2017 (14.x) の場合:
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database の場合:
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
有効になっているとき、この設定は sys.database_scoped_configurations で有効として表示されます。
データベースから送信されるすべてのクエリ実行に対してバッチ モードメモリ許可フィードバックを再度有効にするには、該当するデータベースのコンテキスト内で Transact-SQL ステートメントを実行します。
SQL Server 2017 (14.x) の場合:
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;SQL Server 2019 (15.x) 以降のバージョンと Azure SQL Database の場合:
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK を USE HINT クエリ ヒントとして指定することで、特定のクエリの Batch モード メモリ許可フィードバックを無効にすることもできます。 たとえば、次が挙げられます。
SELECT *
FROM Person.Address
WHERE City = 'SEATTLE'
AND PostalCode = 98104
OPTION (USE HINT('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));
USE HINT クエリ ヒントは、データベース スコープの構成またはトレース フラグの設定よりも優先されます。
行モード メモリ許可フィードバック
適用対象: SQL Server 2019 (15.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance (データベース互換性レベル 150 以降)。
行モード メモリ許可フィードバックは、Batch モードと行モード両方の演算子のメモリ許可サイズを調整することで、Batch モード メモリ許可フィードバックの機能を拡張します。
Azure SQL Database で行モード メモリ許可フィードバックを有効にするには、クエリを実行する際に接続されるデータベースのデータベース互換性レベル 150 以上を有効にします。
例:
ALTER DATABASE [<database name>]
SET COMPATIBILITY_LEVEL = 150;
Batch モード メモリ許可フィードバックと同様に、行モードのフィードバックの動作も memory_grant_updated_by_feedback XEvent を通じて確認できます。 また、行モードと Batch モード両方のメモリ許可フィードバックの状態をより明確に把握できるよう、クエリ実行プランに 2 つの新しい属性が追加されました。
メモリ許可フィードバック自体はクエリ ストアを必要としませんが、SQL Server 2022 (16.x) で改良・導入された永続化を使用するには、データベースでクエリ ストアを有効にし、「読み書き可能」状態にしておく必要があります。 永続化の詳細については、この記事の後半の「パーセンタイルと永続化モードのメモリ許可フィードバック」を参照してください。
行モードのメモリ許可フィードバックの動作も memory_grant_updated_by_feedback 拡張イベントで確認できます。
行モード メモリ許可フィードバックから始まり、IsMemoryGrantFeedbackAdjusted クエリ プラン XML 要素に追加される LastRequestedMemory と MemoryGrantInfo という、2 つの新しいクエリ プラン属性が実際の実行後プランに対して表示されます。
-
LastRequestedMemory属性により、前回のクエリ実行で許可されたメモリがキロバイト (KB) で表示されます。 -
IsMemoryGrantFeedbackAdjusted属性を使用すると、実際のクエリ実行プラン内のステートメントに対するメモリ許可フィードバックの状態を確認できます。
この属性に表示される値は次のとおりです。
IsMemoryGrantFeedbackAdjusted 値 |
説明 |
|---|---|
| いいえ: 最初の実行 | メモリ許可フィードバックは、最初のコンパイルとそれに関連付けられた実行ではメモリを調整しません。 |
| いいえ: 正確な許可 | ディスクへのスピルがなく、許可されたメモリの少なくとも 50% がステートメントによって使用されている場合、メモリ許可フィードバックはトリガーされません。 |
| いいえ: フィードバックは無効になっています | メモリ許可フィードバックが継続的にトリガーされ、メモリを増加させる操作と減少させる操作の間で変動している場合、データベース エンジンはそのステートメントのメモリ許可フィードバックを無効にします。 |
| はい: 調整中 | メモリ許可フィードバックが適用されています。また、次の実行に向けてさらに調整される可能性があります。 |
| はい: パーセンタイル調整 | メモリ許可フィードバックは、直近の実行結果だけでなく、より長期の履歴を参照する「パーセンタイル許可アルゴリズム」によって適用されます。 |
| はい: 安定 | メモリ許可フィードバックが適用されています。また、許可されたメモリが安定しています (つまり、前回の実行で許可されたメモリと今回の実行で許可されたメモリが同じです)。 |
パーセンタイルと永続化モードのメモリ許可フィードバック
適用対象: SQL Server 2022 (16.x) 以降のバージョン、Azure SQL データベース、および Azure SQL Managed Instance。
この機能は SQL Server 2022 (16.x) で導入されましたが、このパフォーマンスの向上は、データベース互換性レベル 140 (SQL Server 2017 (14.x) 以降で導入) または 140 以上の QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n ヒントで動作するクエリ、およびクエリ ストアがデータベースに対して有効になっており、"読み取り書き込み" 状態になっている場合に使用できます。
パーセンタイル メモリ許可フィードバックは、SQL Server 2022 (16.x) では既定で有効になっていますが、クエリ ストアが有効になっていない場合や、クエリ ストアが "読み取り書き込み" 状態でない場合は効果がありません。
SQL Server 2022 (16.x) では、メモリ許可、CE、DOP フィードバックの永続化は既定でオンになっていますが、クエリ ストアが有効になっていない場合や、クエリ ストアが "読み取り書き込み" 状態でない場合は影響しません。
Azure SQL Database では、パーセンタイルおよび永続化モードのメモリ許可フィードバックがすべてのデータベース (新旧問わず) で使用でき、既定で有効になっています。
メモリ許可フィードバックのパーセンタイルと永続化は、現在、Azure SQL Managed Instance では使用できません。
データベースでこの機能を有効にする前に、ワークロード用のパフォーマンス ベースラインを用意することをお勧めします。 ベースラインの数値は、この機能から意図した利点を得られているかどうかを判断するのに役立ちます。
メモリ許可フィードバック (MGF) は、過去のパフォーマンスに基づいてクエリに割り当てられるメモリのサイズを調整する既存の機能です。 ただし、このプロジェクトの初期フェーズでは、プランとのメモリ許可調整のみがキャッシュに保存されました。プランがキャッシュから削除された場合は、フィードバック プロセスを再度開始する必要があり、削除後にクエリが最初に数回実行される際はパフォーマンスが低下します。 新しいアプローチとして、クエリ情報と共に許可情報もクエリ ストアに永続化することで、キャッシュの消去があってもメリットが持続するようになっています。 メモリ許可フィードバックの永続化とパーセンタイルは、メモリ許可フィードバックの既存の制限に悪影響を与えない方法で対処します。
さらに、許可サイズの調整は、最後に使用された許可に対してのみ考慮されました。 そのため、パラメーター化クエリまたはワークロードで実行ごとに必要なメモリ許可サイズが大きく変動する場合、直近の実行から得られたメモリ許可情報は正確でない可能性があります。 その情報は、現在実行中のクエリに実際に必要なメモリ量と大幅にずれている可能性があります。 このような状況では、シナリオのメモリ許可フィードバックは直前の実行結果のみに基づいて調整されたメモリを示すため、パフォーマンス改善に役立ちません。 次の図は、パーセンタイルおよび永続化モードを使用しない場合のメモリ許可フィードバックの動作を示しています。
このように、クエリ実行ごとに必要なメモリ量が交互に変動するようなケースでは、実際の必要量と割り当て量がずれてしまい、メモリの無駄や不足が発生します。 このような状況では、メモリ許可フィードバックは自身の調整のためメリットよりもデメリットの方が大きくなると判断し、自動的に無効になります。
直近の実行結果だけでなく、過去の実行履歴に基づいてパーセンタイルで計算することで、メモリ割り当てを平準化し、スピルの最小化を図ることができます。 たとえば、同じ変動するワークロードでも、次のようなメモリ許可の動作が得られます。
クエリ オプティマイザーは、キャッシュされた実行プランでの過去のクエリ実行におけるメモリ許可サイズ設定要件のうち、高いパーセンタイル値を使用して、メモリ許可サイズを算出します。 許可されるメモリを調整するパーセンタイルの補正は直近の実行履歴に基づいて行われます。 時間の経過とともに、許可されるメモリが適切に調整されて、スピルやメモリの無駄が減っていきます。
永続化は DOP フィードバックと CE フィードバックにも適用されます。
メモリ許可フィードバック機能の有効化と無効化
互換性レベルを変更せず、行モード メモリ許可フィードバックを無効にする
行モード メモリ許可フィードバックは、データベースの互換性レベル 150 以上を維持しながら、データベースまたはステートメント範囲で無効にできます。 データベースから送信されるすべてのクエリ実行に対して行モードメモリ許可フィードバックを無効にするには、該当するデータベースのコンテキスト内で Transact-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 a higher value
この機能の永続化部分が使用されるすべてのデータベースで、クエリ ストアを有効にする必要があります。
パーセンタイルを無効にする
データベースを発生源とするすべてのクエリ実行に対してメモリ許可フィードバック パーセンタイルを無効にするには、該当するデータベースをアクティブにした状態で以下を実行します。
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 をクエリすれば、現在の設定を確認できます。
注
BATCH_MODE_MEMORY_GRANT_FEEDBACK と ROW_MODE_MEMORY_GRANT_FEEDBACK が両方とも OFF に設定されている場合、この機能は動作しません。
フィードバック データがクエリ ストアに永続化されるようになったため、クエリ ストアの使用量の要件が若干増加しています。
パーセンタイルベースのメモリ許可は、スピルを減らしすぎて失敗する傾向があります。 最後の実行だけでなく、過去のいくつかの実行の観察に基づくようになったため、変動するワークロードのメモリ使用量が増加し、実行間のメモリ許可要件の違いが大きくなる可能性があります。
SQL Server 2022 (16.x) 以降では、セカンダリ レプリカに対してクエリ ストアが有効になっている場合、メモリ許可フィードバックは可用性グループ内のセカンダリ レプリカにも対応します。 メモリ許可フィードバックは、プライマリ レプリカとセカンダリ レプリカで異なるフィードバックを適用できます。 ただし、メモリ許可フィードバックはセカンダリ レプリカでは保持されず、フェールオーバー時には、古いプライマリ レプリカからのメモリ許可フィードバックが新しいプライマリ レプリカに適用されます。 セカンダリ レプリカがプライマリ レプリカに昇格した場合、それまでのフィードバックは失われます。 クエリ ストアは、SQL Server 2025 (17.x) 以降のセカンダリ可用性グループ レプリカで使用できます。 詳細については、「 読み取り可能なセカンダリのクエリ ストア」を参照してください。