SE の雑記

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

SQL Database のリソース使用状況を DTU 以外の観点で取得してみる

one comment

SQL Database では、DTU の使用状況で、リソースの使用状況を確認することができますが、それ以外の方法でも取得することができそうだったので、軽くメモを。

以下のようなクエリで、DTU のような単位ではなく、CPU 使用状況やディスクのアクセス状況を確認することができるかと思います。


SELECT
	instance_name,
	CAST(CAST([CPU usage %] AS float) / CAST([CPU usage % base] AS float) * 100  AS int) AS [CPU Usage],
	[Max memory (KB)] / 1024 AS [Max memory (MB)],
	[Used memory (KB)] / 1024 AS [Used memory (KB)] ,
	[Target memory (KB)] / 1024 AS [Target memory (MB)] ,
	[Disk Read IO/sec],
	[Disk Read IO Throttled/sec],
	[Disk Read Bytes/sec] / POWER(1024, 2) AS [Disk Read MB/sec] ,
	[Disk Write IO/sec],
	[Disk Write IO Throttled/sec],
	[Disk Write Bytes/sec] / POWER(1024, 2) AS [Disk Write MB/sec]
FROM
	(
	SELECT
		RTRIM(instance_name) AS instance_name,
		RTRIM(counter_name) AS counter_name,
		cntr_value
	FROM 
		sys.dm_os_performance_counters
	WHERE 
		object_name like '%Resource Pool Stats%'
		AND
		counter_name IN (
		'CPU usage %', 
		'CPU usage % base',
		'Max memory (KB)',
		'Used memory (KB)',
		'Target memory (KB)',
		'Disk Read IO/sec',
		'Disk Read IO Throttled/sec',
		'Disk Read Bytes/sec',
		'Disk Write IO/sec',
		'Disk Write IO Throttled/sec',
		'Disk Write Bytes/sec'
		)
	) AS T
PIVOT
(
	SUM(cntr_value)
	FOR counter_name 
	IN( 
		[CPU usage %],
		[CPU usage % base],
		[Max memory (KB)],
		[Used memory (KB)],
		[Target memory (KB)],
		[Disk Read IO/sec],
		[Disk Read IO Throttled/sec],
		[Disk Read Bytes/sec],
		[Disk Write IO/sec],
		[Disk Write IO Throttled/sec],
		[Disk Write Bytes/sec]
	)
) AS PVT
ORDER BY
	instance_name ASC
    

ディスクのアクセス状況については以下のようなクエリでも取得可能です。

-- IOPS
DECLARE @previous_time datetime, @current_time datetime
DECLARE @previous_read_count bigint, @current_read_count bigint
DECLARE @previous_write_count bigint, @current_write_count bigint

SELECT
	@previous_time = GETDATE(),
	@previous_read_count = SUM([fn_virtualfilestats].[NumberReads]),
	@previous_write_count = SUM([fn_virtualfilestats].[NumberWrites])
FROM
	fn_virtualfilestats(NULL, NULL)

WAITFOR DELAY '00:00:01'

SELECT
	@current_time = GETDATE(),
	@current_read_count = SUM([fn_virtualfilestats].[NumberReads]),
	@current_write_count = SUM([fn_virtualfilestats].[NumberWrites])
FROM
	fn_virtualfilestats(NULL, NULL)
SELECT
	CAST((@current_read_count - @previous_read_count ) / (DATEDIFF(ms, @previous_time, @current_time) / 1000.0) AS bigint) AS NumberReads,
	CAST((@current_write_count - @previous_write_count ) / (DATEDIFF(ms, @previous_time, @current_time) / 1000.0) AS bigint) AS NumberWrites

-- スループット
DECLARE @previous_time datetime, @current_time datetime
DECLARE @previous_readbyte bigint, @current_readbyte bigint
DECLARE @previous_writebyte bigint, @current_writebyte bigint

SELECT
	@previous_time = GETDATE(),
	@previous_readbyte = SUM([fn_virtualfilestats].[BytesRead]),
	@previous_writebyte = SUM([fn_virtualfilestats].[BytesWritten])
FROM
	fn_virtualfilestats(NULL, NULL)

WAITFOR DELAY '00:00:01'

SELECT
	@current_time = GETDATE(),
	@current_readbyte = SUM([fn_virtualfilestats].[BytesRead]),
	@current_writebyte = SUM([fn_virtualfilestats].[BytesWritten])
FROM
	fn_virtualfilestats(NULL, NULL)

SELECT
	CAST((@current_readbyte - @previous_readbyte ) / (DATEDIFF(ms, @previous_time, @current_time) / 1000.0) AS bigint) / POWER(1024,2) AS [MBytesRead],
	CAST((@current_writebyte - @previous_writebyte ) / (DATEDIFF(ms, @previous_time, @current_time) / 1000.0) AS bigint) / POWER(1024,2) AS [MBytesWritten]

 

この辺の情報を取得することで、以下のような形で情報を確認することができるかと。

image

Written by masayuki.ozawa

2月 5th, 2017 at 10:57 pm

Posted in SQL Database

Tagged with

One Response to 'SQL Database のリソース使用状況を DTU 以外の観点で取得してみる'

Subscribe to comments with RSS or TrackBack to 'SQL Database のリソース使用状況を DTU 以外の観点で取得してみる'.

  1. […] SQL Database のリソース使用状況を DTU 以外の観点で取得してみる […]

Leave a Reply

*