実行効率を大きく左右する！SQL Serverのストアドプロシージャキャッシュと再利用の仕組みを理解しよう
SQL Serverでは、クエリを実行するたびに最適な実行計画を生成しています。
しかし、毎回最適化を行うのはコストが高いため、一度生成した実行計画をキャッシュして再利用する仕組みがあります。
この記事では、その中核を担うストアドプロシージャキャッシュ（プランキャッシュ）について、
仕組み・確認方法・パフォーマンス最適化のポイントをわかりやすく解説します。
目次
ストアドプロシージャキャッシュとは？
SQL Serverが生成する「実行計画」を再利用する仕組み
ストアドプロシージャキャッシュ（Procedure Cache）とは、 SQL Serverがクエリの最適化時に生成した実行計画（Execution Plan）を保存し、 再利用するための領域です。
SQL文を再実行するとき、同じ構文・同じパラメータ構成であれば、 新たに最適化せずにキャッシュ済みの実行計画を利用します。
これによりCPU負荷が軽減され、パフォーマンスが大幅に向上します。
キャッシュに保存される内容と仕組み
キャッシュされる対象
- ストアドプロシージャ
- パラメータ化されたクエリ
- Ad-hocクエリ（構文一致時）
- トリガーやユーザー定義関数（UDF）
キャッシュの仕組み
- クエリ実行時に最適化（プラン生成）
- 生成された実行計画をメモリに格納（キャッシュ）
- 同一構文のクエリ実行時に再利用
キャッシュが削除されるタイミング
- メモリ不足時
- データ構造（統計情報・インデックス）の変更時
- DB再起動やキャッシュクリア時
-- キャッシュ全体をクリア（実務では慎重に）
DBCC FREEPROCCACHE;
※ このコマンドは本番環境では安易に使用しないよう注意しましょう。
キャッシュの確認方法
動的管理ビュー（DMV）を使う方法
SQL Serverでは、DMV（Dynamic Management View）を使ってキャッシュ状況を確認できます。
-- 実行計画キャッシュの一覧を確認
SELECT TOP 20
DB_NAME(st.dbid) AS DatabaseName,
cp.objtype,
cp.usecounts,
st.text AS SQLText
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
ORDER BY cp.usecounts DESC;
usecounts：再利用回数objtype：オブジェクト種別（Proc / Adhocなど）text：キャッシュされたSQL文
この結果をもとに、再利用が少ないSQLやキャッシュ効率を分析できます。
キャッシュ再利用の仕組みと注意点
再利用される条件
- 完全に同一の構文であること（空白・改行も含む）
- 同一データベース・同一ユーザーコンテキスト
- 同じSETオプション状態（例：ANSI_NULLS）
再利用されないパターン
- 動的SQLで都度異なる文字列を生成
- SETオプションが異なるセッション
- 統計情報の更新などで再コンパイルが発生
再利用率を上げるには、パラメータ化されたクエリやストアドプロシージャを活用することが重要です。
パラメータスニッフィングとの関係
パラメータスニッフィングとは？
最初に実行されたパラメータをもとに最適化された実行計画が、 以降の異なるパラメータでも再利用される現象を指します。
結果として、一部の条件では最適化されない実行計画が使われ、 パフォーマンス低下を引き起こすことがあります。
対策例
OPTION (RECOMPILE)：都度最適化を行うOPTIMIZE FOR UNKNOWN：代表値ではなく汎用計画を使用- 統計情報を最新化して最適化を促す
CREATE PROCEDURE GetSales
@CustomerID INT
AS
BEGIN
SELECT * FROM Sales
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR UNKNOWN);
END;
キャッシュ再利用の仕組みとパラメータスニッフィングは密接に関係しているため、 チューニング時には両方を意識して設計することが重要です。
実務での最適化ポイント
- 動的SQLを避け、ストアドプロシージャを活用する
- パラメータ化を意識し、キャッシュ再利用率を高める
- キャッシュヒット率をDMVで定期確認
- パラメータスニッフィング対策を実装（OPTION句など）
- 不要キャッシュをクリアする場合は限定的に実行
-- 特定プランのみ削除（plan_handleを指定）
DBCC FREEPROCCACHE (0x06000800B582FE21E8F1C3A8010000000000000000000000);
全体をクリアするより、安全に特定プランのみ除去できます。
まとめと次のステップ
学んだ内容の整理
- ストアドプロシージャキャッシュは実行計画を再利用し、最適化コストを削減。
- 再利用条件は構文・SETオプション・パラメータが一致すること。
- パラメータスニッフィングが発生すると逆効果になる場合がある。
- DMVを使ってキャッシュ状況を定期的に分析する。
参考リンク
0 件のコメント:
コメントを投稿