プランキャッシュに保存された実行プランの一覧を出力する：SQL Server動的管理ビューレファレンス（41）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、プランキャッシュに保存された実行プランの一覧の出力について解説します。

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys.dm_exec_cached_plans」における、プランキャッシュに保存された実行プランの一覧の出力について解説します。対応バージョンは、SQL Server 2008以降です。

概要

　SQL Serverでは、クエリを実行する際にはクエリ文や統計情報を使用してコンパイル処理が実行され、コンパイル結果は「実行プラン」としてプランキャッシュに保存されます。次回以降の実行では、コンパイル前にプランキャッシュが確認され、実行プランが保存されている場合にはコンパイルは行われません。

　SQL Server内でメモリの負荷が発生すると、プランキャッシュに保存された実行プランは、コストベースの判断により削除される場合があります。

　「sys.dm_exec_cached_plans」動的管理ビューでは、現在、プランキャッシュに保存されている実行プランの一覧を出力できます。

　なお、「sys.dm_exec_cached_plans」動的管理ビューの結果には、プラン表示やクエリテキストは含まれていません。そのため、実行プランの詳細を確認する場合には、結果に含まれる「plan_handle」列の値を使用し、「sys.dm_exec_query_plan」動的管理関数や「sys.dm_exec_sql_text」動的管理関数と結合して、プラン表示やクエリテキストを出力する必要があります。

• sys.dm_exec_cached_plans（TRANSACT-SQL）

出力内容

　プランキャッシュに保存された実行プランごとに1行出力されます。

列名	データ型	説明
bucketid	int	エントリをキャッシュするハッシュバケットのID
refcounts	int	このキャッシュオブジェクトを参照しているキャッシュオブジェクトの数
usecounts	int	キャッシュオブジェクトが検索された回数。パラメーター化クエリがキャッシュでプランを検索するときは増加しない
size_in_bytes	int	キャッシュオブジェクトによって使用されているバイト数
memory_object_address	varbinary（8）	キャッシュエントリのメモリアドレス
cacheobjtype	nvarchar（34）	キャッシュオブジェクトの種類 　Compiled Plan 　Compiled Plan Stub 　Parse Tree 　Extended Proc 　CLR Compiled Func 　CLR Compiled Proc
objtype	nvarchar（16）	オブジェクトの種類 　Proc: ストアドプロシージャ 　Prepared: 準備済みステートメント 　Adhoc: アドホッククエリ 　ReplProc: レプリケーションフィルタープロシージャ 　Trigger: トリガー 　View: ビュー 　Default: Default 　UsrTab: ユーザーテーブル 　SysTab: システムテーブル 　Check: CHECK 制約 　Rule: ルール
plan_handle	varbinary（64）	メモリ内のプランの識別子
pool_id	int	このプランのリソースプールのID

動作例

　SQL Serverインスタンスに接続し、クエリやストアドプロシージャを実行します。「sys.dm_exec_cached_plans」動的管理ビューを出力すると、プランキャッシュに保存されている実行プランの一覧が表示されます（図1）。

図1　実行プランがキャッシュされていることが分かる

　「sys.dm_exec_cached_plans」動的管理ビューの出力のみではプラン表示や実行プランの元となるクエリやストアドプロシージャが分かりませんので、「plan_handle」列の値（プランハンドル）を使用して「sys.dm_exec_query_plan」動的管理関数と「sys.dm_exec_sql_text」動的管理関数を結合し、再度出力します（図2）。

図2　プランキャッシュに保存された実行プランの、クエリテキストとプラン表示（xml形式）を出力した

　対象のクエリやストアドプロシージャ、実行プランの内容が分かるようになりました。実行プランの再利用回数なども分かります。

　次に、インスタンスで「optimize for ad hoc workloads」サーバ構成オプションを有効化します（図3）。

図3　「optimize for ad hoc workloads」サーバ構成オプションを「T-SQL」で有効化した

　「optimize for ad hoc workloads」サーバ構成オプションを有効化すると、バッチの初回実行時はコンパイル済み実行プランのスタブのみがプランキャッシュに追加され、2回目の実行時に完全な実行プランがキャッシュに追加されるよう、動作が変更されます。

　プランキャッシュをクリアしてクエリを1回だけ実行し、「sys.dm_exec_cached_plans」動的管理ビューを出力します。実行プランのスタブが保存されていることが分かります（図4）。

図4　「cacheobjtype」列の値から実行プランのスタブが保存されていることが分かる

　この時点では完全な実行プランは保存されていないため、「sys.dm_exec_query_plan」動的管理関数でxml形式のプラン表示を出力できません。

　もう一度クエリを実行し、「sys.dm_exec_cached_plans」動的管理ビューを出力すると、先ほどのスタブはプランキャッシュから削除され、完全な実行プランが追加されたことが分かりました（図5）。

図5　「cacheobjtype」列の値から、2回目の実行により完全な実行プランに変更されたことが分かる

　実行プランのスタブは「128バイト」、完全な実行プランは「16384バイト」であり、スタブはサイズが小さいことが分かります。このように、「sys.dm_exec_cached_plans」動的管理ビューを使用して、実行プランのサイズの変化についても調査することができます。

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019 CTP2」をインストールした環境を想定して解説しています。

筆者紹介

椎名 武史（しいな たけし）

日本ユニシス株式会社所属。Microsoft MVP for Data Platform（2017〜）。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章（いとう としあき）

日本ユニシス株式会社所属。入社以来SQL Server一筋で評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。