SE の雑記

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

SQL Database のリソース使用状況の取得方法の一例

leave a comment

SQL Database の DTU モデルでは、リソースの使用状況は、具体的な値ではなく、「割り当てられているリソースの何 % を使用しているか」というような尺度で取得することができるようになっており、これらの情報は次の DMV から取得することができます。

今の SQL Database では、これ以外の方法でも情報を取得することができますので、軽く紹介してみたいと思います。

取得用のクエリですが、一揃いを Simple Monitor に置いてあります。
情報取得については、使用状況を確認する対象のデータベースに「Simple_Monitor」というテーブルを作成し、このテーブルに対して情報を格納するような作りになっています。
最初に、GitHub 内の「情報取得.sql」のコメント内の CREATE TABLE ステートメントを実行して、リソースの使用状況を確認する DB にテーブルを作ってください。
次に SSMS 等で「情報取得.sql」を実行して、情報の取得を開始すれば作業は、取得作業は完了です。
実行すると、S3 レベルで、CPU の使用率が 1~4% 程度上昇し、1 日分取得すると、135 MB 程度、領域を使用することになりますので、無負荷というわけではありませんので、本番環境等で実行する場合は自己責任でお願いいたします。
(S0 のような CPU の割り当てがもともと低いサイズを使用すると 15% 程度は CPU を使いますので、低いサービスレベルを使用している場合は、性能への影響を特に注意してください)
「情報取得.sql」は 5 秒間隔でテーブルに性能情報を追加するものになります。
取得している情報は「sys.dm_os_performance_counters」の情報を加工したものとなり、パフォーマンスモニターから実際のリソース使用状況を取得するというアプローチをとっています。
取得した情報は「リソース使用状況.sql」を実行することで、加工した情報を取得できます。
私の環境では、テーブルの再構築を実施した場合、「sys.dm_db_resource_stats」の情報は、次のようになりました。

avg_cpu_percent 6.93
avg_data_io_percent 4.11
avg_log_write_percent 100
avg_memory_usage_percent 4.34
xtp_storage_percent 0
max_worker_percent 1
max_session_percent 0.16
dtu_limit 100

 
インデックスの再構築は、「対象のテーブルからデータを読み込み、再構築済みのデータを構成する」というような処理となりますので、

  • 対象データの読み取り
  • 整列されたデータを構築するためのログの書き込み
  • 上記を実施するための CPUの利用

というようなワークロードが発生します。
今回の処理では、割り当てられているリソースの中の「ログの書き込みで使用できるリソース」(avg_log_write_percent) が上限に達しているということが確認できますね。
このデータの場合ですと、「ログの書き込み」のリソース割り当てがボトルネックとなり、この処理の効率よい実行を阻害しているということを判断することができます。
「リソース使用状況.sql」で取得された情報では、DTU のリソースの使用状況も出力されますが、それ以外にも、パフォーマンスモニター観点でのリソース使用状況の情報も出力されるようになっています。

  • Log MBytes Flushed/sec
  • Background writer MBytes/sec

の値を見ることで、「avg_log_write_percent」が「何%」だった時に、実際にどの程度のスループットでログの書き込みが行われていたかを判断することができるというように、「DTU が何 % だった時に該当のリソースがどの程度使用されていたか」を確認することができるようなクエリとなっています。

  • Basic / Standard では、リモートストレージ (HTTP ストレージ)
  • Premium では、ローカルストレージ

を利用していることもありますので、ディスク I/O 周りはその辺の情報も取得するようにしています。
(Premium のサービスレベルを使用している場合、HTTP ストレージの情報が有効な値ではありませんが、Basic / Standard では有効な値となるというような、それぞれの構成によって適した項目を確認する必要があります)
「今これぐらいのリソースを使用しているので、DTU モデルではどれくらいになるのか?」については、DTU Calculator で取得することができます。
本クエリは「DTU のリソースの使用状況が実際のスループットやリソース使用状況としてどれくらいなのか」のアプローチで情報を取得するものになります。
(時期によって構成に変更があり、リソースの割り当て状況も変わってきますので、この情報で取得したものは、「情報取得時点のリソースの使用状況であり、後日取得した場合は変更されている可能性がある」ということは理解しておいてください)
初期の SQL Database と違い、今の SQL Database は SQL Server のアプローチ方法を知っていると、様々な情報を取得することができるようになっていますのでアプローチの一つとして本クエリが参考になれば幸いです。
SQL Database の DTU は、DTU の値による相対的な性能の差を表したものですので、想定負荷をかけた状態で DTU のサイズの調整をする、運用の中でスケールを変更させるというような、クラウドならではの柔軟なスケールの変更によるサイズの調整をするようなことは意識しておいた方がよいのかなと。
最初に「このサイズに決めたら以降は変更しない」というような運用が必要な場合は、DTU モデルの特性が活かせないかと思いますので、そのような場合には、vCore モデルになりますかねぇ。

Share

Written by Masayuki.Ozawa

6月 16th, 2018 at 11:30 pm

Posted in SQL Database

Tagged with

Leave a Reply