ApsaraDB RDS:RDS SQL Server におけるメモリプレッシャーの診断と最適化

操作手順

1. RDS インスタンスリストに移動し、上部でリージョンを選択してから、対象のインスタンス ID をクリックします。

2. 左側のナビゲーションウィンドウで、[モニタリングとアラート] をクリックします。

3. メモリ関連のメトリックである mem_usage、Page_life_expectancy、bufferpool_hit_ratio を表示します。

メモリプレッシャーの初期確認

RDS SQL Server では、mem_usage (メモリ使用量) が 90% を超えるのは通常、正常な状態です。SQL Server は、データキャッシュなどのタスクのために、利用可能なメモリをできるだけ多く使用してパフォーマンスを向上させます。このメトリックには、SQL Server プロセス、オペレーティングシステム、および管理サービスによって使用されるメモリも含まれます。

ただし、Linked Server、CLR、In-Memory OLTP、または多数の XEvents や Traces など、メモリオーバーヘッドの高い機能が有効になっているインスタンスでは、実際のメモリ消費量が構成された max server memory の値を大幅に超える可能性があります。これにより、全体的なメモリプレッシャーが増加し、パフォーマンス変動のリスクが高まります。この状況では、max server memory の値を下げて、システムのために十分なメモリを確保し、安定性を確保する必要があります。

mem_usage メトリックの推奨上限値：

• 512 GB メモリインスタンス：97%

• 256 GB インスタンスのメモリ使用率：96%

• 192 GB 以下のインスタンスタイプ：95%

操作手順

1. RDS インスタンスリストに移動し、上部でリージョンを選択してから、対象のインスタンス ID をクリックします。

2. 左側のナビゲーションウィンドウで、[Autonomy Service] > [パフォーマンス最適化] を選択し、[Performance Insight] タブを開きます。

3. ページの右上隅にある [カスタムメトリック] をクリックします。リストから目的のメモリ関連のパフォーマンスメトリックを選択し、[確認] をクリックします。一般的なメモリメトリックは、[メモリ使用量の分類] と [AdvancedMemUsage] (高度なメモリ使用量の分類) の 2 つのグループに整理されています。

メモリ使用量の分類メトリック

[メモリ使用量の分類] のメトリックは、一般的に SQL Server Memory Manager オブジェクトのパフォーマンスカウンターに対応しています。

• Total_Server_Memory_Kb：このメトリックを使用して、メモリが上限に達しているかどうかを大まかに判断します。

• Database_Cache_Memory_Kb と Stolen_Server_Memory_Kb：これらのメトリックを使用して、データキャッシュと他の用途に割り当てられたメモリの比率を判断します。

• Free_Memory_Kb：このメトリックを使用して、短期的に利用可能なメモリを観察します。

ただし、単一のメモリ使用量メトリックの値が高いか低いかだけでは、それが良いか悪いかを判断するには不十分です。パフォーマンスの傾向とワークロードも考慮する必要があります。

AdvancedMemUsage メトリック (Stolen メモリの内訳)

Stolen メモリの使用量をさらに診断するために、RDS は [AdvancedMemUsage] 詳細メトリックを提供します。これは、Stolen メモリを異なるメモリ割り当てカテゴリに分類します。これらのメトリックは、SQL Server の特定の内部メモリコンポーネントに対応し、メモリ使用量の分類の詳細な内訳を提供します。

`AdvancedMemUsage` メトリックを分析することで、どのコンポーネントがメモリを消費しているかを判断し、適切な対策を講じることができます。例：

• `CACHESTORE_SQLCP` や `OBJCP` などのプランキャッシュ関連のメトリックが高い場合は、実行計画の再利用とパラメーター化の最適化に重点を置きます。

• `MEMORYCLERK_SQLCLR` が異常な場合は、CLR モジュールを確認します。

• `MemoryClerk_SOSNODE` が長期間にわたって上昇する場合は、SQL Server エンジンの問題を示します。再起動などの運用措置を検討してください。

異常な動作

インスタンスが以下の特徴を示す場合、通常はデータページが長時間メモリに留まることができず、バッファープールにプレッシャーがかかっていることを意味します。

• I/O メトリックの急増： Performance Insight で `Page_Reads` (物理読み取り) が大幅に増加し、ディスク I/O スループットが増加します。

• 短いライフサイクル： ページ生存期間 (PLE) が一貫して健全なしきい値を下回っています。

PLE は、データページがバッファープールに留まる平均時間 (秒) です。従来の「300 秒」というしきい値は、大容量メモリのインスタンスにはもはや適用できません。メモリ仕様に基づいてしきい値を動的に計算します。一般的な数式は次のとおりです。

PLE = (バッファープールメモリ (GB) / 4 GB) × 300

PLE 計算例：

• 16 GB インスタンスタイプ (バッファープール約 12 GB)：妥当な PLE は 900 秒 (12 / 4 × 300) より大きい必要があります。

• 128 GB インスタンスタイプ (バッファープール約 110 GB)：妥当な PLE は 8250 秒 (110 / 4 × 300) より大きい必要があります。

診断アプローチ

• 相関関係の確認： PLE の低下が `Page_Reads` の同時増加を伴うかどうかを確認します。それらが強く相関し、ビジネスのピーク時に発生する場合、キャッシュ不足と診断できます。

• 干渉の除外： PLE が低下しても I/O が大幅に増加しない場合、またはバックアップ、インデックスの再構築、CheckPoint 中に発生する場合は、通常は正常な変動です。短期的な低下は過度に注意する必要はありません。

• 容量の評価： `Database_Cache_Memory_Kb` をアクティブなデータセットのサイズと比較します。キャッシュがいっぱいでも、ホットデータの非常に小さな部分しか保持できない場合は、容量のボトルネックを示します。

最適化ソリューション

• メモリのスケールアップ： ワーキングセットが現在のメモリ仕様よりもはるかに大きいことを確認した場合、インスタンスタイプをアップグレードすることが、I/O レイテンシを削減する最も直接的な方法です。

• クエリの最適化： フルテーブルスキャンや大規模な集約がないか確認します。存在する場合は、インデックスを追加してスキャンをシークに変更します。これにより、大規模なクエリがバッファープールからすべてのホットデータをフラッシュするのを防ぎます。

• ワーキングセットの削減：

  • アーカイブ： 既存のコールドデータをアーカイブテーブルに移行します。

  • 圧縮： テーブルまたはインデックスのデータ圧縮を有効にして、同じ量のメモリにより多くのデータページを収容します。

  • メンテナンス： 断片化が激しいインデックスを再構築してページ密度を向上させます。未使用のインデックスを削除してメンテナンスのオーバーヘッドを削減します。

異常な動作

インスタンス内の Stolen メモリの割合が異常に増加し、エンジンの上限に達し、バッファープールのスペースを圧迫し始めると、通常は実行計画が効果的に再利用されていないことを意味します。

• メモリ特性： `Stolen_Server_Memory_Kb` が継続的に増加し、高いレベルで推移し、プランキャッシュの内部クォータ上限に近づきます。これにより、`Database_Cache_Memory_Kb` が十分なメモリを受け取れなくなり、PLE が低下します。

• パフォーマンス特性： キャッシュが頻繁にクリアされ、新しいプランが再コンパイルされるため、CPU 使用率の増加を伴います。また、データページキャッシュのスペースが減少するため、ディスク I/O の増加も伴います。

• 典型的なトリガーシナリオ： アプリケーションがパラメーター化されていない多くのアドホック SQL 文を送信するか、ORM フレームワークが大量の単一使用実行計画を生成します。これにより、キャッシュが価値の低いプランで埋め尽くされます。

診断アプローチ

• 相関関係の確認： Stolen メモリが過去のベースラインよりも大幅に高く、PLE の低下と負の相関関係を示しているかどうかを観察します。

• 単一使用プランの割合: usecounts = 1 のプランの数とそのメモリ使用量です。単一使用プランがプランキャッシュの非常に高い割合 (たとえば、50% を超える) を占める場合、大量のメモリが浪費されていることを示します。

  診断用の SQL コマンド

  WITH PlanStats AS
  (
      SELECT
          cp.usecounts,
          cp.size_in_bytes / 1024.0 / 1024.0 AS size_mb
      FROM sys.dm_exec_cached_plans AS cp
      WHERE cp.cacheobjtype = 'Compiled Plan'
  )
  SELECT
      total_plans           = COUNT(*),
      single_use_plans      = SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END),
      single_use_ratio_pct  = 
          CAST(
              100.0 * SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0)
              AS DECIMAL(5,2)
          ),
      total_plan_mb         = CAST(SUM(size_mb) AS DECIMAL(18,2)),
      single_use_plan_mb    = CAST(SUM(CASE WHEN usecounts = 1 THEN size_mb ELSE 0 END) AS DECIMAL(18,2)),
      single_use_mem_pct    = 
          CAST(
              100.0 * SUM(CASE WHEN usecounts = 1 THEN size_mb ELSE 0 END) 
              / NULLIF(SUM(size_mb), 0)
              AS DECIMAL(5,2)
          )
  FROM PlanStats;
  

• ソースの特定： `sys.dm_exec_sql_text` を使用して単一使用プランの SQL テキストをサンプリングします。同じ構造でリテラル値が異なる多くの SQL 文が見つかった場合、原因がアプリケーションのパラメーター化不使用であることを確認できます。次の SQL 文は、最も多くの実行計画キャッシュスペースを使用する上位 20 の単一使用キャッシュを示しています。

  キャッシュを表示する SQL コマンド

  -- 最も多くのメモリを消費するアドホッククエリとそのテキストの上位 20 件を表示
  SELECT TOP 20
      cp.usecounts AS [Execution_Count],
      cp.size_in_bytes / 1024 AS [Plan_Size_KB],
      cp.objtype AS [Object_Type],
      st.text AS [SQL_Text] 
  FROM sys.dm_exec_cached_plans AS cp
  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
  WHERE cp.objtype = 'Adhoc' 
    AND cp.usecounts = 1 
  ORDER BY cp.size_in_bytes DESC;

最適化ソリューション

• Optimize for Ad hoc Workloads オプションの有効化

  • 手順： RDS インスタンスリストページに移動し、インスタンスの詳細ページに移動します。左側のナビゲーションウィンドウで、[パラメーター設定] をクリックします。optimize for ad hoc workloads パラメーターを検索し、有効にします。

  • 原則： このパラメーターを有効にすると、アドホック SQL 文の最初の実行では軽量なスタブのみがキャッシュされます。完全なプランは 2 回目の実行でのみ保存されます。

  • 効果： これにより、単一使用クエリの Stolen メモリ使用量が大幅に削減され、メモリプレッシャーが緩和されます。このオプションは CPU のコンパイルオーバーヘッドを削減するものではなく、メモリ使用量のみを最適化します。

• アプリケーション側の最適化

  • パラメーター化クエリ： 文字列連結の代わりにパラメーター化クエリを使用するようにコードロジックを変更します。

  • ORM の構成： ORM フレームワークで自動パラメーター化またはセカンドレベルキャッシュが有効になっているかどうかを確認します。

• 強制パラメーター化の有効化

  アプリケーションコードを変更できない場合は、RDS コンソールでデータベースレベルの強制パラメーター化を有効にできます。

  • RDS インスタンスリストに移動し、インスタンスの詳細ページを開き、左側のナビゲーションウィンドウで [データベース管理] をクリックします。

  • 強制パラメーター化が有効になっているデータベースの場合、右側の [詳細の表示] をクリックします。

  • [基本情報] セクションで、parameterization パラメーターを FORCED に設定し、[送信] をクリックします。

    説明

    このパラメーターを有効にした後、プランの変更によって一部の複雑なクエリでパフォーマンスリグレッションが発生する可能性があるため、ステージング環境で検証してください。

異常な動作

バッファープールのプレッシャーとは異なり、このタイプのプレッシャーは必ずしも PLE の低下を引き起こしません。クエリにソートまたはハッシュ操作が含まれる場合、十分なワークスペースメモリを取得できないと、2 つの典型的な症状が現れます。

• キューイングと待機 (Wait)

  • 動作： クエリは実行を開始せず、SUSPENDED 状態のままになります。

  • メトリック： 待機タイプは RESOURCE_SEMAPHORE (非常に深刻で、メモリが枯渇しクエリがキューイングしていることを示す) または RESOURCE_SEMAPHORE_QUERY_COMPILE です。`Memory Grants Pending` パフォーマンスカウンターの値が急増します。

• ディスクへのスピル (Spill)

  • 動作： クエリは実行中ですが、非常に遅いです。

  • メトリック： TempDB への I/O 書き込みが大幅に増加します。

  • 実行計画： Sort または Hash Match オペレーターに「Operator used tempdb to spill data...」というメッセージとともに黄色の警告アイコンが表示されます。これは、メモリが不足し、SQL Server が計算のために中間結果をディスクに書き込むことを余儀なくされたことを示します。

診断アプローチ

リアルタイムまたは履歴データをクエリすることで、メモリ許可の消費が最も高い SQL 文を分析できます。結果の実行計画 (`query_plan`) で、Spill キーワードを検索するか、Sort または `Hash` オペレーターに警告アイコンがあるかどうかを確認します。

• 現在、リアルタイムで最も多くのメモリ許可を消費しているステートメントを見つけます。

  リアルタイムクエリの分析

  -- リアルタイムのメモリ許可を表示
  SELECT
      mg.session_id,
      mg.request_time,
      mg.grant_time, -- NULL の場合、クエリはメモリを待機中 (RESOURCE_SEMAPHORE)
      (mg.requested_memory_kb / 1024.0) AS requested_mb,
      (mg.granted_memory_kb / 1024.0) AS granted_mb,
      (mg.required_memory_kb / 1024.0) AS required_mb,
      mg.queue_id,
      mg.wait_order,
      st.text AS sql_text,
      qp.query_plan
  FROM sys.dm_exec_query_memory_grants AS mg
  CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
  CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
  ORDER BY mg.granted_memory_kb DESC;
  

• キャッシュ内の履歴実行統計を分析して、履歴的に最も多くのメモリを要求したクエリを見つけます。

  履歴クエリの分析

  SELECT TOP 20
      qs.execution_count,
      (qs.max_grant_kb / 1024.0) AS max_grant_mb, -- 最大の単一実行で要求されたメモリ
      (qs.total_grant_kb / qs.execution_count / 1024.0) AS avg_grant_mb,
      (qs.total_worker_time / qs.execution_count / 1000.0) AS avg_cpu_ms,
      qs.last_execution_time,
      SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
          ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
            END - qs.statement_start_offset)/2) + 1) AS query_text,
      qp.query_plan
  FROM sys.dm_exec_query_stats AS qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
  ORDER BY qs.max_grant_kb DESC;
  

主な原因と最適化ソリューション

• 古い統計 (最も一般的な原因)

  • 原因： 古い統計により、オプティマイザーが結果セットの行数を著しく過小評価します。例えば、実際には 100 万行を処理しているのに 1 行と見積もるかもしれません。これに基づき、オプティマイザーは 1 MB などの非常に少量のメモリを要求します。これにより、メモリが不足するため、実行中に頻繁にディスクへのスピルが発生します。

  • ソリューション： データベースの統計を定期的に、またはオフピーク時に更新して、オプティマイザーがメモリ要件を正確に見積もれるようにします。

• 大規模クエリによる同時競合

  • 原因： フルテーブルソートや大規模なハッシュ集計を必要とする複数の重いレポートクエリが同時に実行されます。これによりメモリが枯渇し、後続のクエリが待機キューに入ることがあります。

  • ソリューション： 実行をずらす戦略を実装します。メモリを大量に消費するレポートタスクを夜間に順次実行するようにスケジュールし、ピーク時のリソース競合を回避します。

• コードまたはインデックス設計の問題

  • メモリ消費の多いオペレーター： クエリに複雑な JOIN、GROUP BY、ORDER BY、または DISTINCT 操作が含まれているが、適切なインデックスがありません。これにより、SQL Server はメモリ内に巨大なハッシュテーブルを構築したり、中間ソートを実行したりすることを余儀なくされ、メモリ需要が急増します。

  • 過剰なデータ幅： SELECT * を使用したり、不要な長いテキスト列を選択したりすると、単一のデータ行が「肥大化」します。メモリ許可サイズ = 推定行数 × 平均行幅 であるため、列が広いほど、必要な合計メモリも多くなります。

  • ソリューション：

    • ソート済みインデックスの作成： カバーリングインデックスまたは順序付きインデックスを作成して、オプティマイザーがメモリ消費の少ない物理操作を選択できるようにします。

    • アプリケーション側の最適化： 「必要十分」の原則に厳密に従います。必要な列のみを選択して、データ行の幅を減らします。多くの結合を持つ大規模なクエリの場合は、複数のより単純なステップに分割してみてください。