実行プランの属性情報を出力する：SQL Server動的管理ビューレファレンス（59）

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

[伊東敏章，＠IT]

印刷

通知 連載「％」の新着をメールで通知

隕九ｋPost

Shareシェア

はてなブックマーク

SharePocket Button

SQL Server動的管理ビュー一覧

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

概要

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

　実行プランにはツリー構造の実行計画が含まれますが、実行計画以外にも、データベースIDやセッション言語の情報、実行コンテキストの数などの属性情報が含まれています。

　「sys.dm_exec_plan_attributes」動的管理関数を使用すると、実行プランの属性情報を出力できます。

• sys.dm_exec_plan_attributes（Transact-SQL）

構文と引数

構文　sys.dm_exec_plan_attributes ( plan_handle )

引数名	データ型	説明
plan_handle	varbinary（64）	プランキャッシュに格納されている実行プランの一意の識別子 「sys.dm_exec_cached_plans」動的管理ビューを参照することで、プランキャッシュに格納された実行プランのプランハンドルを取得できる

出力内容

　結果は、属性ごとに1行のデータが出力されます。

列名	データ型	説明
attribute	varchar（128）	プランに関連付けられている属性の名前 （属性の一覧項参照）
value	sql_variant	プランに関連付けられている属性の値
is_cache_key	bit	この属性が、プランに対するキャッシュ参照キーの一部として使用されているかどうか

属性の一覧

　属性「attribute」列に表示される属性と、値「value」及びデータ型の説明は下記の通りです。

属性	データ型	説明
set_options	int	プランで有効になっているSETオプションの値（SETオプション項参照）
objectid	int	データベースオブジェクト（プロシージャ、ビュー、トリガーなど）ではオブジェクトID アドホックプラン、準備されたプランでは、バッチテキストの内部ハッシュ
dbid	int	プランによって参照されるエンティティを含むデータベースのID アドホックプランまたは準備されたプランでは、バッチの実行元となるデータベースID
dbid_execute	int	システムオブジェクトの場合、キャッシュされたプランを実行するためのデータベースID その他の場合は「0」
user_id	int	「-2」の場合、バッチが暗黙的な名前解決に依存せず、複数ユーザー間での共有が可能であることを示す 他の値の場合、データベースクエリを送信したユーザーのユーザーID
language_id	smallint	接続の言語ID 「sys.syslanguages」で参照できる
date_format	smallint	接続の日付形式
date_first	tinyint	週の最初の曜日（DATEFIRST）
status	int	内部ステータスビット
required_cursor_options	int	カーソルの種類などユーザーによって指定されたカーソルオプション（CORSORオプション項参照）
acceptable_cursor_options	int	SQL Serverがステートメントの実行をサポートするために暗黙的に変換できるカーソルオプション
inuse_exec_context	int	クエリプランを使用している現在実行中のバッチの数
free_exec_context	int	現在使用されていないクエリプランのキャッシュされた実行コンテキストの数
hits_exec_context	int	実行コンテキストが再利用された回数（バッチ実行回数の合計）
misses_exec_context	int	実行コンテキストがプランキャッシュに見つからなかった回数
removed_exec_context	int	プランキャッシュのメモリの負荷により削除された実行コンテキストの数
inuse_cursors	int	キャッシュされたプランを使用しているカーソルを1つ以上含む、現在実行中のバッチの数
free_cursors	int	キャッシュされたプランのアイドル状態または解放されたカーソルの数
hits_cursors	int	キャッシュされたプランから非アクティブなカーソルが取得され、再利用された回数（バッチ実行回数の合計）
misses_cursors	int	非アクティブなカーソルがキャッシュに見つからなかった回数
removed_cursors	int	プランキャッシュのメモリの負荷により削除されたカーソルの数
sql_handle	varbinary（64）	バッチのSQLハンドル
merge_action_type	smallint	MERGEステートメントの結果として使用するトリガーの実行プランの種類 　0：非トリガープラン、または「DELETE」アクションのみを指定する「MERGE」ステートメントの結果として実行されるトリガープラン 　1：「MERGE」ステートメントの結果として実行される「INSERT」トリガープラン 　2：「MERGE」ステートメントの結果として実行される「UPDATE」トリガープラン 　3：対応する「INSERT」アクションまたは「UPDATE」アクションを含む「MERGE」ステートメントの結果として実行される「DELETE」トリガープラン

SETオプション

　「set_options」属性の値は、下記のオプションの値の和で示されます。

オプション	値
ANSI_PADDING	1
Parallel Plan	2
FORCEPLAN	4
CONCAT_NULL_YIELDS_NULL	8
ANSI_WARNINGS	16
ANSI_NULLS	32
QUOTED_IDENTIFIER	64
ANSI_NULL_DFLT_ON	128
ANSI_NULL_DFLT_OFF	256
NoBrowseTable プランがFOR BROWSE操作の実装に作業テーブルを使用しないことを示す	512
TriggerOneRow AFTERトリガーデルタテーブルに対する行の最適化がプランに含まれていることを示す	1024
ResyncQuery クエリが内部システムストアドプロシージャによって送信されたことを示す	2048
ARITH_ABORT	4096
NUMERIC_ROUNDABORT	8192
DATEFIRST	16384
DATEFORMAT	32768
LanguageID	65536
UPON プランがコンパイルされたとき、データベースオプションPARAMETERIZATIONがFORCEDに設定されたことを示す	131072
ROWCOUNT	262144

CURSORオプション

　「required_cursor_options、acceptable_cursor_options」列の値は下記のオプションの値の和で示されます。

オプション	値
なし	0
INSENSITIVE	1
SCROLL	2
READ ONLY	4
FOR UPDATE	8
LOCAL	16
GLOBAL	32
FORWARD_ONLY	64
KEYSET	128
DYNAMIC	256
SCROLL_LOCKS	512
OPTIMISTIC	1024
STATIC	2048
FAST_FORWARD	4096
IN PLACE	8192
FOR select_statement	16384

動作例

　SQL Server Management Studio（SSMS）でSQL Serverに接続し、クエリを実行します（図1）。

図1　SSMSの新しいクエリから、単純なクエリを実行したところ

　「sys.dm_exec_cached_plans」動的管理ビューをもとにして実行したクエリテキストを条件に、実行プランの属性情報を「sys.dm_exec_plan_attributes」動的管理関数を使用して取得しました（図2）。

図2　実行したクエリの属性情報を表示したところ

　実行結果から、接続のSETオプションの値や実行コンテキストの使用状況を確認できました。

　「set_options」列の値が「4345」のため、「ANSI_PADDING」（1）、「CONCAT_NULL_YIELDS_NULL」（8）、「ANSI_WARNINGS」（16）、「ANSI_NULLS」（32）、「QUOTED_IDENTIFIER」（64）、「ANSI_NULL_DFLT_ON」（128）、「ARITH_ABORT」（4096）のSETオプションが有効化されていることが分かります。

　実際にSSMSからクエリオプション画面を確認すると、上記のSETオプションが有効化されていました（図3、図4）。

図3　SSMSのクエリオプションの詳細設定画面からSETオプションを確認できる

図4　SSMSのクエリオプションのANSI画面から、一部のSETオプションを確認できる

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

縺薙�騾｣霈峨ｒ縲碁€｣霈芽ｨ倅ｺ九い繝ｩ繝ｼ繝医€阪↓逋ｻ骭ｲ縺吶ｋ New

筆者紹介

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

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

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

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