#背景
以前こちらの記事で、sys.dm_exec_query_statsを使用して、サーバー全体の中でCPUボトルネックになっているクエリを抽出する方法を紹介しました。

昨今のSQL ServerおよびSQL DatabaseなどのPaaSでは、クエリストアが利用できますので、こちらが利用できる環境であれば、こちらを利用するのが手っ取り早く高精度な情報を得ることができると思います。

しかし、以下の状況下においては、現在でもsys.dm_exec_query_statsを利用した方法が有用であると考えます。

SQL Server 2014など、古いバージョンを使っておりクエリストアが使えないとき
クエリストアを使用しているが、クエリストアに関する情報の保存サイズをおさえるため等の理由で、レンジを1時間や30分などに設定している常用下で、特定の5分や10分間の間のリソースボトルネックとなっていたクエリを後追いしたいとき

※リソースボトルネック＝CPU、論理IO、ワークスペースメモリを指しています。

#いままで使用していた方法
こちらの記事から抜粋した、以下のアイデアでボトルネックを調査していました。

要は、sys.dm_exec_requestsのスナップショットを2点とって、その2点の情報を使っていかに高精度な計算を行うか、というものでした。
この方法でもある程度正確にボトルネッククエリをリストアップすることができますが、以下のようなケースのうち、クエリAとクエリBしか考慮できていませんでした。

Snapshot①と②の間隔が数分など短い場合や、DBサーバーのメモリに余裕がありキャッシュアウトがほとんど起きないようなケースであればこれでも問題ないのですが、1時間や1日単位でボトルネック調査をしたい場合などはSnapshot間隔の間でキャッシュとキャッシュアウトが起こったり、Snapshot②を取得する前にキャッシュアウトされることも十分考えられます。

このような状況下であってもより高精度にボトルネッククエリを抽出する方法を考えました。
前提として、こちらの仕組みを使って1分間に1回、DMVの情報をテーブルに保存しているとします。

#方法
2点の情報を使うのではなく、抽出期間に存在する全てのSnapshotを利用します。
例えば、特定の1時間のCPUボトルネックを調査したい場合、Snapshot①とSnapshot②の間には約60個のSnapshotが存在します。

これらの情報も使うことで、今までの方法であれば見落としていたクエリCやクエリDも抽出することができるようになります。

#クエリ
ストアド単位の情報の解析はこちら
ステートメント単位の情報の解析はこちら
にそれぞれまとめておきました。

#まとめ
DMVを使用したリソースボトルネックの抽出精度を向上させるために考えたことと、実装したクエリをご紹介しました。
実際、今までの調査クエリだと見落としていたクエリが、新方式だとボトルネックとしてあがってくるようになり、精度の向上を実感しています。
もしよければご利用ください。