2010-02-25

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

SQL Server 2008 からの新機能である「リソースガバナ」で利用できる分類子関数は、ユーザーがSQL Server へ接続を確立する時（ログイン時）に実行される関数なので、利用できる関数が次の種類に限定されています。

HOST_NAME()
APP_NAME()
SUSER_NAME()
SUSER_SNAME()
IS_SRVROLEMEMBER()
IS_MEMBER()

この中で役立つのが APP_NAME 関数での振り分けです。この関数は、アプリケーション名を取得できる便利な関数なので、この関数を利用すればアプリケーションごとにワークロードグループを分けられるようになります。ADO や ADO.NET を利用している場合は、アプリケーション名を次のように接続文字列（Connection String）の Application Name で指定することができます。

'' ADO（WSH）の接続文字列の使用例（アプリケーション名を batch と指定）
Dim cn
cn = WScript.CreateObject("ADODB.Connection")
cn.Open("Provider=SQLOLEDB;" _
         & "Server=salt;" _
         & "Database=AdventureWorks;" _
         & "Application Name=batch;" _
         & "Integrated Security=SSPI;")

matu_tak’s blog

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

リソースガバナの分類子関数内で利用できる関数

リソースガバナ設定のスクリプト生成