リソース ガバナ（Resource Governor）によるリソース調整

SQL Server 2008

SQL Server 2008 からは、リソース ガバナ機能がサポートされました。これは、CPU 利用率やメモリ割り当て量といったリソースの調整が行える大変便利な機能です。これにより、CPU を占有するバッチ アプリケーションの CPU 利用率を制限したり、CPU を占有する SQL の CPU 利用率を制限したりするといったことが可能になります。


リソース ガバナは、次のように試すことができます。
まずは、［管理］フォルダで［リソース ガバナ］を右クリックして［新しいリソース プール］をクリックして新しいリソース プールを作成します。

［リソース ガバナのプロパティ］ダイアログが表示されたら、［リソース ガバナを有効にする］をチェックし、［リソース プール］の［名前］列に「testResourcePool」など、任意の名前を入力します。

次に、リソース プール内へワークロード グループを作成するために、引き続き同じダイアログで、作成した「testResourcePool」を選択して［リソースのワークロード グループ］セクションの［名前］列に「testWorkloadG1」および「testWorkloadG2」と入力して 2つのワークロード グループを作成します。


作成したリソース プールとワークロード グループを確認するために、次のように［管理］フォルダの［リソース ガバナ］を展開します。

次に、ユーザー定義分類子関数（Classification Function）を作成します。この関数は、通常のユーザー定義関数と同様、CREATE FUNCTION ステートメントを利用して次のように作成します。

USE master
go
CREATE FUNCTION［test分類関数］()
 RETURNS sysname 
 WITH SCHEMABINDING
AS
BEGIN
	DECLARE @grp_name AS sysname
	IF (SUSER_NAME() = 'testUser')
		SET @grp_name = 'testWorkloadG1'
	RETURN @grp_name
END

この関数では、testUser ユーザーの場合に @grp_name を 'testWorkloadG1' へ設定するようにしています。

次に、作成した分類子関数をリソース ガバナへ設定するために、次のように［リソース ガバナ］を右クリックして［プロパティ］をクリックします。


プロパティ画面では、［リソース ガバナを有効にする］をチェックして、［分類子関数の名前］で作成した分類子関数（test分類関数）を選択し、［OK］ボタンをクリックします。
以上でリソース ガバナの設定が完了です。


■ 負荷ワークロードの実行と監視
次に、負荷の高いクエリを実行して、リソース ガバナの効果を確認します。
まずは、「testUser」ログイン アカウントを次のように作成して、AdventureWorks データベースのユーザーとして設定し、db_owner ロールへ追加しておきます。

USE master
go

-- ログイン アカウントの作成
CREATE LOGIN testUser
 WITH PASSWORD = 'testUser123'
go

-- データベース ユーザーの作成
USE AdventureWorks
go
CREATE USER testUser
 FOR LOGIN testUser
go

-- db_owner ロールへの追加
EXEC sp_addrolemember 'db_owner', 'testUser'
go

次に、ワークロード状況を監視するために、［スタート］メニューの［管理ツール］から［パフォーマンス］をクリックして、システム モニタを起動します。
システム モニタでは、ツールバーの［+］ボタンをクリックして［カウンタの追加］ダイアログを表示し、［パフォーマンス オブジェクト］で「SQLServer: Workload Group Stats」を選択、カウンタの一覧から「CPU Usage %」（CPU 利用率）を選択し、インスタンスの一覧から「default」と「testWorkloadG1」を選択して［閉じる］ボタンをクリックします。

続いて、CPU 負荷の高いクエリ（以下）を、（testUser からではなく）sysadmin ロールのメンバーから（管理者アカウントから）実行します。

USE AdventureWorks
go
DECLARE @i int = 1
WHILE @i <= 100
BEGIN
   DBCC DROPCLEANBUFFERS  --　バッファのクリア
   SELECT * FROM
	Sales.SalesOrderDetail od
	INNER JOIN Sales.SalesOrderHeader oh
          ON od.SalesOrderID = oh.SalesOrderID
	INNER JOIN Production.Product p
          ON od.ProductID = p.ProductID
	INNER JOIN Production.ProductDocument pd
          ON p.ProductID = pd.ProductID

   DBCC DROPCLEANBUFFERS  --　バッファのクリア
   SELECT * FROM
	Sales.SalesOrderDetail od
	INNER JOIN Sales.SalesOrderHeader oh
          ON od.SalesOrderID = oh.SalesOrderID
	INNER JOIN Production.Product p
          ON od.ProductID = p.ProductID
	INNER JOIN Production.ProductDocument pd
          ON p.ProductID = pd.ProductID
	INNER JOIN Production.Document d
          ON pd.DocumentID = d.DocumentID
	WHERE  d.Title LIKE '%bbb%' OR d.FileName LIKE '%ccc%'
	   OR d.DocumentSummary LIKE '%ddd%'
           OR od.CarrierTrackingNumber LIKE '%sss%'
   SET @i += 1
END

このクエリの実行時間は、環境によって異なりますが、Virtual PC や Pentium M 1.2GHz の環境では約 3分、Core2 Quad 2.4GHz の環境では約 30秒かかります。クエリの実行が終わる前に、システム モニタへ戻り、CPU 利用率の状況を確認します。

sysadmin メンバーから実行されたクエリは、default ワークロード グループとして実行され、CPU 利用率が 100% 近くを推移していることを確認できます。

上記のクエリの実行が完了した後、今度は、testUser ログイン（ユーザー）でクエリ エディタへ接続し、同じクエリを実行します。

接続後にクエリを実行し、実行中にシステム モニタで CPU 利用率の状況を確認します。

testUser ユーザーから実行されたクエリは、testWorkloadG1 ワークロード グループとして実行されていることを確認できます。

次に、上記のクエリ（testUser ユーザーが実行したクエリ）の実行中に、sysadmin ロールのメンバーから同じクエリを実行し、そのときの状況をシステム モニタで確認します。

sysadmin のメンバーがクエリを実行すると、testUser が実行しているクエリと半分ずつの CPU 利用率（50%）を推移するようになり、CPU が均等に分配されていることを確認できます。

クエリの実行中に、リソース プールの調整（CPU 利用率の制限）を行います。sysadmin のメンバーでログインした接続をもう 1つ作成し、次のように ALTER RESOURCE POOL ステートメントを実行して testResourcePool の Max CPU Percent を 5 へ変更します。

ALTER RESOURCE POOL testResourcePool
 WITH ( max_cpu_percent = 5 )

ALTER RESOURCE GOVERNOR RECONFIGURE

リソース プールの調整後、システム モニタで状況を確認します。

default ワークロード グループは、95% 前後を推移するように変わり、testWorkloadG1 ワークロード グループは 5% 前後を推移するように変わることを確認できます。
なお、リソースプールの調整を GUI ベースで行いたい場合は、次のように操作します。


続いて、さらにリソース プールの調整 （CPU 利用率の制限）を行います。次のように ALTER RESOURCE POOL ステートメントを実行して testResourcePool の Max CPU Percent を 30 へ変更します。

ALTER RESOURCE POOL testResourcePool
 WITH ( max_cpu_percent = 30 )

ALTER RESOURCE GOVERNOR RECONFIGURE

リソース プールの調整後、システム モニタで状況を確認します。


default ワークロード グループは、70% 前後を推移するように変わり、testWorkloadG1 ワークロード グループは 30% 前後を推移するように変わることを確認できます。


なお、そのほかの SQL Server 2008 の新機能の具体的な利用方法については、弊社執筆の SQL Server 2008 自習書シリーズ（下記 URL）の「SQL Server 2008 の注目の新機能をイチ早く試してみよう! 」編を参考にしてみてください。
http://www.microsoft.com/japan/sqlserver/2008/self-learning/default.mspx