SE の雑記

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

Predictable Performance から見るパフォーマンスレベルに応じたリソースの調整

leave a comment

 ぺんぺん師匠Azure SQL Database Benchmark (ベンチマーク)の詳細説明動画が公開!という記事で紹介されている動画のシリーズで面白いものがあったので、それに倣ってパフォーマンスレベルのリソース調整について少し。

Azure SQL Database for Business-Critical Cloud ApplicationsAzure SQL Database for Business-Critical Cloud Applications: (03) Predictable Performance を参考にさせていただいています。

この動画は DTU の予測可能なパフォーマンスについての解説をさせれているものになるのですが、その中で個人的にイメージしやすいな~と思ったものに以下のスライドがありました。
image

image

Web / Business とは異なり、パフォーマンスレベルに応じてリソースが制御され、調整機構 (Governor) によって、処理のスループットが変わることがあらわされています。

33:15 ぐらいからデモが行われているのですがそこでは以下のようなクエリを使用して、S2 と P1 の処理時間の差を出していました。

画面では途中までしかクエリが表示されていなかったので、多少内容は違っているかもしれませんが、S2 と P1 のデータベースに以下の関数を作成します。

この関数は計算のみが行われ、単純に CPU のリソースが使用されるものになります。

IF (OBJECT_ID('dbo.fn_perf_test') IS NOT NULL)
	DROP FUNCTION dbo.fn_perf_test
GO

CREATE FUNCTION [dbo].[fn_perf_test]()
RETURNS INT AS
BEGIN
	DECLARE @start DATETIME2(6) = CURRENT_TIMESTAMP
	DECLARE @i int
	DECLARE @f float
	SET @i = 1
	WHILE (@i < 1000000)
	BEGIN
		SET @f = (@i * 0.99999) + (@i * 0.9999) - (@i * 0.9999) + (@i * 0.999999) - (@i * 0.99999)
		SET @i = @i + 1
	END
	RETURN DATEDIFF(ms, @start, CURRENT_TIMESTAMP)
END
GO

この関数を以下のようなクエリで S2 と P1 に対して実行して結果を取得しています。

SET NOCOUNT ON
DECLARE @t1 INT, @t2 INT, @t3 INT, @t4 INT, @t5 INT
EXEC @t1 = dbo.fn_perf_test
EXEC @t2 = dbo.fn_perf_test
EXEC @t3 = dbo.fn_perf_test
EXEC @t4 = dbo.fn_perf_test
EXEC @t5 = dbo.fn_perf_test

SELECT @t1 AS test_run_1
	, @t2 AS test_run_2
	, @t3 AS test_run_3
	, @t4 AS test_run_4
	, @t5 AS test_run_5
GO

 

下が動画で紹介されていた S2 / P1 の実行結果となります。

S2 は  50 DTU / P1 は 100 DTU となり、スループットユニットのサイズが 2 倍違います。

処理時間についても大体 2 倍程度の差が出ていますね。

imageimage

Basic と S1 で比較して見た結果が以下になります。

image

それでは、この差が何によって発生しているかを考えてみたいと思います。

先日の SQL Server 収穫祭のフォローアップにもなるのですが、DTU に CPU の処理性能が異なってきます。

この処理性能の違いが処理時間に影響を与えるのですが、待ち事象としては SOS_SCHEDULER_YIELD として表れていると考えています。

先ほどの関数では計算を行うクエリだけが記述されていましたが、今回は待ち事象の情報を追加したいので、以下の関数を作成してみます。

この関数は処理前後の SOS_SCHEDULER_YIELD の値を取得するものになります。


IF (OBJECT_ID('dbo.fn_perf_test2') IS NOT NULL)
	DROP FUNCTION dbo.fn_perf_test2
GO

CREATE FUNCTION fn_perf_test2()
RETURNS @Result TABLE(
ProcTime int,
WaitCount int,
WaitTime int,
AvgWaitTime int)
AS
BEGIN
	DECLARE @start DATETIME2(6) = CURRENT_TIMESTAMP
	DECLARE @i int
	DECLARE @f float
	SET @i = 1

	DECLARE @BeforeWaitCount int, @BeforeWaitTime int, @AfterWaitCount int,@AfterWaitTime int
	SELECT @BeforeWaitCount = waiting_tasks_count ,@BeforeWaitTime = wait_time_ms 
	FROM sys.dm_db_wait_stats 
	WHERE wait_type = 'SOS_SCHEDULER_YIELD'

	WHILE (@i < 1000000)
	BEGIN
		SET @f = (@i * 0.99999) + (@i * 0.9999) - (@i * 0.9999) + (@i * 0.999999) - (@i * 0.99999)
		SET @i = @i + 1
	END
	
	SELECT @AfterWaitCount = waiting_tasks_count ,@AfterWaitTime = wait_time_ms 
	FROM sys.dm_db_wait_stats 
	WHERE wait_type = 'SOS_SCHEDULER_YIELD'

	INSERT INTO @Result 
	VALUES(DATEDIFF(ms, @start, CURRENT_TIMESTAMP), @AfterWaitCount - @BeforeWaitCount, 
	@AfterWaitTime - @BeforeWaitTime, 
	(@AfterWaitTime - @BeforeWaitTime) / (@AfterWaitCount - @BeforeWaitCount))
	
	RETURN 
END

この関数を以下のクエリから実行してみます。

SELECT * FROM [dbo].[fn_perf_test2]()
SELECT * FROM [dbo].[fn_perf_test2]()
SELECT * FROM [dbo].[fn_perf_test2]()
SELECT * FROM [dbo].[fn_perf_test2]()
SELECT * FROM [dbo].[fn_perf_test2]()

 

今回は登録済みサーバーから複数のデータベースたいしてクエリを実行していますので、各結果に対して 2 行 (登録済みサーバーに登録されている環境分) の情報が取得されます。

image

Basic : 5DTU / S1 : 20 DTU ですので DTU 数としては 4 倍の差があります。

Basic で平均して 39 秒程度、S1 で平均して 12 秒程度ですので 4 倍近くの差が出ていますね。

この時の SOS_SCHEDULER_YIELD の差が Waitxx で取得しているのですが、[WaiTime] の差が顕著に出ているかと思います。この時間は処理時間に近いものとなっています。

平均的な待ち時間も異なっていますので、DTU によって SOS_SCHEDULER_YIELD に影響を与えていそうですね。

オンプレミスの SQL Server でも SQL Server 2012 以降のリソースガバナーで実装された CAP_CPU_PERCENT を設定することで同様な動作を実現することができます。

# 自宅の検証環境で 15% / 2% で設定した結果になります。

image

CAP_CPU_PERCENT を使用した場合も SOS_SCHEDULER_YIELD に差が出ますので、CPU リソースの制御の動作は同じのようですね。

15% に設定した場合は平均 19 秒、2% に設定した場合は、平均 88 秒なのでトレンドとしても SQL Database 相当には制御できていそうです。

image

SQL Database のリソースの制御状況は以下の待ち事象に注目するとよいのかなと。

CPU SOS_SCHEDULER_YIELD
ディスクからの読み取り PAGEIO_LATCH_xx

IO_QUEUE_LIMIT
ログの書き込み LOG_RATE_GOVERNOR


これらの待ち事象が以下に影響してくるかと思います。

image

Written by masayuki.ozawa

10月 9th, 2014 at 2:39 pm

Posted in SQL Database

Tagged with

Leave a Reply

*