Skip to content

MS_SQLServerAdHocQueryMonitoring

Jump to bottom
nishi_74322014 edited this page Aug 18, 2026 · 2 revisions

SQL Server アドホック クエリ問題の監視

概要

アドホック クエリ

  • 「実行プラン」が再利用されず、SQL がコンパイルされる。
  • このため、多数のユーザが短いアドホック クエリを同時実行すると、
    CPU の使用率が非常に高くなり、システム全体の性能が低下することがある。

「実行プラン」の再利用

  • まったく同じクエリのときのみ「実行プラン」が再利用される。
  • 検索キーの値を変更しただけで、「実行プラン」の再利用がされなくなる。
  • これは、クエリをパラメタ化することで防ぐことができる。

補足（「まったく同じ」の厳密さ）: プラン キャッシュのキーは
クエリ テキストのハッシュであり、
空白・改行・大文字小文字・コメントの違いでも別物として扱われる。
ORM が生成する SQL やアプリ側の文字列連結で
わずかに揺らぐだけでキャッシュが増殖する。

また、キャッシュされたプランは SET オプションの組み合わせごとにも
別エントリになる。SSMS とアプリで ARITHABORT の既定値が異なるため、
「SSMS では速いのにアプリだと遅い」現象が起きる
（SQL Server のコネクションとセッション参照）。

補足（パラメータ化の 2 つの意味）: 「クエリのパラメタ化」には
2 つのレイヤがあり、混同しやすい。

レイヤ 内容
アプリ側 SqlParameter を使う（sp_executesql として送られる）。SQL インジェクション対策としても必須
サーバ側 単純パラメータ化（既定）／強制パラメータ化（ALTER DATABASE ... SET PARAMETERIZATION FORCED）でリテラルを自動的にパラメータに置き換える

本来はアプリ側で解決すべきで、
強制パラメータ化はソースを変更できない場合の最後の手段。
パラメータ スニッフィングによる別の性能問題を招くこともある。

監視方法

パフォーマンス カウンタ

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

  • SQL Server:Buffer Manager:Free pages
  • SQL Server: SQL Statistics:SQL Compilations/sec

のカウンタ値が高く、

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

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

移行メモ（正誤）: Memory Grants Pending は
Buffer Manager ではなく Memory Manager オブジェクトのカウンタである
（SQLServer:Memory Manager\Memory Grants Pending）。
Free pages も現行バージョンでは Buffer Manager から
Buffer Node 系へ整理されており、名称が変わっている。

補足（現在見るべき指標）: カウンタよりも DMV のほうが
直接的に原因を特定できる。

-- プラン キャッシュの内訳（アドホックが占有していないか）
SELECT objtype,
       COUNT(*)                         AS plan_count,
       SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS mb,
       SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use_count
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY mb DESC;

objtype = 'Adhoc' かつ usecounts = 1 のプランが
キャッシュの大半を占めていれば、本ページの症状に該当する。

併せて確認したい比率は以下。

カウンタ 見方
SQL Compilations/sec ÷ Batch Requests/sec 10% を超えるとコンパイル過多
SQL Re-Compilations/sec 再コンパイルが多い場合は統計更新やスキーマ変更を疑う
Plan Cache Hit Ratio 低ければキャッシュが効いていない

SQLプロファイラ（SQLトレース）

必要に応じて、SQLプロファイラ（SQLトレース）を使用する。

実行プランのグラフィカル表示

問題のクエリが特定できたら、SSMSを使用して、
クエリプラン（実行プラン）のグラフィカル表示も可能
（実行プランのグラフィカル表示）。

対策方法

この状態では、CPU ボトルネックが発生する可能性がある。

この場合、SQL のコンパイルのコストを避けるために、
クエリのパラメタ化による「実行プラン」の再使用を検討する。

補足（optimize for ad hoc workloads）: アプリを直せない場合の
即効性のある緩和策として、サーバ構成オプション
optimize for ad hoc workloads を 1 にする方法がある。

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

これを有効にすると、初回実行時はプラン本体ではなくスタブだけを
キャッシュし、2 回目に実行されたときに初めて完全なプランを保存する。
1 回しか実行されないクエリでプラン キャッシュが圧迫されるのを防げる
（SQL Server の基本的な設定参照）。
副作用がほぼ無いため、既定で有効にしておく構成が一般的。

参考

アドホック クエリの性能問題の監視、対策方法については、以下の URL を参照のこと。

SQL Server のオプティマイザ

SQL Server のオプティマイザ

オプティマイザが実行プランを決定する。

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

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

NetDevInfraWiki

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

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally