ぺんぺん師匠 が Azure SQL Database Benchmark (ベンチマーク)の詳細説明動画が公開！という記事で紹介されている動画のシリーズで面白いものがあったので、それに倣ってパフォーマンスレベルのリソース調整について少し。
Azure SQL Database for Business-Critical Cloud Applications の Azure SQL Database for Business-Critical Cloud Applications: (03) Predictable Performance を参考にさせていただいています。
この動画は DTU の予測可能なパフォーマンスについての解説をさせれているものになるのですが、その中で個人的にイメージしやすいな～と思ったものに以下のスライドがありました。 
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 倍程度の差が出ていますね。
Basic と S1 で比較して見た結果が以下になります。
それでは、この差が何によって発生しているかを考えてみたいと思います。
先日の 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 行 (登録済みサーバーに登録されている環境分) の情報が取得されます。
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% で設定した結果になります。
CAP_CPU_PERCENT を使用した場合も SOS_SCHEDULER_YIELD に差が出ますので、CPU リソースの制御の動作は同じのようですね。
15% に設定した場合は平均 19 秒、2% に設定した場合は、平均 88 秒なのでトレンドとしても SQL Database 相当には制御できていそうです。
SQL Database のリソースの制御状況は以下の待ち事象に注目するとよいのかなと。
| CPU | SOS_SCHEDULER_YIELD |
| ディスクからの読み取り | PAGEIO_LATCH_xx IO_QUEUE_LIMIT |
| ログの書き込み | LOG_RATE_GOVERNOR |
これらの待ち事象が以下に影響してくるかと思います。
