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

適用対象: SQL Server 2017 (14.x) 以降のバージョン、Azure SQL Managed Instance、Azure SQL Database

クエリが実行される際に、許可されるメモリが大きすぎたり小さすぎたりすることがあります。 許可されるメモリが大きすぎる場合、サーバー上での並列処理が抑制されます。 逆に小さすぎると、ディスクへのスピルが発生し、これには高いコストがかかります。 メモリ許可フィードバックでは、前回実行時 (パーセンタイル フィードバックの場合は複数回分) に必要だったメモリ量が記録されます。 その履歴データに基づいて、次回以降の実行でクエリに許可されるメモリが自動的に調整されます。

この機能は 3 段階でリリースされました。 まず Batch モードのメモリ許可フィードバック、次に行モードのメモリ許可フィードバックが導入されました。そして SQL Server 2022 (16.x) では、クエリ ストアによるディスク上の永続化と、パーセンタイル許可と呼ばれる改良アルゴリズムを採用した、メモリ許可フィードバックが導入されました。

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

対象: SQL Server (SQL Server 2017 (14.x) 以降)、Azure SQL Database、Azure SQL Managed Instance (データベース互換性レベル 140 以上)

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

次のグラフでは、Batch モード アダプティブ メモリ許可フィードバックを使用する 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 未満のプランについては、超過分の再計算は行われません。

メモリ許可条件が過少な場合、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 以上を維持しながら、データベースまたはステートメント範囲で無効にできます。 データベースを発生源とするすべてのクエリ実行に対して Batch モード メモリ許可フィードバックを無効にするには、該当するデータベースをアクティブにした状態で次の 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 で有効として表示されます。

データベースを発生源とするすべてのクエリ実行に対して Batch モード メモリ許可フィードバックを再有効化するには、該当するデータベースをアクティブにした状態で次の 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 クエリ ヒントとして指定することで、特定のクエリの Batch モード メモリ許可フィードバックを無効にすることもできます。 たとえば、次が挙げられます。

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 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 拡張イベントで確認できます。

行モード メモリ許可フィードバックから始まり、MemoryGrantInfo クエリ プラン XML 要素に追加される IsMemoryGrantFeedbackAdjusted と LastRequestedMemory という、2 つの新しいクエリ プラン属性が実際の実行後プランに対して表示されます。

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

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

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

適用対象: SQL Server (SQL Server 2022 (16.x) 以降)、Azure SQL Database、Azure SQL Managed Instance

この機能は SQL Server 2022 (16.x) で導入されましたが、SQL Server 2017 以降のデータベース互換性レベル 140 以上、または QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n ヒントが 140 以上で、かつクエリ ストアが「読み書き可能」状態で有効になっていれば使用できます。

• SQL Server 2022 (16.x) では、パーセンタイル メモリ許可フィードバックが既定で有効になっていますが、クエリ ストアが有効でないか「読み書き可能」状態でない場合、効果はありません。
• メモリ許可、CE、DOP の各種フィードバックの永続化は SQL Server 2022 (16.x) では既定で有効になっていますが、クエリ ストアが有効でないか「読み書き可能」状態でない場合、効果はありません。
• 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 をクエリすれば、現在の設定を確認できます。

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

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

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

関連するコンテンツ

• SQL データベースでのインテリジェント クエリ処理
• インテリジェント クエリ処理機能の詳細
• RECONFIGURE (Transact-SQL)
• パフォーマンスの監視とチューニング
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)