SE の雑記

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

SQL Database v12 のデータベースのアクセス状況を取得する

leave a comment

SQL Database v12 のデータベースのアクセス状況の取得方法について軽く。

今回の投稿で書いているクエリは各 DB を選択している状態で実行する必要があります。
master データベースだと想定している値が取得できないかと。

一般的な方法としては、fn_virtualfilestats を使用して取得する方法があるかと思います。

私がよく使用するクエリとしては、以下のようなものとなります。

SELECT
	[database_files].[name],
	[database_files].[type_desc],
	[database_files].[size] * 8.0 AS size,
	[database_files].[max_size] * 8.0 AS max_size_KB,
	[database_files].[growth],
	CASE [is_percent_growth]
		WHEN 0 THEN [database_files].[growth] * 8.0
		ELSE [growth]
	END AS [converted_growth],
	[database_files].[is_percent_growth],
	[fn_virtualfilestats].[NumberReads],
	[fn_virtualfilestats].[IoStallReadMS],
	[fn_virtualfilestats].[BytesRead],
	[fn_virtualfilestats].[NumberWrites],
	[fn_virtualfilestats].[IoStallWriteMS],
	[fn_virtualfilestats].[BytesWritten],
	[fn_virtualfilestats].[BytesOnDisk]
FROM
	fn_virtualfilestats(DB_ID(), NULL)
	LEFT JOIN
	sys.database_files
	ON
	database_files.file_id  = fn_virtualfilestats.FileId
OPTION (RECOMPILE)
GO

 

このクエリで取得できる情報は以下のようなものとなり、データベースのファイル毎にどの程度の I/O が発生しているかを取得することができます。

image

Read と Write の状況を取得する場合は、以下のようなクエリでしょうか。

SELECT
	SUM([fn_virtualfilestats].[BytesRead]) AS [BytesRead],
	SUM([fn_virtualfilestats].[BytesWritten]) AS [BytesWritten]
FROM
	fn_virtualfilestats(DB_ID(), NULL)
	LEFT JOIN
	sys.database_files
	ON
	database_files.file_id  = fn_virtualfilestats.FileId
OPTION (RECOMPILE)
GO

 

image

ここまでは一般的な内容かと思いますが、最近気づいたものとしては、sys.dm_os_performance_counters から取得する方法です。

この DMV からも色々な観点で取得できるかと思いますが、最近面白いと思ったのは、HTTP Storage から取得する方法です。

このカウンターは、SQL Server 2014 から追加されたもので、Azure の BLOB ストレージにデータベースのファイルを直接配置した場合に有効な値が取得できるものとなります。

SQL Database v12 でもこの方式は使用されているようで、HTTP ストレージのカウンターから情報を取得することができるようです。

縦持ちのデータを横持ちに変換するため、微妙なクエリを実行してしまっていますが、以下のような SQL で情報を取得できます。

# PIVOT 使えばもう少しきれいになるかな…。

SELECT
	RTRIM(t1.instance_name) AS instance_name,
	t2.cntr_value AS [Read Bytes/sec],
	t3.cntr_value AS [Write Bytes/sec],
	t4.cntr_value AS [Transfers/Sec],
	CASE t6.cntr_value
		WHEN 0 THEN 0
		ELSE t5.cntr_value / t6.cntr_value
	END AS [Avg. Bytes/Read],
	CASE t8.cntr_value
		WHEN 0 THEN 0
		ELSE t7.cntr_value / t8.cntr_value
	END AS [Avg. Bytes/Write],
	CASE t10.cntr_value
		WHEN 0 THEN 0
		ELSE t9.cntr_value / t10.cntr_value
	END AS [Avg. microsec/Read],
	CASE t12.cntr_value
		WHEN 0 THEN 0
		ELSE t11.cntr_value / t12.cntr_value
	END AS [Avg. microsec/Write]
FROM
	sys.dm_os_performance_counters t1
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Read Bytes/sec') AS t2
	ON t1.instance_name = t2.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Write Bytes/sec') AS t3
	ON t1.instance_name = t3.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Transfers/Sec') AS t4
	ON t1.instance_name = t4.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Avg. Bytes/Read') AS t5
	ON t1.instance_name = t5.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Avg. Bytes/Read BASE') AS t6
	ON t1.instance_name = t6.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Avg. Bytes/Write') AS t7
	ON t1.instance_name = t7.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Avg. Bytes/Write BASE') AS t8
	ON t1.instance_name = t8.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Avg. microsec/Read') AS t9
	ON t1.instance_name = t9.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Avg. microsec/Read BASE') AS t10
	ON t1.instance_name = t10.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Avg. microsec/Write') AS t11
	ON t1.instance_name = t11.instance_name
	INNER JOIN
	(SELECT	instance_name,cntr_value
	FROM sys.dm_os_performance_counters
	WHERE counter_name = 'Avg. microsec/Write BASE') AS t12
	ON t1.instance_name = t12.instance_name
WHERE
	t1.object_name LIKE '%HTTP Storage%'
	AND t1.instance_name <> '_Total'
GROUP BY
	t1.instance_name,
	t2.cntr_value,
	t3.cntr_value,
	t4.cntr_value,
	t5.cntr_value,
	t6.cntr_value,
	t7.cntr_value,
	t8.cntr_value,
	t9.cntr_value,
	t10.cntr_value,
	t11.cntr_value,
	t12.cntr_value
ORDER BY
	t1.instance_name
OPTION (RECOMPILE)

最初に紹介した、fn_virtualfilestats を使用したクエリの合計の読み込み / 書き込みを取得するクエリでは以下のような情報が取得できます。

image

同一のタイミングで、HTTP ストレージの情報を取得したものがこちらです。

image

今回の環境であれば「wasd2prodjawe1ars24.blob.core.windows.net」というストレージに対しての Read / Write が同一の値となっていることが確認できます。

SQL Database の DB は BLOB ストレージへの直接配置の方法を使用して、コンピュートとストレージを切り離した構成となっているのかもしれないですね。

# メインの DB を配置するストレージ以外にもいくつかの BLOB ストレージをアタッチしているようですが。

SQL Database のデータベースのアクセスに関して、どのようなディスク負荷となっているかを確認したい場合、この辺の情報を元にするとよいかと。

Share

Written by Masayuki.Ozawa

9月 11th, 2016 at 11:45 am

Posted in SQL Database

Tagged with

«
»

Leave a Reply