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

2017-06-13

既定では、圧縮を使用してバックアップすると CPU 使用率が大幅に増加し、圧縮プロセスによって消費される追加の CPU が同時操作に悪影響を与える可能性があります。そのため、CPU の競合が発生したときにリソースガバナーによって CPU 使用率が制限されるセッションで、優先順位の低い圧縮バックアップを作成できます。このトピックでは、特定の SQL Server ユーザーのセッションを、このような場合の CPU 使用率を制限する Resource Governor ワークロードグループにマッピングすることによって分類するシナリオについて説明します。

重要

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

このトピックには、次の一連のシナリオが含まれています。これらは順番に示されています。

Low-Priority 操作のログインとユーザーの設定
CPU 使用率を制限するためのリソースガバナーの構成
現在のセッションの分類の検証 (Transact-SQL)
CPU が制限されたセッションを使用したバックアップの圧縮

Low-Priority 操作のログインとユーザーの設定

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

次の手順では、この目的でログインとユーザーを設定する手順について説明し、その後に 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 ログインとユーザーの名前に置き換えます。

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

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

注

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

この Resource Governor シナリオでは、構成は次の基本的な手順で構成されます。

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

注

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

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

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

CPU 使用率を制限するように Resource Governor を構成するには (Transact-SQL)

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

リソースプール pool_name を作成する WITH ( MAX_CPU_PERCENT = value );

値は、最大平均 CPU 帯域幅の割合を示す 1 から 100 までの整数です。適切な値は、環境によって異なります。図の目的上、このトピックの例では 20%% (MAX_CPU_PERCENT = 20) を使用します。
CREATE WORKLOAD GROUP ステートメントを発行して、CPU 使用率を管理する優先順位の低い操作用のワークロードグループを作成します。この手順の例では、次の構文を使用します。

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

CREATE 関数 [schema_name.]function_name() RETURNS sysname

SCHEMABINDING を使用する

という形で

開始

DECLARE @workload_group_name AS sysname

IF (SUSER_NAME() = 'user_of_low_priority_login')

SET @workload_group_name = 'workload_group_name'

帰る @workload_group_name

終了

この CREATE FUNCTION ステートメントのコンポーネントについては、以下を参照してください。
- DECLARE @local_variable (Transact-SQL)
- SUSER_SNAME (Transact-SQL)
  
  重要
  
  SUSER_NAMEは、分類子関数で使用できるいくつかのシステム関数の 1 つに過ぎません。詳細については、「分類子 User-Defined 関数の作成とテスト」を参照してください。
- SET @local_variable (Transact-SQL)。
ALTER RESOURCE GOVERNOR ステートメントを発行して、分類子関数を 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)」domain_name\MAX_CPUで作成したサンプル SQL Server ユーザーのユーザー名を使用します。これを、優先順位の低い圧縮バックアップの作成に使用するログインのユーザーの名前に置き換えます。

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

[上]

こちらもご覧ください

分類子 User-Defined 関数を作成してテストする
 リソースガバナー

次の方法で共有

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

このトピックには、次の一連のシナリオが含まれています。これらは順番に示されています。

Low-Priority 操作のログインとユーザーの設定

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

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

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