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

[アーティクル]
06/25/2013

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

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

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

優先度の低い操作を行うためのログインとユーザーの設定
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)」を参照してください。

例 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 使用率を制限するためのリソースガバナーの構成

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

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

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

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

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

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

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

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

CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

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

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.
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

結果ペインの 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) を選択します。圧縮されたデータベースバックアップを作成する方法については、「データベースの完全バックアップの作成 (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

[先頭に戻る]

次の方法で共有

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

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

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

例 A : ログインとユーザーの設定 (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)