Skip to content

MS_SQLServerJoinMethodMonitoring

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server 結合方式の問題を監視する

概要

SQL Server では、結合方法を

  • ネスト化ループ結合
  • マージ結合
  • ハッシュ結合

から選択できる。

詳細

アルゴリズム

三つのアルゴリズムは、一般的に

ネスト・ループ結合 < マージ結合 < ハッシュ結合

の順で高速（ハッシュ結合が最速）。

ただし、以下のケースでは、必ずしもこの順番にならない。

  • 二つのテーブルのレコードの数が極端に違う場合
  • 両方のレコードの数が十分小さいとき
  • 検索条件に合致した 1 レコードを早く返したい

補足（速さは件数で逆転する）: 上の順序は
大量データ同士を結合する場合の一般論であり、
実際にはデータ量によって最適な方式が入れ替わる。
「ハッシュ結合が最速」と覚えるのではなく、
オプティマイザが件数の見積りから選び分けていると理解するのが正しい。

方式 得意な状況 計算量の目安 事前処理
ネスト ループ 外側が少数 + 内側にインデックス O(N × logM) 不要
マージ 両方が結合キー順に整列済み O(N + M) ソートが必要な場合あり
ハッシュ 大量 × 大量、インデックスなし O(N + M) ハッシュ テーブル構築（メモリ）

このため、実行プランで「不適切な結合方式が選ばれている」場合、
真因は結合方式ではなく件数の見積り誤りであることがほとんどである。
統計情報の鮮度を先に確認すること
（SQL Server のオプティマイザ）。

ネスト化ループ結合

  • 単純に二重ループを回してテーブルを結合する方法。
  • インデックスが設定されていない小さなテーブルと
    インデックスが設定されている大きなテーブルの二つを結合する場合に効果的。

マージ結合

  • ネスト・ループ結合の改良版。

  • 二つのテーブルの結合フィールドをあらかじめソート。

  • ソート結果の結合フィールドのポインタを上から下へと順に走査。

  • これによって、レコードの走査が 1 回で済む。

ハッシュ結合

  • ネスト・ループ結合の改良版。

  • ハッシュ結合は高速だが、

    • ハッシュ テーブルを作成するため、
    • 元のテーブルのサイズが大きい場合、
      ハッシュ テーブルがメモリを大量に消費することがある。

補足（ハッシュのスピル）: ハッシュ テーブルが
クエリに割り当てられたメモリ グラントに収まらない場合、
tempdb にあふれる（spill）。
実行プランでは Hash Match 演算子に警告アイコンが付き、
「Hash Warning」として現れる。
この状態になると、メモリ内処理を前提にした速度は出ない
（SQL Server のファイルの配置の tempdb の項も参照）。

スピルが起きる主因も、やはり件数の見積り誤りである。

監視方法

この場合、パフォーマンス カウンタを使用して監視を行う。

この場合、

  • SQL Server:Buffer Manager:Free pages

のカウンタ値が低く、

  • SQL Server:Buffer Manager:Stolen Page Count
  • SQL Server:Buffer Manager:Memory Grants Pending

のカウンタ値が高いという状態になる。

移行メモ（正誤）: Memory Grants Pending は
Memory Manager オブジェクトのカウンタである
（SQLServer:Memory Manager\Memory Grants Pending）。
SQL Server アドホック クエリ問題の監視と
ちょうど逆の兆候を見ている点に注目すると、両ページの対比が理解しやすい。

補足（現在の調べ方）: メモリ グラント待ちは DMV で直接見える。

-- メモリ グラントを待っている／確保している要求
SELECT session_id, request_time, grant_time,
       requested_memory_kb, granted_memory_kb, used_memory_kb,
       wait_order, is_next_candidate, queue_id, dop
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

grant_time が NULL の行はメモリ待ち（待ち事象 RESOURCE_SEMAPHORE）。
また、requested_memory_kb に対して used_memory_kb が
極端に小さい場合は過大なメモリ グラントで、
他のクエリを待たせている。
SQL Server 2017 以降のバッチ モード メモリ許可フィードバック、
2019 以降の行モード メモリ許可フィードバックは、
この過大／過小を実行結果から自動補正する機能である。

対策方法

この状態では、SQL Server のメモリ不足が発生する可能性がある。

この場合、

  • 「ネスト化ループ結合」に変更できる
  • 「ハッシュ結合」を使用している

クエリを探すことに重点を置く。

結合方法を見分けるには、実行プランを確認する
（実行プランのグラフィカル表示）。

補足（結合ヒントは最後の手段）: OPTION (LOOP JOIN) や
INNER LOOP JOIN といったヒントで結合方式を固定できるが、
データ量が変われば最適解も変わるため、恒久的な指定は避ける。
対処の優先順位は以下。

  1. 統計情報を更新して見積りを正す
  2. 結合キーにインデックスを作る（ネスト ループが選べるようになる）
  3. 不要な行を先に絞る（WHERE の見直し、中間結果の削減）
  4. パラメータ スニッフィングが原因なら OPTIMIZE FOR / RECOMPILE
  5. どうしても直らない場合にヒント、またはクエリ ストアでプラン強制

なお、ヒントを付けるとそのヒントを満たすプランしか作れなくなり、
場合によってはクエリ自体が失敗する（FORCE ORDER との併用時など）。

参考

詳細は、以下の URL を参照のこと。

Tags: 移行, データアクセス, SQL Server, 障害対応, 性能

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally