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

[アーティクル]
07/12/2024

適用対象 SQL Server

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

重要

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

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

優先度の低い操作を行うためのログインとユーザーの設定
CPU 使用率を制限するためのリソースガバナーの構成
現在のセッションの分類の確認 (Transact-SQL)
CPU が制限されているセッションを使用したバックアップの圧縮

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

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

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

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

ログインを作成するには
- ログインの作成
- CREATE LOGIN (Transact-SQL)
必要に応じて、このログインに VIEW SERVER STATE 権限を付与します。
- GRANT (システムオブジェクトの権限の許可) (Transact-SQL)
詳細については、「GRANT (データベースプリンシパルのアクセス許可 (Transact-SQL)」を参照してください。
このログインに対して SQL Server ユーザーを作成します。

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

固定データベースロールにユーザーを追加するには
- sp_addrolemember (Transact-SQL)
詳細については、「GRANT (データベースプリンシパルのアクセス許可 (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

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

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

リソースガバナーのリソースプールを作成し、CPU の競合が発生したときにリソースプール内の要求に割り当てられる最大平均 CPU 帯域幅を制限するように構成します。
このプールを使用するリソースガバナーワークロードグループを作成して構成します。
ユーザー定義関数 (UDF) である 分類子関数を作成します。リソースガバナーは、この関数の戻り値を使用してセッションを分類し、適切なワークロードグループにセッションがルーティングされるようにします。
分類子関数をリソースガバナーに登録します。
リソースガバナーのメモリ内の構成に変更を適用します。

Note

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

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

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

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

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) を使用します。
CREATE WORKLOAD GROUP ステートメントを実行して、CPU 使用率を制限する優先度の低い操作用にワークロードグループを作成します。この手順の例では、次の構文を使用します。
```
CREATE WORKLOAD GROUP [<group_name>] USING [<pool_name>];
```
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).
ALTER RESOURCE GOVERNOR ステートメントを実行して、分類子関数をリソースガバナーに登録します。この手順の例では、次の構文を使用します。
```
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);
```
次のように 2 回目の ALTER RESOURCE GOVERNOR ステートメントを実行して、リソースガバナーのメモリ内の構成に変更を適用します。
```
ALTER RESOURCE GOVERNOR RECONFIGURE;  
```

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

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

pMAX_CPU_PERCENT_20 リソースプールを作成します。
gMAX_CPU_PERCENT_20 ワークロードグループを作成します。
前の例で作成したユーザー名を使用する rgclassifier_MAX_CPU() 分類子関数を作成します。
分類子関数をリソースガバナーに登録します。

この例では、トランザクションをコミットした後に、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

[トップ]

参照

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

次の方法で共有

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

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

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

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

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

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

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

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

参照

フィードバック

その他のリソース

次の方法で共有

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

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

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

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

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

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

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

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

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

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

参照

フィードバック

その他のリソース

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

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

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

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

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