SE の雑記

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

SQL Database の新しいサービスレベルのリソースのフィット状況を確認

leave a comment

Azure界の抱かれたい男No.1 から、Azure SQL Database introduces new near real-time performance metrics の情報を教えてもらいましたので、ちょっと見てみたいと思います。

上記の情報は、新しく追加された sys.dm_db_resource_stats を使用して、リソースの使用状況を確認し使用しているサービスレベルでリソースが足りているのかを確認するための方法となるかと思います。

DMV からは以下のリソースの情報が取得できます。

  • avg_cpu_percent
  • avg_data_io_percent
  • avg_log_write_percent
  • avg_memory_percent

類似の情報としては以前から、sys.resource_stats がありましたが、こちらについては 5 分間隔の集計値で 14 日の保存となっていました。
今回追加された DMV は 15 秒間隔で 1 時間以内のデータを確認できるというものになるようです。

紹介されている以下のクエリは、リソースの使用状況が 80 % 未満になっていた利用状況を表示するというもののようですね。
# 1 に近ければ近いほど、リソースの使用状況が 80% 未満になっているかと。

SELECT 
    (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats

image

 

SQL Database のサービスレベルについてはポータルからだけでなく、 PowerShell から変更することが可能ですので、直近の情報をみて動的に変更するという手法が取れるかもしれないですね。

# 変更中は接続が一時的に切断される気がしますが。

Changing Database Service Tiers and Performance Levels

 

同様の情報を sys.resource_stats から取得する場合には、以下のようなクエリになるかと。

# 以下のクエリは日単位で集計しています。

 

SELECT 
	database_name
 	, sku
	, CONVERT(nvarchar, start_time,111)
	, (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
    , (COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
    , (COUNT(end_time) - SUM(CASE WHEN avg_physical_data_read_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM 
	sys.resource_stats
GROUP BY
	database_name, sku, CONVERT(nvarchar, start_time,111)

image

 

新しいサービスレベルの性能傾向については私も検証をしているのですが、使ってみた感じでは以下のリソースについてはレベルに応じて調整が行われているように感じています。

# 接続数とワーカースレッドについては具体的な値が Azure SQL データベースのサービス階層とパフォーマンス レベル で公開されています。公式な情報ではありませんが、Microsoft Azure SQL Database Performance Tests: Summary も参考になります。

  • 接続数
  • ワーカースレッド数
  • ログの書き込み速度
  • CPU の利用可能上限

avg_memory_percent も DMV から取得できるようなのでメモリについても制限がされている可能性がありますね。

# キャッシュヒット率等を見るのが難しいのでこの辺の検証は悩ましいですが。

sys.dm_db_resource_stats は sys.dm_db_wait_stats と組み合わせてみることも重要かと思います。

  • ログの書き込み速度 : WRITELOG
  • CPU の利用可能上限 : SOS_SCHEDULER_YIELD
  • メモリの使用状況 : PAGEIOLATCH_xx

から待ちの回数や状況を確認することができるかと思います。

接続数が上限に達して、接続ができない場合は、

Resource ID : 2. The session limit for the database is 300 and has been reached

ワーカースレッド数が上限に達して、ワーカースレッドを確保できない場合は、

Resource ID : 1. The request limit for the database is 30 and has been reached. See

というようなエラーがアプリ側に返るとおもいますので、この辺もトラッキングしておくとよいかと思います。

以前検証した時には、上記の接続の失敗が sys.database_connection_stats には表れていなかったような気がしますが、この辺は要検証ということで。

sys.dm_db_wait_stats については累計値が格納されていますので、定期的に値を取得して前回との差分を出すといったアプローチを考慮する必要がありますが。

DTU についてはもうちょっと研究したいと思います。

Written by masayuki.ozawa

9月 12th, 2014 at 8:17 am

Posted in SQL Database,SQL Server

Tagged with

Leave a Reply

*