SSMSで動的管理を使用して実行済みクエリを見つける方法をまとめてみた

はじめに

業務中、既存のRDSで管理されている既存ストアドプロシージャの機能改善をするために、
クエリを探していることがありました。
しかし、その実行中クエリがあまりにも長大なクエリだったので、RDS上のトップSQLでは 4,096 バイトで全文見れないという、なんていうSQL書いてるんだと言いたくなる代物でした。

仕方がないので代案を模索してしたところ、RDSの接続元がSQL Serverでしたので、そちらから
直近で実行したクエリをサーチできる方法を見つけました。

というわけで今回は、動的管理ビュー（DMV：Dynamic Management Views） を用いながら、SSMSの実行クエリを探す方法を書きたいと思います。

やり方

さっそくですが、実行しているクエリを抽出には以下のようにクエリを書きます。

SELECT TOP 20 
    st.text AS QueryText,
    cp.usecounts AS ExecutionCount,
    cp.size_in_bytes / 1024 AS SizeKB,
    cp.creation_time AS CacheTime
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%検索したいテーブル名やキーワード%'
ORDER BY cp.usecounts DESC;

何をしているかを解説してきますが、そもそも動的管理ビューについて、簡単にまとめたいと思います。

動的管理ビュー（DMV：Dynamic Management Views）

DMVとは、SQL Serverが内部で管理している様々な統計情報やメタデータにアクセスするための仮想的なビューです。

どういうことかというと、例えば一台の車があったとします。
車にはエンジン残量や、スピードメーターなどのダッシュボードがありますよね。
ダッシュボードは今の車の状態、つまり、車の内部システムが常に監視、情報を収集している「NOWタイムの車の状態」を表示していると言えます。

DMVは、これと全く同じです。
つまり、SQL Serverという「データベースエンジン」の内部で常に収集されている情報を、私たちが見やすい形で表示してくれる「ダッシュボード」のような存在です。

「仮想的なビュー」というのも、実際にテーブルとして存在しているわけではく、SQL Serverが「今この状態だよ」って教えてくれる窓口になっているというわけです。

なぜあるのか

従来、データベースの内部状況を把握するには、SQL Server Profilerなどの重いツールを使う必要がありました。しかしDMVを使えば、軽量かつリアルタイムに必要な情報だけを取得できるため、本番環境でも安心して使用できるようです。

DMVの命名規則

もう少し、DMVについて話をしますね。
このDMVは sys.dm_ で始まる名前が付けられており、用途別に分類されています。
ざっと以下のようになっているそうです。

sys.dm_exec_* : クエリ実行に関する情報

sys.dm_os_*: オペレーティングシステムレベルの情報

sys.dm_db_*: データベース固有の情報

sys.dm_io_*: I/O統計情報

実行済みクエリの検索

さて、先ほどのクエリに話を戻します。
今回使用しているのは、実行しているクエリの全文を知りたいので、クエリ実行に関する情報から取得します。
以下でsys.dm_exec_cached_plansを使っているのは、SQL Serverがメモリにキャッシュしているクエリ実行プランの情報を提供しているためです。

これにより、plan_handle（プランの識別子）、実行回数、メモリ使用量、キャッシュされた時間などが取得できます。

FROM sys.dm_exec_cached_plans

CROSS APPLYを使用しているのは、plan_handleを指定することで実際に実行されたSQL文のテキストを取得できるためです。

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

あとの各カラムは以下のようになっています。ここではQueryTextさえ分かればよかったので、
ほかの情報はそこまでほしいものではありませんでしたが、パフォーマンスを測定するのにも必要な情報なので抽出しました。

・QueryText: 実際に実行されたSQL文

・ExecutionCount: そのクエリが実行された回数

・SizeKB: クエリプランのメモリ使用量（KB単位）

・CacheTime: プランがキャッシュされた時刻

応用編:実行時間が長いクエリを探す

次いでに、パフォーマンスチューニングする例として、実行時間が長いクエリを探すパターンも
クエリを出しておきます。

-- 実行時間が長いクエリを抽出する
SELECT TOP 10
    st.text AS QueryText,
    qs.total_elapsed_time / 1000 AS TotalElapsedTimeMS,
    qs.avg_elapsed_time / 1000 AS AvgElapsedTimeMS,
    qs.execution_count AS ExecutionCount
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.avg_elapsed_time DESC;

sys.dm_exec_query_statsは、実行時のパフォーマンス統計に特化したDMVです。
「どのくらい時間がかかったか」「どのくらいCPUを使ったか」などの詳細な実行統計を提供します。

/ 1000は、見ればわかると思いますが1000で割っています。これは、SQL Server はSQL Serverの時間統計はマイクロ秒単位で記録されているため、1000で割ることで人間にとって理解しやすいミリ秒単位に変換しています。

このクエリで抽出した値をもとに、パフォーマンスの状態を数値で把握します。
例えば、
・avg_elapsed_timeが1000ms以上 → 明らかに最適化が必要
・execution_countが高く、avg_elapsed_timeも高い → 最優先で対応すべき
・total_elapsed_timeは高いがavg_elapsed_timeは低い → 実行回数を減らす方向で検討

などですね。

終わりに

現在進行形でDBのパフォーマンスを数値化して、どうやったら改善できるか or どれくらい処理速度などの数値が改善できるのかを考えているのですが、まあ、大変ですね（笑）。

自分がこれまで取り組んでいた、SQLを組む作業では見えてこない奥深さを感じさせるぎょうむです。
って感じたので、今回もさっそく記事にさせてもらったのですが（笑）。

これが何かの役に立てばうれしいです！