リソース ガバナのワークロード管理のシナリオ

リソース ガバナは、SQL Server システムのワークロードの監視および管理のためにさまざまな方法で使用できます。ここでは、リソース ガバナの構成方法の概要と、リソース ガバナの使用方法の例を紹介します。ここで紹介するシナリオには、ワークロード グループとリソース プールの作成および変更のための Transact-SQL コードの例が含まれています。

リソース ガバナの構成

SQL Server 2008 をインストールすると、リソース ガバナが使用可能になりますが、有効にはなりません。既定では、内部および既定のワークロード グループと、それらに対応するリソース プールが存在します。

独自のリソース プールとワークロード グループを作成して使用するには、次の手順を完了する必要があります。

• 指定した制限を持つリソース プールを作成します。

• 指定した制限とポリシーを持つワークロード グループを作成し、そのワークロード グループが属するリソース プールを指定します。

• 受信要求に対して使用する分類関数を定義して、アクティブにします。

上の手順が完了すると、アクティブなリソース ガバナの構成と、分類されるすべてのアクティブな要求の状態を表示できるようになります。

リソース プールとワークロード グループの要件の特定

管理者の課題は、現在の SQL Server 環境に対して使用するワークロード グループとリソース プールの構成を決定することです。そのためには、アプリケーションによる SQL Server リソースの現在の使用状況を把握する必要があります。

この課題を解決するには、既定のリソース プールのワークロード グループを作成し、アプリケーションを数日間実行して、以下の問いに答えるための情報を収集します。

• CPU の要件は?
  1 要求あたりの総 CPU 使用率の平均値に 1 秒あたりの要求数の平均値を掛けて、CPU 帯域幅の推定値を特定します。

  CPU 使用率の最大値、平均値、および標準偏差を使用して、CPU 使用率の上限が必要かどうかを特定します。

• 同時要求の数は?
  同時要求数の最大値と平均値の統計を使用して、最小同時実行率を特定します。

• 必要なメモリの合計量は?
  使用されているメモリの合計量の最大値と平均値を使用して、メモリの要件を特定します。

• 1 つのクエリに必要なメモリの容量は?
  1 クエリあたりの使用メモリの最大値と平均値の統計を使用して、必要なメモリの容量を特定します。

• メモリ不足のために実行されていないクエリはあるか?
  メモリ キューで待機する時間の平均値の統計を使用して、メモリが使用できないためにブロックされているクエリがあるかどうかを特定します。

シナリオ

ここでは、7 つのシナリオを例として用意しました。シナリオ 1 ～ 5 は関連しており、それぞれが 1 つ前のシナリオに基づいています。シナリオ 6 はシナリオ 1 ～ 5 とは無関係で、シナリオ 7 はシナリオ 6 に基づいています。

シナリオ 1

Q: SQL Server の新しいバージョンのインストールが完了し、リソース ガバナを使用したいのですが、現在の環境で使用するにはどうすればよいでしょうか?

A: ワークロードによるリソースの消費を監視するためにリソース ガバナを使用することを検討してください。次の手順に従って、リソース ガバナの環境をセットアップします。構成の例も参考にしてください。

1. ワークロードのためのワークロード グループを作成します。

2. 分類関数を作成します。

3. 分類関数をリソース ガバナに登録します。

4. リソース ガバナを有効にします。

5. リソース ガバナのパフォーマンス カウンタを監視し、ワークロード グループのリソースの使用状況に関する情報を返す DMV に対してクエリを実行します。

例

注
次の構成では、ワークロード グループが使用するリソース プールが指定されていません。既定では、ワークロード グループは既定のプールを使用します。

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

シナリオ 2

Q: シナリオ 1 の監視の結果に基づいて、アドホック グループ (GroupAdhoc) のクエリの実行時間が 30 秒を超えるたびにイベントが発生するようにするにはどうすればよいでしょうか?

A: 次の手順に従って、現在のリソース ガバナの構成を変更します。構成の例も参考にしてください。

1. アドホック グループの CPU 使用率の制限を設定します。

2. SQL トレース イベント (リソース ガバナ管理クラス イベント) を監視します。

3. イベントの発生時に何らかの操作を実行します (イベントを無視する、電子メールを送信する、ページを送信する、要求に対して KILL コマンドを実行するなど)。

例

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

シナリオ 3

Q: アドホック グループにさらに制限を加えて、すべての要求の累積 CPU 使用率が 50% を超えないようにするにはどうすればよいでしょうか?

A: これまでのシナリオでは既定のプールが使用されているため、新しいリソース プールを作成する必要があります。次の手順に従って、現在のリソース ガバナの構成を変更します。構成の例も参考にしてください。

1. 新しいリソース プールを作成し、CPU の制限を構成します。

2. その新しいリソース プールが使用されるようにアドホック ワークロード グループを構成します。

例

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

シナリオ 4

Q: 管理グループが診断クエリを実行するためのリソースが常に確保されている状態を維持しながら、それらのクエリによって使用されるサーバーのメモリ リソースを 10% 以下に制限するにはどうすればよいでしょうか?

A: そのためには新しいリソース プールを作成する必要があります。次の手順に従って、現在のリソース ガバナの構成を変更します。構成の例も参考にしてください。

1. 新しいリソース プールを作成し、リソースの制限を設定します。

2. その新しいプールが使用されるように管理ワークロード グループを構成します。

例

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

シナリオ 5

Q: 現在レポートは既定のリソース プールにありますが、独立したワークロード グループ (GroupReports) を使用しています。既定のリソース グループのワークロードよりレポートの方がリソース使用の優先度が高くなるようにするにはどうすればよいでしょうか?

A: そのためには、既定のグループの IMPORTANCE の設定を構成する必要があります。

注
既定のグループの設定は変更できますが、既定のプールの設定は変更できません。既定のプールの変更が必要な場合は、新しいリソース プールを作成する必要があると考えられます。

次の手順に従って、現在のリソース ガバナの構成を変更します。構成の例も参考にしてください。

1. 既定のグループの設定を変更します。

例

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

シナリオ 6

Q: 既存のリソース プールを使用する新しいワークロード グループを作成し、そのグループが新しいリソース プールに移動されるように分類関数を変更するにはどうすればよいでしょうか?

A: 次の手順に従って、この新しいリソース ガバナ環境をセットアップします。構成の例も参考にしてください。

1. 既定の設定で新しいリソース プールを作成します。

2. 既存のプール内にある新しいワークロード グループを作成します。

3. 要求を処理するための新しい分類関数を作成して登録します。

例

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

シナリオ 7

Q: 不要になったワークロード グループ (シナリオ 6 で作成したワークロード グループ) と、そのワークロード グループを含むリソース プールを削除するにはどうすればよいでしょうか?

A: 次の手順に従って、リソース ガバナの環境を変更します。構成の例も参考にしてください。

1. 残すワークロード グループに要求を分類する新しい分類関数を作成して登録します。

2. ワークロード グループを削除します。

3. リソース プールを削除します。

4. 構成の変更を適用します。

例

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

関連項目

参照

リソース ガバナのカタログ ビュー (Transact-SQL)

リソース ガバナ関連の動的管理ビュー (Transact-SQL)

概念

リソース ガバナーの概念

リソース ガバナー DDL とシステム ビュー

リソース ガバナーを使用してバックアップの圧縮による CPU 使用率を制限する方法 (Transact-SQL)