SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

CPU 使用率 / バッチ実行数との対比による Batch Resp Statistics の活用

with one comment

SQL Server のパフォーマンスモニターとして、SQL Batch Resp Statistics という項目があります。
この項目は、SQL のクエリをバッチという単位で集計した際の次のような観点の情報を確認することができます。

  • 特定の実行時間 (Elapsed Time) の範囲のバッチ実行回数とバッチ実行時間の合計
  • 特定の CPU 使用時間 (CPU Time) の範囲のバッチ実行回数とバッチ実行時間の合計

この項目は「インスタンス内でどのような時間を消費しているクエリが実行されているか?」を把握するのに役に立つ情報となっており、単体でも便利なのですが、他の情報と組み合わせることでさらに活用の幅が広がります。
本投稿では、「CPU 使用時間の範囲のバッチ情報」を活用した方法についてまとめてみたいと思います。

Batch Resp Statistics の CPU 使用時間の情報としては次のような項目があります。

  • Batch Resp Statistics(CPU Time:Requests)
  • Batch Resp Statistics(CPU Time:Total(ms))

これらの項目には、「>=000000ms & <000001ms」「>=000001ms & <000002ms」というような範囲が定められています。
「>=000000ms & <000001ms」の「CPU Time:Requests」 が「10」だった場合は、「1ms 未満の CPU を使用したバッチが 10 回実行された」ことになります。
「>=000000ms & <000001ms」の「CPU Time:Total(ms)」 が「10」だった場合は、「1ms 未満の CPU を使用したバッチが複数回実行され、10ms CPU が使用された」ことになります。
これらの情報を他の情報と組み合わせることで、情報のブレークダウンを行うことができます。
それでは実際にブレークダウンをしてみます。
今回は、パフォーマンスモニターのログを Perflog を次のオプションで実行して Excel のグラフ化を行ったものを加工しながら使用しています。

.\Perflog.ps1 -CounterFile ".\DataCollector01.blg" -ConfFiles "SQL_BatchReq.json","SQL_BatchRespCPUReq.json","SQL_BatchRespCPUTotal.json","SRV_Process.json"

パフォーマンスモニターで Batch Resp Statistics の情報を取得した場合、「累計値」が取得されることになります。

今回の分析では時系列の増減を確認したいため、前の取得値との差分を算出したものをグラフ化しています。

(Batch Resp Statistics の時系列データを算出するためには、(「確認対象データ」-「一つ前のデータ」) / 取得間隔 を計算する必要があります。この算出は Perflog スクリプト内では自動で実施されますので、スクリプトでグラフ化した場合は個別の算出は不要です。)
SQL Server に対してのクエリ (バッチ) の実行数を取得したものが次のグラフとなります。
image
毎秒、120 前後のクエリが実行されていることが確認できますね。
それではこの情報と「Batch Resp Statistics(CPU Time:Requests) 」を組み合わせて、新しく、次のような 2 軸グラフを作成してみます。
image
赤い太線の線グラフがバッチ実行数となり、積み上げ面グラフが Batch Resp Statistics(CPU Time:Requests) となります。

棒グラフと積み上げ面グラフが一致したグラフとなっていますね。
このグラフから「実行されているバッチ実行数に対して、CPU 使用時間がどの範囲のクエリが実行回数が多いか」という判断を行うことができます。
積み上げ面グラフから、次の範囲の情報の面積が多くなっているとが確認できます。

  • >=000000ms & <000001ms
  • >=000001ms & <000002ms

このことから、実行されているクエリの中では、CPU 使用時間が上記のクエリが大半を占めていることということを判断することができます。

この情報を活用することで、CPU 使用時間の大きい範囲の時間帯の面積が多い場合には、頻繁に CPU を消費するクエリが実行されているというような分析をすることができます。
それでは他の組み合わせも見てみましょう。
CPU 使用率の情報を確認してみます。
SQL Server のプロセスの CPU 使用率としては 8% 程度を推移しています。
image
それではこの情報と「Batch Resp Statistics(CPU Time:Total(ms))」を組み合わせてみます。
image

CPU 使用率はプロセスの情報をベースに取得していたため、完全に積み上げグラフと一致しているわけではないですが、おおよそのトレンドは一致していますね。
今回の積み上げグラフは CPU 使用時間を使用していますので「面積が多い時間範囲は CPU を多く使用している」ということになります。
今回の積み上げグラフであれば「>=000001ms & <000002ms」や「>=000005ms & <000010ms」の範囲の CPU 使用時間のクエリが面積が多くなっています。
このことから CPU 使用率を減らすためには、これらの CPU 使用時間のクエリを一段下の時間範囲にすることができれば、CPU 使用率を効果的に減らすことができる可能性が考えられることになります。
「どのようなクエリがこの CPU 使用時間のクエリとなっているか?」については、プランキャッシュのクエリから情報を取得することで確認をすることができます。
このような分析が Batch Resp Statistics と他の情報を組み合わせた際の基本的な考え方となります。
クエリチューニングをする際に、どのようなクエリからアプローチをすればよいか?を考える際には、何も情報がないところから始めると、難易度が上がりますので、このような手法で、チューニング対象の判断を検討するという方法を覚えておくと、分析の幅が広がるのではないでしょうか。
このような「層の厚み」による分析をすると「チューニングの効果があったのか?」を「どの厚みが減ったか?」で確認することができますので便利なシチュエーションがいくつかあるかと思います。

Written by Masayuki.Ozawa

12月 8th, 2019 at 10:00 pm

Posted in SQL Server

Tagged with ,

One Response to 'CPU 使用率 / バッチ実行数との対比による Batch Resp Statistics の活用'

Subscribe to comments with RSS or TrackBack to 'CPU 使用率 / バッチ実行数との対比による Batch Resp Statistics の活用'.

Leave a Reply