SE の雑記

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

SQL Data Warehouse の DWU USED を取得するクエリを書いてみました

leave a comment

SQL Data Warehous をポータルで確認した際に、以下のような画像のタイルの情報を確認することができます。

image

DWU LIMIT と DWU USED という項目があるのですが、現状、簡単に取得する方法が見当たらないのですよね…。
Provide a way to expose the actual DWU usage (not the same as the scale setting). によると、今後のリリースで検討となっているのですが、現時点では実装されているかが微妙でして。

上記のフィードバックの実装の結果なのかもしれませんが、sys.resource_stats に SKU = ‘DW’という SKU の情報が取得できるようになっていました。

ということで、次のようなクエリを作成してみました。

このクエリは、1 論理サーバーに 1 つの SQL DW のデータベースが作成されている場合に、DWU の使用状況を取得するものになります。

SELECT
	CONVERT(datetime2(0), DATEADD(hh, 9, FORMAT(start_time, 'yyyy/MM/dd HH:mm'))) AS start_time_ja,
	CONVERT(datetime2(0), FORMAT(start_time, 'yyyy/MM/dd HH:mm')) AS start_time_utc,
	AVG(dwu_size) AS dwu_size,
	SUM(max_dwu_used) AS total_dwu_used,
	MAX(max_dwu_used) AS max_dwu_used,
	MAX(max_dist_storage_in_megabytes) AS max_dist_storage_in_megabytes,
	CONVERT(bigint, AVG(avg_dist_storage_in_megabytes)) AS avg_dist_storage_in_megabytes,
	CONVERT(bigint, SUM(total_storage_in_megabytes)) AS total_storage_in_megabytes,
	CONVERT(bigint, AVG(avg_cpu_percent)) AS avg_cpu_percent,
	CONVERT(bigint, MAX(avg_cpu_percent)) AS max_cpu_percent,
	CONVERT(bigint, AVG(avg_data_io_percent)) AS avg_data_io_percent,
	CONVERT(bigint, MAX(avg_data_io_percent)) AS max_data_io_percent,
	CONVERT(bigint, AVG(avg_log_write_percent)) AS avg_log_write_percent,
	CONVERT(bigint, MAX(avg_log_write_percent)) AS max_log_write_percent,
	MAX(max_worker_percent) AS max_worker_percent,
	MAX(max_session_percent) AS max_session_percent,
	AVG(compute_node_count) AS compute_node_count,
	SUM(data_count) AS data_count
FROM
(
SELECT 
	CONVERT(datetime2(1), start_time) AS start_time,
	MAX(storage_in_megabytes) AS max_dist_storage_in_megabytes,
	AVG(storage_in_megabytes) AS avg_dist_storage_in_megabytes,
	SUM(storage_in_megabytes) AS total_storage_in_megabytes,
	AVG(avg_cpu_percent) AS avg_cpu_percent,
	AVG(avg_data_io_percent) AS avg_data_io_percent,
	AVG(avg_log_write_percent) AS avg_log_write_percent,
	MAX(max_worker_percent) AS max_worker_percent,
	MAX(max_session_percent) AS max_session_percent,
	(SELECT MAX(v) FROM 
	 (VALUES 
	  (CONVERT(bigint, AVG(avg_cpu_percent))), 
	  (CONVERT(bigint, AVG(avg_data_io_percent))), 
	  (CONVERT(bigint, AVG(avg_log_write_percent)))
	 ) AS T(v)
	) AS max_dwu_used,
	CAST(AVG(dtu_limit) / 7.5 AS int) AS dwu_size,  -- https://docs.microsoft.com/ja-jp/azure/sql-data-warehouse/sql-data-warehouse-get-started-create-support-ticket
	CAST(AVG(dtu_limit) / 7.5 / 100 AS int) AS compute_node_count,
	COUNT(*) AS data_count
FROM 
	sys.resource_stats
WHERE
	sku = 'DW'
	AND
	database_name LIKE 'Distribution[_]%'
GROUP BY
	CONVERT(datetime2(1), start_time)
) AS T
GROUP BY
	FORMAT(start_time, 'yyyy/MM/dd HH:mm')
ORDER BY 
	FORMAT(start_time, 'yyyy/MM/dd HH:mm') DESC
GO

SELECT 
	* 
FROM(
SELECT 
	DATEADD(hh, 9, start_time) AS start_time_ja,
	start_time AS start_time_utc,
	CAST(AVG(dtu_limit) / 7.5 AS int) AS dwu_size,  -- https://docs.microsoft.com/ja-jp/azure/sql-data-warehouse/sql-data-warehouse-get-started-create-support-ticket
	(SELECT MAX(v) FROM 
	 (VALUES 
	  (CONVERT(bigint, AVG(avg_cpu_percent))), 
	  (CONVERT(bigint, AVG(avg_data_io_percent))), 
	  (CONVERT(bigint, AVG(avg_log_write_percent)))
	 ) AS T(v)
	) AS max_dwu_used,
	MAX(storage_in_megabytes) AS max_dist_storage_in_megabytes,
	CONVERT(bigint, AVG(storage_in_megabytes)) AS avg_dist_storage_in_megabytes,
	SUM(storage_in_megabytes) AS total_storage_in_megabytes,
	CONVERT(bigint, AVG(avg_cpu_percent)) AS avg_cpu_percent,
	CONVERT(bigint, AVG(avg_data_io_percent)) AS avg_data_io_percent,
	CONVERT(bigint, AVG(avg_log_write_percent)) AS avg_log_write_percent,
	MAX(max_worker_percent) AS max_worker_percent,
	MAX(max_session_percent) AS max_session_percent,
	CAST(AVG(dtu_limit) / 7.5 / 100 AS int) AS compute_node_count,
	COUNT(*) AS data_count
FROM 
	sys.resource_stats
WHERE
	sku = 'DW'
	AND
	database_name LIKE 'Distribution[_]%'
GROUP BY
	start_time
) AS T
WHERE
	data_count <> 60 / compute_node_count
ORDER BY 
	start_time_ja DESC

 

image

image

現時点の SKU = ‘DW’ の情報ですが、ディストリビューション + 管理用のデータベースの情報が取得できるようです。

しかし、「どのデータベースのディストリビューションか」というものについては取得することができないようで、論理サーバー内に複数の SQLDW の情報が存在している場合は、想定しているデータが取得できないかと思います。

回りくどい集計を実施しているのですがこれは、

  • ストレージサイズについては、ディストリビューション単位の使用状況が取得できる
  • ディストリビューションの情報については、接続しているコンピュートノードの情報が出力されており、複数のディストリビューションが接続されている場合、CPU 等の使用状況は同一の情報が複数出力される

というような対策となっています。

300 DWU までは確認してみたのですが、ポータルとの情報の差は多少あるのですが、似たような情報は取得できているのではないでしょうか。

SQL DW の場合、DMV の DTU のリミットの情報は 7.5 倍されたものが出力されているようですので、その編集も実施しています。

SQL Data Warehouse のサポート チケットを作成する方法

実際に必要な DTU を計算するには、必要とされる DWU の合計に 7.5 を掛けます。

情報の出力内容が、かなり暫定的なもののようにも見えるため、どこまで使用できるかは不明ですが、参考となる情報としては取得できるのではないでしょうか。

クエリに誤りがありましたら DWU USED の取得.sql にフィードバックいただけると幸いです m(_ _)m

Written by masayuki.ozawa

9月 13th, 2017 at 10:25 pm

Leave a Reply

*