クエリを最後に実行した際の実際の実行プランを出力する：SQL Server動的管理ビューレファレンス（70）

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

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys.dm_exec_query_stats」における、クエリを最後に実行した際の実行プランの出力について解説します。対応バージョンは、SQL Server（サポートされている全てのバージョン）です。

概要

　SQL Serverでは、クエリは実行前にクエリオプティマイザーによってコンパイルされ、実行プランが作成されます。実行プランには、クエリ実行に必要なデータ抽出や結合、フィルタリングなどの処理を行う順序や方法が含まれており、作成された実行プランは次回以降の実行時のためにキャッシュされます。

　実行プランには、コンパイル時に作成される、統計情報などを元にして計算されたデータ量などの推定情報が含まれる推定実行プランと、クエリ実行完了時に作成される、推定実行プランの情報に加えて、クエリ実行時の実際のリソース使用状況やデータ行数、ランタイム警告などの情報が含まれる実際の実行プランがあります。

　実際の実行プランには、クエリ実行時に実際に読み込まれたテーブルやインデックスの行数や、演算子によって処理された行数、実行回数などの情報が含まれています。そのため、クエリのパフォーマンスの分析などに非常に役立つ情報となっています。

　SQL Server 2017以前までは、実際の実行プランを確認するには、クエリの実行前にサーバトレースや拡張イベントなどの記録を開始しておき、実際にクエリを実行したタイミングを含めて資料を採取する必要がありました。

　SQL Server 2019では、事前にトレースフラグ「2451」またはデータベースの構成で「LAST_QUERY_PLAN_STATS」オプションを有効化しておくことで、新しく実装された「sys.dm_exec_query_plan_stats」動的管理関数を使用して、以前のバージョンまでは事前に資料採取を開始しておかなければ確認できなかった実際の実行プランを、クエリの実行完了後に確認できるようになりました。

• sys.dm_exec_query_plan_stats（TRANSACT-SQL）

構文と引数

構文　sys.dm_exec_query_plan_stats(plan_handle)

列名	データ型	説明
plan_handle	varbinary（64）	実行プランを一意に識別する識別子 実行プランがプランキャッシュ内に存在するか、現在実行されている必要がある 次の動的管理オブジェクトから取得できる ・sys.dm_exec_cached_plans（Transact-SQL） ・sys.dm_exec_query_stats（Transact-SQL） ・sys.dm_exec_requests（Transact-SQL） ・sys.dm_exec_procedure_stats（Transact-SQL） ・sys.dm_exec_trigger_stats（Transact-SQL）

出力内容

列名	データ型	説明
dbid	smallint	ステートメントがコンパイルされたデータベースのID
objectid	int	この実行プランのオブジェクトのID アドホックおよび準備されたバッチでは「NULL」
number	smallint	番号付きストアドプロシージャを表す整数 アドホックおよび準備されたバッチでは「NULL」
encrypted	bit	対応するストアドプロシージャが暗号化されているかどうか 　「0」＝暗号化されていない 　「1」＝暗号化されている
query_plan	xml	この実行プランが最後に実行されたときのXML形式の実際のプラン表示 アドホックバッチやストアドプロシージャの呼び出し、ユーザー定義関数の呼び出しに含まれているTransact-SQLステートメントごとに1つのプランが生成される

動作例

　SQL Server Management Studio（SSMS）で、動作確認用に作成したクエリを実行しました（図1）。

図1　実行プランを出力する対象のクエリを実行したところ

　「sys.dm_exec_query_plan_stats」動的管理関数を使用して、クエリの実際の実行プランを取得するには、クエリ実行に使用された実行プランのプランハンドルが必要です。

　今回はプランキャッシュに記録されているクエリの実行情報を出力できる「sys.dm_exec_query_stats」動的管理ビューと、ステートメントのテキスト情報を出力できる「sys.dm_exec_sql_text」動的管理関数を「sys.dm_exec_query_plan_stats」動的管理関数と組み合わせることで、クエリテキストと併せて実際の実行プランの情報を出力しました（図2）。

図2　クエリテキストに続いて「sys.dm_exec_query_plan_stats」動的管理関数の情報を出力したところ

　動作確認用クエリの実行直前に「DBCC FREEPROCCACHE」コマンドを実行してプランキャッシュをクリアしたので、他のクエリの情報は表示されていません。

　出力された情報はXML形式のテキストです。SSMSはXML形式の実行プランのグラフィカルな表示に対応しており、画面に表示されたXML形式の実行プランのテキストをクリックすることで、グラフィカルな画面で表示できます。

　先ほどの実行では、トレースフラグ「2451」やデータベースの「LAST_QUERY_PLAN_STATS」オプションを有効化していないため、簡略化された実行プランのみが出力されました。簡略化された実行プランには、実際のデータ行数などの情報は記録されておらず、実際の実行プランとしての情報は得られそうにありませんでした（図3）。

図3　簡略化された実行プランには、先頭のオペレーターの推定情報のみが表示された

　次に、データベースで「LAST_QUERY_PLAN_STATS」オプションを有効化して（図4）、テスト用のクエリの再実行と「sys.dm_exec_query_plan_stats」動的管理関数の出力を行いました（図5）（図6）。

図4　データベースの「LAST_QUERY_PLAN_STATS」オプションを有効化したところ

図5　「sys.dm_exec_query_plan_stats」動的管理関数をもう一度出力したところ

図6　XML形式の実行プランをクリックすると、実際の実行プランが出力された

　「LAST_QUERY_PLAN_STATS」オプションを有効化してクエリを実行した場合には、実際の実行プランが表示され、SSMSで表示すると、実際の行数などの情報を確認できました。

　類似の動的管理関数に「sys.dm_exec_query_plan」があります。「sys.dm_exec_query_plan」で出力できるのは推定実行プランであるため、「sys.dm_exec_query_plan_stats」動的管理関数を出力できる場合には、これを出力した方がより多くの情報を確認できます（図7）。

図7　「sys.dm_exec_query_plan」は推定実行プランを出力するため、確認できる情報が少ない

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

筆者紹介

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

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

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

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