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

既定の設定では、圧縮を使用してバックアップを行うと CPU 使用率が著しく増加し、圧縮処理に CPU が追加で消費されるために、同時に実行中の操作が悪影響を受ける可能性があります。このため、CPU の競合が発生したときは、リソース ガバナーによって CPU 使用率が制限されるセッションで、優先度の低い圧縮されたバックアップの作成が求められる場合があります。このトピックでは、このような場合に CPU 使用率を制限するリソース ガバナー ワークロード グループに、特定の SQL Server ユーザーのセッションをマップしてそれらのセッションを分類するシナリオを示します。

重要
所定のリソース ガバナーのシナリオでは、セッションの分類が、ユーザー名、アプリケーション名、または接続を区別できるその他の要素に基づいて行われます。詳細については、「分類関数の記述に関する注意点」および「リソース ガバナのワークロード管理のシナリオ」を参照してください。

このトピックでは、一連のシナリオを以下の順序で取り上げます。

1. 優先度の低い操作を行うためのログインとユーザーの設定

2. CPU 使用率を制限するためのリソース ガバナーの構成

3. 現在のセッションの分類の確認 (Transact-SQL)

4. CPU が制限されているセッションを使用したバックアップの圧縮

優先度の低い操作を行うためのログインとユーザーの設定

このトピックのシナリオには、優先度の低い SQL Server ログインおよびユーザーが必要です。ユーザー名は、このログインで実行されるセッションを分類し、CPU 使用率を制限するリソース ガバナー ワークロード グループにセッションをルーティングするために使用されます。

以下では、この目的でログインとユーザーを設定する手順について説明した後に、Transact-SQL の例「例 A : ログインとユーザーの設定 (Transact-SQL)」を示します。

セッションを分類するためにログインとデータベース ユーザーを設定するには

1. 優先度の低い圧縮されたバックアップを作成するための SQL Server ログインを作成します。

   ログインを作成するには

   • SQL Server ログインを作成する方法

   • CREATE LOGIN (Transact-SQL)

2. 必要に応じて、このログインに VIEW SERVER STATE 権限を付与します。

   • GRANT (システム オブジェクトの権限の許可) (Transact-SQL)

   詳細については、「固定サーバー ロールの権限 (データベース エンジン)」を参照してください。

3. このログインに対して SQL Server ユーザーを作成します。

   ユーザーを作成するには

   • データベース ユーザーを作成する方法

   • CREATE USER (Transact-SQL)

4. このログインおよびユーザーのセッションで特定のデータベースをバックアップできるようにするには、対象となるデータベースの db_backupoperator データベース ロールにこのユーザーを追加します。この作業は、このユーザーがバックアップするデータベースごとに行います。必要に応じて、このユーザーを他の固定データベース ロールに追加します。

   固定データベース ロールにユーザーを追加するには

   • sp_addrolemember (Transact-SQL)

   詳細については、「固定データベース ロールの権限 (データベース エンジン)」を参照してください。

例 A : ログインとユーザーの設定 (Transact-SQL)

次の例は、優先度の低いバックアップ用に新しい SQL Server ログインおよびユーザーを作成する場合にのみ該当します。既存のログインとユーザーで適切なものがあれば、それを使用してもかまいません。

重要
次の例では、サンプルのログインとユーザー名 domain_name\MAX_CPU を使用しています。この名前を、優先度の低い圧縮されたバックアップを作成する際に使用する予定の SQL Server ログインおよびユーザーの名前に置き換えてください。

この例では、Windows アカウント domain_name\MAX_CPU にログインを作成し、このログインに VIEW SERVER STATE 権限を付与します。この権限によって、リソース ガバナーでログインのセッションがどのように分類されるかを確認できます。次にこの例では、domain_name\MAX_CPU のユーザーを作成し、このユーザーを AdventureWorks2008R2 サンプル データベースの db_backupoperator 固定データベース ロールに追加します。このユーザー名は、リソース ガバナーの分類関数で使用されます。

-- Create a SQL Server login for low-priority operations
USE master;
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];
GO
-- Create a SQL Server user in AdventureWorks2008R2 for this login
USE AdventureWorks2008R2;
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';
GO

-- Create a SQL Server login for low-priority operations
USE master;
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];
GO
-- Create a SQL Server user in AdventureWorks2008R2 for this login
USE AdventureWorks2008R2;
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';
GO

[先頭に戻る]

CPU 使用率を制限するためのリソース ガバナーの構成

注
リソース ガバナーが有効になっていることを確認してください。詳細については、「リソース ガバナを有効または無効にする方法 (SQL Server Management Studio)」を参照してください。

このリソース ガバナーのシナリオでは、次の基本的な手順で構成が行われます。

1. リソース ガバナーのリソース プールを作成し、CPU の競合が発生したときにリソース プール内の要求に割り当てられる最大平均 CPU 帯域幅を制限するように構成します。

2. このプールを使用するリソース ガバナー ワークロード グループを作成して構成します。

3. ユーザー定義関数 (UDF) である分類関数を作成します。リソース ガバナーは、この関数の戻り値を使用してセッションを分類し、適切なワークロード グループにセッションがルーティングされるようにします。

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

5. リソース ガバナーのメモリ内の構成に変更を適用します。

注
リソース ガバナーのリソース プール、ワークロード グループ、および分類の詳細については、「リソース ガバナーの概念」を参照してください。

上記の手順で使用する Transact-SQL ステートメントについては、「CPU 使用率を制限するようにリソース ガバナーを構成するには」で説明し、その後に Transact-SQL の例を示します。

リソース ガバナーを構成するには (SQL Server Management Studio)

• テンプレートを使用してリソース ガバナを構成する方法 (SQL Server Management Studio)

• リソース プールを作成する方法 (SQL Server Management Studio)

• ワークロード グループを作成する方法 (SQL Server Management Studio)

CPU 使用率を制限するようにリソース ガバナーを構成するには (Transact-SQL)

1. CREATE RESOURCE POOL ステートメントを実行してリソース プールを作成します。この手順の例では、次の構文を使用します。

   CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

   Value は、最大平均 CPU 帯域幅の割合を示す 1 ～ 100 の整数です。適切な値は環境によって異なります。わかりやすいように、このトピックの例では 20% (MAX_CPU_PERCENT = 20) を使用します。

2. CREATE WORKLOAD GROUP ステートメントを実行して、CPU 使用率を制限する優先度の低い操作用にワークロード グループを作成します。この手順の例では、次の構文を使用します。

   CREATE WORKLOAD GROUP group_name USING pool_name;

3. CREATE FUNCTION ステートメントを実行して、前の手順で作成したワークロード グループを優先度の低いログインのユーザーにマップする分類関数を作成します。この手順の例では、次の構文を使用します。

   CREATE FUNCTION [schema_name.]function_name() RETURNS sysname

   WITH SCHEMABINDING

   AS

   BEGIN

   DECLARE @workload_group_name AS sysname

   IF (SUSER_NAME() = 'user_of_low_priority_login')

   SET @workload_group_name = 'workload_group_name'

   RETURN @workload_group_name

   END

   この CREATE FUNCTION ステートメントのコンポーネントの詳細については、次のトピックを参照してください。

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     重要
     SUSER_NAME は、分類関数で使用できるシステム関数の 1 つです。詳細については、「分類関数の記述に関する注意点」を参照してください。

   • SET @local_variable (Transact-SQL).

4. ALTER RESOURCE GOVERNOR ステートメントを実行して、分類関数をリソース ガバナーに登録します。この手順の例では、次の構文を使用します。

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

5. 次のように 2 回目の ALTER RESOURCE GOVERNOR ステートメントを実行して、リソース ガバナーのメモリ内の構成に変更を適用します。

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

例 B : リソース ガバナーの構成 (Transact-SQL)

次の例では、以下の手順を 1 つのトランザクションで実行します。

1. pMAX_CPU_PERCENT_20 リソース プールを作成します。

2. gMAX_CPU_PERCENT_20 ワークロード グループを作成します。

3. 前の例で作成したユーザー名を使用する rgclassifier_MAX_CPU() 分類関数を作成します。

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

この例では、トランザクションをコミットした後に、ALTER WORKLOAD GROUP または ALTER RESOURCE POOL ステートメントで要求された構成の変更が適用されます。

重要
次の例では、「例 A : ログインとユーザーの設定 (Transact-SQL)」で作成したサンプルの SQL Server ユーザーのユーザー名 domain_name\MAX_CPU を使用しています。この名前を、優先度の低い圧縮されたバックアップを作成する際に使用する予定のログインのユーザーの名前に置き換えてください。

-- Configure Resource Governor.
BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%. 
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20
   WITH
      (MAX_CPU_PERCENT = 20);
GO
-- Create a workload group to use this pool. 
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20
USING pMAX_CPU_PERCENT_20;
GO
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'Default' group.
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workload_group_name AS sysname
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'
    RETURN @workload_group_name
END;
GO

-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);
COMMIT TRAN;
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

-- Configure Resource Governor.
BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%. 
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20
   WITH
      (MAX_CPU_PERCENT = 20);
GO
-- Create a workload group to use this pool. 
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20
USING pMAX_CPU_PERCENT_20;
GO
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'Default' group.
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workload_group_name AS sysname
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'
    RETURN @workload_group_name
END;
GO

-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);
COMMIT TRAN;
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

[先頭に戻る]

現在のセッションの分類の確認 (Transact-SQL)

必要に応じて、分類関数で指定したユーザーとしてログインし、オブジェクト エクスプローラーで次の SELECT ステートメントを実行してセッションの分類を確認します。

USE master;
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name 
FROM sys.dm_exec_sessions AS sess 
JOIN sys.dm_resource_governor_workload_groups AS grps 
    ON sess.group_id = grps.group_id
WHERE session_id > 50;
GO

USE master;
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name 
FROM sys.dm_exec_sessions AS sess 
JOIN sys.dm_resource_governor_workload_groups AS grps 
    ON sess.group_id = grps.group_id
WHERE session_id > 50;
GO

結果ペインの name 列に、分類関数で指定したワークロード グループ名のセッションが 1 つ以上表示されるはずです。

注
この SELECT ステートメントで呼び出される動的管理ビューの詳細については、「sys.dm_exec_sessions (Transact-SQL)」および「sys.dm_resource_governor_workload_groups (Transact-SQL)」を参照してください。

[先頭に戻る]

CPU が制限されているセッションを使用したバックアップの圧縮

最大 CPU が制限されているセッションで圧縮されたバックアップを作成するには、分類関数で指定したユーザーでログインします。バックアップ コマンドで、WITH COMPRESSION (Transact-SQL) を指定するか、[バックアップを圧縮する] (SQL Server Management Studio) を選択します。

圧縮されたデータベース バックアップを作成するには

• データベースの完全バックアップを作成する方法 (Transact-SQL)

• データベースをバックアップする方法 (SQL Server Management Studio)

例 C : 圧縮されたバックアップの作成 (Transact-SQL)

次に示す BACKUP の例では、AdventureWorks2008R2 データベースの圧縮された完全バックアップを、新たな形式のバックアップ ファイル Z:\SQLServerBackups\AdvWorks2008R2Data.bak に作成します。

--Run backup statement in the gBackup session.
BACKUP DATABASE AdventureWorks2008R2 TO DISK='Z:\SQLServerBackups\AdvWorks2008R2Data.bak' 
WITH 
   FORMAT, 
   MEDIADESCRIPTION='AdventureWorks2008R2 Compressed Data Backups'
   DESCRIPTION='First database backup on AdventureWorks2008R2 Compressed Data Backups media set'
   COMPRESSION;
GO

--Run backup statement in the gBackup session.
BACKUP DATABASE AdventureWorks2008R2 TO DISK='Z:\SQLServerBackups\AdvWorks2008R2Data.bak' 
WITH 
   FORMAT, 
   MEDIADESCRIPTION='AdventureWorks2008R2 Compressed Data Backups'
   DESCRIPTION='First database backup on AdventureWorks2008R2 Compressed Data Backups media set'
   COMPRESSION;
GO

[先頭に戻る]

関連項目

タスク

ユーザー定義の分類関数を作成およびテストする方法 (Transact-SQL)

概念

リソース ガバナの概要

リソース ガバナーの概念

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

リソース ガバナーのトラブルシューティング

その他の技術情報

リソース ガバナを使用した SQL Server ワークロードの管理

リソース ガバナの使用法に関するトピック