SE の雑記

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

SQL Server 2014 CTP 1 の新機能を使ってみる – リソースガバナーの新機能 –

leave a comment

SQL Server 2014 CTP1 の新機能を少しずつ使ってみたいと思います。

1 回目の今回の投稿ではあえて Hekaton ではなくリソースガバナーの新機能のご紹介を。
BOL としては、 ALTER RESOURCE POOL (Transact-SQL) に記載されています。

SQL Server 2014 のリソースガバナーでは、以下の 2 種類のパラメーターが設定できるようになっています。

  • MIN_IOPS_PER_VOLUME
  • MAX_IOPS_PER_VOLUME

このパラメーターですが、現状 GUI から設定することはできないようでクエリを実行して変更する必要があります。
# CAP_CPU_PERCENT も GUI の設定は入っていないようですね。
image

MIN_IOPS_PER_VOLUME =value

Applies to: SQL Server 2014 through SQL Server 2014.

Specifies a value for the minimum I/O operations per second (IOPS) per disk volume for workloads and groups of workloads.

MAX_IOPS_PER_VOLUME =value

Applies to: SQL Server 2014 through SQL Server 2014.

Specifies a value for the maximum I/O operations per second (IOPS) per disk volume for workloads and groups of workloads.

SQL Server 2014 のリソースガバナーでは新機能として IOPS が調整できるようになっています。
以下のようにリソースプールに対して IOPS を設定します。
# 今回はテストのため default に設定していますが本来は新規にプールを作成したほうが良いです。

ALTER RESOURCE POOL [default]
WITH
(MAX_IOPS_PER_VOLUME = 1000,
MIN_IOPS_PER_VOLUME  = 100)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

これに伴い [SQL Server:Resource Pool Stats] にディスク IO 関連のカウンタが追加されています。
imageimage

テストとして以下のようなクエリを実行しています。

SET STATISTICS TIME OFF
SET NOCOUNT ON
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
SET STATISTICS TIME ON
DECLARE @i bigint
SELECT @i = COUNT (*) FROM Table_1
SET STATISTICS TIME OFF
GO 5

それでは実際に IOPS を制限してみたいと思います。
まずは IOPS を制限していない状態でベースラインを取得してみます。

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 15 ms.
Beginning execution loop

SQL Server Execution Times:
   CPU time = 250 ms,  elapsed time = 538 ms.

SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 363 ms.

SQL Server Execution Times:
   CPU time = 314 ms,  elapsed time = 227 ms.

SQL Server Execution Times:
   CPU time = 93 ms,  elapsed time = 188 ms.

SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 169 ms.
Batch execution completed 5 times.

次に以下のクエリを実行してリソースガバナーで IOPS の上限を設定し、再度クエリを実行してみます。

ALTER RESOURCE POOL [default]
WITH
(MAX_IOPS_PER_VOLUME = 100,
MIN_IOPS_PER_VOLUME  = 0)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

実行結果がこちらです。

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Beginning execution loop

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 1850 ms.

SQL Server Execution Times:
   CPU time = 46 ms,  elapsed time = 1854 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1894 ms.

SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 1909 ms.

SQL Server Execution Times:
   CPU time = 32 ms,  elapsed time = 1867 ms.
Batch execution completed 5 times.

クエリの実行完了までの時間が長くなっていることが確認できますね。
MAXIOPS を 2000 にした場合は以下のようになりました。

Beginning execution loop

SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 446 ms.

SQL Server Execution Times:
   CPU time = 219 ms,  elapsed time = 295 ms.

SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 282 ms.

SQL Server Execution Times:
   CPU time = 188 ms,  elapsed time = 301 ms.

SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 244 ms.
Batch execution completed 5 times.

設定を変更することで IOPS が変わり処理時間に影響を与えています。
パフォーマンスモニターからも IOPS が変わっていることが確認できます。

以下は [LigicalDiskDisk Transfers/sec] の内容になります。
IOPS を最初は 50 に設定し、途中で 80 に設定した状態がこちらのグラフになります。
image

スケールを 1.0 にしているので左の軸と Transfers / sec は同一の値になります。
リソースガバナーで設定した通りの値で推移していますね。

今回は SELECT でテストをしていますので [SQLServer:Resource Pool Stats] の読み取りに関しての項目でも同様のトレンドを示しています。

image

書き込みの IOPS の制限は BCP を使用した一括挿入でテストをするとよさそうです。
BCP を使用した一括書き込みで以下のようにリソースガバナーにより書き込みの IOPS が制限されていることが確認できます。
image

IOPS の制限を解除したい場合には以下のように 0 を設定するとよさそうです。

ALTER RESOURCE POOL [default]
WITH
(MAX_IOPS_PER_VOLUME = 0,
MIN_IOPS_PER_VOLUME  = 0)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

バッチ処理とオンライン処理を並列で実行した場合などには IOPS を制限することでディスク負荷を分散させることは有効になりそうですね。
# バックアップ時の IOPS 調整に使ってもよさそうです。

Share

Written by Masayuki.Ozawa

6月 28th, 2013 at 12:00 am

Posted in SQL Server

Tagged with ,

«
»

Leave a Reply