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

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

重要

所定のリソース ガバナーのシナリオでは、セッションの分類が、ユーザー名、アプリケーション名、または接続を区別できるその他の要素に基づいて行われます。 詳細については、「 Resource Governor Classifier Function 」および「 Resource Governor Workload Group」を参照してください。

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

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

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

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

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

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

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

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

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

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

   ログインを作成するには

   • ログインの作成

   • CREATE LOGIN (Transact-SQL)

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

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

   詳細については、「 GRANT Database Principal Permissions (Transact-SQL)」を参照してください。

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

   ユーザーを作成するには

   • データベース ユーザーの作成

   • CREATE USER (Transact-SQL)

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

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

   • sp_addrolemember (Transact-SQL)

   詳細については、「 GRANT Database Principal Permissions (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 のユーザーを作成し、このユーザーを AdventureWorks2012 サンプル データベースの 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 AdventureWorks2012 for this login  
USE AdventureWorks2012;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO  
  

[上へ]

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

Note

リソース ガバナーが有効になっていることを確認してください。 詳細については、「 リソース ガバナーの有効化」を参照してください。

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

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

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

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

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

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

Note

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

これらの手順の Transact-SQL ステートメントについては、「CPU 使用率を制限するためのResource Governorを構成するには」の手順で説明します。その後に、Transact-SQL の例を示します。

リソース ガバナーを構成するには (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;  
   

例 B:Resource Governor の構成 (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  
  

[上へ]

現在のセッションの分類の確認 (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  

結果ペインの [名前] 列に、分類子関数で指定したワークロード グループ名のセッションが 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) を選択します。 圧縮されたデータベース バックアップを作成するには、「データベースの完全バックアップを作成する (SQL Server)」を参照してください。

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

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

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

[上へ]

参照

ユーザー定義の分類子関数の作成とテスト
リソース ガバナー