SQL Serverの実行計画を可視化！プランキャッシュの確認と分析方法を徹底解説
SQL Serverでは、実行済みクエリの「実行計画」をメモリ上にキャッシュして再利用する仕組みがあります。
このキャッシュを分析することで、再利用効率の確認や不要プランの特定が可能です。
この記事では、sys.dm_exec_cached_plans を中心に、
プランキャッシュの確認・分析・管理方法を実務視点でわかりやすく解説します。
目次
プランキャッシュとは？
クエリ最適化結果を保持するメモリ領域
プランキャッシュ（Plan Cache）とは、SQL Serverがクエリ最適化で生成した「実行計画」を保存し、再利用するためのメモリ領域です。 同じSQL構文を再実行する際、最適化をスキップして実行できるため、CPU負荷を軽減します。
キャッシュされる対象はストアドプロシージャ、パラメータ化クエリ、Ad-hocクエリなど多岐にわたります。
プランキャッシュを確認する主なビュー
代表的なDMV（動的管理ビュー）
| ビュー名 | 主な用途 |
|---|---|
| sys.dm_exec_cached_plans | キャッシュされたプラン情報を確認 |
| sys.dm_exec_query_stats | クエリの実行統計（実行回数・CPU時間など） |
| sys.dm_exec_sql_text | 実際のSQLテキストを取得 |
| sys.dm_exec_query_plan | XML形式で実行計画を表示 |
これらを組み合わせることで、実行計画の再利用状況やパフォーマンスを分析できます。
sys.dm_exec_cached_plans の基本構文と利用例
基本構文
SELECT *
FROM sys.dm_exec_cached_plans;
単体では plan_handle（プラン識別子）などの情報が取得できますが、 実務では他ビューと組み合わせて詳細を確認します。
実務でよく使うサンプル
SELECT TOP 20
DB_NAME(st.dbid) AS DatabaseName,
cp.objtype,
cp.usecounts AS ReuseCount,
st.text AS SQLText,
qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
ORDER BY cp.usecounts DESC;
これにより、再利用回数が多い（効率的な）実行計画や、逆に再利用されていないクエリを特定できます。
キャッシュ再利用回数やSQL内容の分析
再利用回数の確認
usecountsが1の場合：再利用されていない → 動的SQLの可能性usecountsが多い場合：キャッシュ効率が良い
再利用効率の向上ポイント
- クエリのパラメータ化を徹底する
- 同一構文・同一SETオプションで実行
- ストアドプロシージャを活用
不要キャッシュの特定
SELECT objtype, COUNT(*) AS Count, SUM(size_in_bytes)/1024/1024 AS SizeMB
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY SizeMB DESC;
キャッシュサイズが大きい項目（特にAdhoc）は、メモリ圧迫の原因になることがあります。
不要プランの削除とキャッシュメンテナンス
特定のプランを削除
不要な実行計画だけをピンポイントで削除する場合は、plan_handleを指定します。
DBCC FREEPROCCACHE (0x06000800B582FE21E8F1C3A8010000000000000000000000);
※ plan_handleは sys.dm_exec_cached_plans から取得可能。
キャッシュ全体のクリア（注意）
DBCC FREEPROCCACHE; -- 全キャッシュ削除（推奨されない）
全削除すると再最適化が発生し、一時的にCPU負荷が増加するため、本番では慎重に実施します。
実務での活用ポイントと注意事項
- 定期的にキャッシュ状況を確認し、Adhocクエリの比率を把握する
- キャッシュ肥大化が発生した場合は原因SQLを特定
- プランキャッシュの再利用率をモニタリング（usecounts活用）
- パラメータスニッフィングの影響を考慮して設計
キャッシュサイズの確認
SELECT name, SUM(pages_kb)/1024 AS SizeMB
FROM sys.dm_os_memory_clerks
WHERE name LIKE '%CACHE%'
GROUP BY name
ORDER BY SizeMB DESC;
この結果で「SQL Plans」項目がプランキャッシュのメモリ使用量です。
まとめと次のステップ
学んだ内容の整理
- プランキャッシュは実行計画の再利用を行うメモリ領域。
- sys.dm_exec_cached_plans で再利用状況や容量を確認できる。
- usecountsやobjtypeで効率を分析し、不要キャッシュを削除。
- 本番では全キャッシュ削除は避け、限定的に操作する。
SQL Server学習シリーズ
参考リンク
0 件のコメント:
コメントを投稿