キャッシュされたプロシージャの実行統計を出力する：SQL Server動的管理ビューレファレンス（60）

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

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

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

概要

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

　実行プランにはツリー構造で表されるプロシージャの実行計画以外にも、実行プランがキャッシュされた時刻や実行回数、実行時間など、実行統計の情報が保存されています。

　「sys.exec_procedure_stats」動的管理ビューを使用することで、プランキャッシュに保存されているプロシージャの実行プランについて、実行統計の情報を出力できます。

• sys.exec_procedure_stats（Transact-SQL）

出力内容

列名	データ型	説明
database_id	int	ストアドプロシージャのデータベースID
object_id	int	ストアドプロシージャのオブジェクトID
type	char（2）	オブジェクトの種類 　P＝SQLストアドプロシージャ 　PC＝アセンブリ（CLR）ストアドプロシージャ 　X＝拡張ストアドプロシージャ
type_desc	nvarchar（60）	オブジェクトの種類の説明 　SQL_STORED_PROCEDURE 　CLR_STORED_PROCEDURE 　EXTENDED_STORED_PROCEDURE
sql_handle	varbinary（64）	ストアドプロシージャのクエリハンドル 「dm_exec_query_stats」の「sql_handle」と相関する
plan_handle	varbinary（64）	プランの識別子 プランがキャッシュに残っている間だけ一定 「sys.dm_exec_cached_plans」動的管理ビューで使用できる ネイティブコンパイルストアドプロシージャがメモリ最適化テーブルに対してクエリを実行するときは、常に「0x000」
cached_time	datetime	ストアドプロシージャがキャッシュに追加された時刻
last_execution_time	datetime	最後に実行された時刻
execution_count	bigint	最後にコンパイルされてから実行された回数
total_worker_time	bigint	コンパイル後にこのストアドプロシージャの実行で消費されたCPU時間の合計（マイクロ秒単位） ネイティブコンパイルストアドプロシージャで、多くの実行が1ミリ秒未満である場合、精度が高くない可能性がある
last_worker_time	bigint	前回実行したときに使用されたCPU時間（マイクロ秒単位）
min_worker_time	bigint	1回の実行で使用した最小CPU時間（マイクロ秒単位）
max_worker_time	bigint	1回の実行で使用した最大CPU時間（マイクロ秒単位）
total_physical_reads	bigint	コンパイル後にこのストアドプロシージャの実行で行われた物理読み取りの合計数 メモリ最適化テーブルを参照する場合は「0」
last_physical_reads	bigint	最後に実行された物理読み取り数 メモリ最適化テーブルを参照する場合は「0」
min_physical_reads	bigint	1回の実行で行われた物理読み取りの最小数 メモリ最適化テーブルを参照する場合は「0」
max_physical_reads	bigint	1回の実行で行われた物理読み取りの最大数 メモリ最適化テーブルを参照する場合は「0」
total_logical_writes	bigint	コンパイル後にこのストアドプロシージャの実行によって行われた論理書き込みの合計数 メモリ最適化テーブルを参照する場合は「0」
last_logical_writes	bigint	最後に実行されたときに行われた論理書き込みの合計数 ページがすでにダーティであった場合はカウントされない メモリ最適化テーブルを参照する場合は「0」
min_logical_writes	bigint	1回の実行で行われた論理書き込みの最小数 メモリ最適化テーブルを参照する場合は「0」
max_logical_writes	bigint	1回の実行で行われた論理書き込みの最大数 メモリ最適化テーブルを参照する場合は「0」
total_logical_reads	bigint	コンパイル後にこのストアドプロシージャの実行によって実行された論理読み取りの合計数 メモリ最適化テーブルを参照する場合は「0」
last_logical_reads	bigint	最後に実行されたときに行われた論理読み取りの数 メモリ最適化テーブルを参照する場合は「0」
min_logical_reads	bigint	1回の実行で行われた論理読み取りの最小数 メモリ最適化テーブルを参照する場合は「0」
max_logical_reads	bigint	1回の実行で行われた論理読み取りの最大数 メモリ最適化テーブルを参照する場合は「0」
total_elapsed_time	bigint	このストアドプロシージャの実行完了までの経過時間の合計（マイクロ秒単位）
last_elapsed_time	bigint	このストアドプロシージャの前回の実行完了までの経過時間（マイクロ秒単位）
min_elapsed_time	bigint	このストアドプロシージャの実行完了までの最小経過時間（マイクロ秒単位）
max_elapsed_time	bigint	このストアドプロシージャの実行完了までの最大経過時間（マイクロ秒単位）
total_spills	bigint	コンパイル後にこのストアドプロシージャの実行によって書き込まれたページの合計数 適用対象：SQL Server 2017（14.x）CU3以降
last_spills	bigint	最後に実行されたときに書き込まれたページの数 適用対象：SQL Server 2017（14.x）CU3以降
min_spills	bigint	1回の実行中に書き込まれたページの最小数 適用対象：SQL Server 2017（14.x）CU3以降
max_spills	bigint	1回の実行中に書き込まれたページの最大数 適用対象：SQL Server 2017（14.x）CU3以降
pdw_node_id	int	このディストリビューションが配置されているノードの識別子 適用対象：Azure SQLデータウェアハウス、Parallel Data Warehouse
total_page_server_reads	bigint	コンパイル後にこのストアドプロシージャの実行によって実行されたページサーバの読み取りの合計数 適用対象：Azure SQL Databaseハイパースケール
last_page_server_reads	bigint	最後にストアドプロシージャを実行したときに行われたページサーバの読み取り回数 適用対象：Azure SQL Databaseハイパースケール
min_page_server_reads	bigint	1回の実行で行われたページサーバの読み取りの最小数 適用対象：Azure SQL Databaseハイパースケール
max_page_server_reads	bigint	1回の実行で行われたページサーバの読み取りの最大数 適用対象：Azure SQL Databaseハイパースケール

動作例

　ストアドプロシージャを作成、実行して「sys.exec_procedure_stats」動的管理ビューを出力します（図1）。

図1　12139行もの結果が出力された

　プロシージャキャッシュに記録されている全ての実行プランの実行統計が出力されました。

　「sys.exec_procedure_stats」動的管理ビューは、プランキャッシュに記録された全てのプロシージャの実行統計を出力します。そのため、特定のストアドプロシージャの実行統計を確認するには、「object_id」などを使用して結果を絞り込みます（図2）。

図2　「sys.all_objects」カタログビューを使用してプロシージャ名の条件を追加して出力した

　SQL Server 2019 RC1では、1つの実行プランにつき同一の情報が40行出力されたため、図2で「DISTINCT」句を追加しました。SQL Server 2017の出力は1行であり、SQL Server 2019 RC1の不具合の可能性があります。

　実行結果からは、実行時間や実行回数、CPU時間などのパフォーマンスに関する情報を取得できました。

　絞り込み条件や出力順を変更することで、実行時間の長いプロシージャやCPU負荷の高いプロシージャを抽出することが可能です（図3）。

図3　実行回数順に1秒当たりの「worker_time」を計算して出力するクエリを作成し実行したところ

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

筆者紹介

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

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

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

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