次の方法で共有


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

適用対象 SQL Server

既定の設定では、圧縮を使用してバックアップを行うと 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 ログインを作成します。

    ログインを作成するには

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

    詳細については、「GRANT (データベース プリンシパルのアクセス許可 (Transact-SQL)」を参照してください。

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

    ユーザーを作成するには

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

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

    詳細については、「GRANT (データベース プリンシパルのアクセス許可 (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 のユーザーを作成し、このユーザーを AdventureWorks2022 サンプル データベースの 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 AdventureWorks2022 for this login  
USE AdventureWorks2022;  
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 使用率を制限するようにリソース ガバナーを構成するには」で説明し、その後に Transact-SQL の手順の一例を示します。

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

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

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

    CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );
    

    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 ステートメントのコンポーネントの詳細については、次を参照してください。

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

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);
    
  5. 次のように 2 回目の ALTER RESOURCE GOVERNOR ステートメントを実行して、リソース ガバナーのメモリ内の構成に変更を適用します。

    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.  
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 つ以上表示されるはずです。

Note

この 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 の例では、AdventureWorks2022 データベースの圧縮された完全バックアップを、新たな形式のバックアップ ファイル Z:\SQLServerBackups\AdvWorksData.bak に作成します。

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

[トップ]

参照

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