SQL Server 2008 以降は CPU / メモリのワークロード制御としてリソースガバナーの機能が追加されています。
このリソースガバナーの機能を使用して、SQL Server Agent のジョブのリソースを制御しようというのが今回のお話です。
Contents
■SQL Server Agent でジョブ実行時のログインについて
リソースガバナーでは HOST_NAME()、APP_NAME()、SUSER_NAME()、SUSER_SNAME()、IS_SRVROLEMEMBER()、IS_MEMBER()、LOGINPROPERTY()、ORIGINAL_DB_NAME()、CONNECTIONPROPERTY() のシステム関数を使用することが可能です。
リソースガバナーを使用する際には、SUSER_NAME() を使用して、ログインで制御するのが一般的かと思います。
SQL Server Agent からジョブを実行した場合、デフォルトの設定ではログインは SQL Server Agent のサービス起動アカウントになります。 
そのため、SQL Server Agent で実行されたジョブのログインも SQL Server Agent サービスの起動アカウントとなります。
■SQL Server Agent のジョブをリソースガバナーで制御する
SQL Server Agent から実行されたジョブはサービスの起動アカウントでログインがされます。
今回は以下のような分類関数を作成しました。
|
CREATE FUNCTION [dbo].[fnClassifier]() |
ログインしているユーザー名を分類関数として使用する単純なものですね。
リソースガバナーは以下のように設定してあります。 
SQL Server Agent のサービスアカウント名で、ワークロードグループを作成しています。
この状態で SQL Server Agent からジョブを実行すると、SQL Server Agent の実行ユーザーをワークロードグループとして設定したリソースプールに割り当てられてジョブが実行されます。
# SQL Agent というリソースプールのリソース使用状況が高くなっていることが確認できます。 
SQL Server Agent から実行されるすべてのジョブを特定のワークロードグループに関連付けたい場合は、SQL Server Agent のサービスアカウントを使用して分類関数を作成することで、リソースを制御できそうですね。
■ステップ単位でリソースガバナーで制御する
実際の運用では SQL Server Agent から実行される全ジョブを特定のワークロードグループに関連付けるより、ステップ単位でワークロードグループを変更したいという要件の方が多いかと思います。
この方法について少し考えてみたいと思います。
# 今回は Transact-SQL スクリプトのジョブで考えています。
SQL Server Agent のジョブステップには、[実行時のユーザー] を指定することが可能です。 
実行時のユーザーを使用して、ジョブステップを実行した場合の動作を見ていきたいと思います。
実行時のユーザーを指定した場合、ジョブは以下のように実行がされます。 
最初に、SQL Server Agent のサービスアカウントでログインし、EXECUTE AS を使用してセキュリティの実行コンテキストを変更して、実際の処理を実行します。 
最初に SQL Server Agent のサービス起動アカウントで SQL Server にログインをしているため、SUSER_NAME を使用したワークロードグループの指定は実行時のユーザーではなく、SQL Server Agent のサービス起動アカウントが使用されます。
# ワークロードグループとの関連付けはログイン時に実施されるため。 
BackupUser というユーザーを実行時のユーザーとして指定しています。
以下のワークロードグループを設定した時の関連付けられるリソースプールを確認してみます。 
実行時のユーザーは BackupUser としていますが、このユーザーに関連付けられていログインで最初に SQL Server にログインがされておらず、SQ
L Server Agent のサービス起動アカウントでログインがされているため、使用されるリソースプールは以下のようになります。
# SQL Server Agent のサービスアカウントに関連づいているリソースプール内で実行されます。 
ユーザー系の情報を使用した場合、ログイン時の情報が起点となりますので、途中で EXECUTE AS をしても想定したリソースプールに関連付けることができません。
ステップ単位でリソースプールを使い分けたい場合、少し面倒なのですが APP_NAME() を使用するとステップ単位でリソースプールを分けることが可能です。
今回はバックアップを取得するジョブを実行しているのですが、このジョブのアプリケーション名は [SQLAgent – TSQL JobStep (Job 0x036015145B537D44A9D08C27729CD8D5 : Step 1)] になります。
# ジョブを実行する前に SQL Server Profiler を設定して、ApplicationName から取得するのが早いと思います。
そこで、分類関数を以下のように設定します。
|
CREATE FUNCTION [dbo].[fnClassifier]() |
実行されたアプリケーションがバックアップを取得する SQL Server Agent のジョブステップだった場合は、バックアップ用のワークロードグループを設定しています。
リソースガバナーの設定はこのようになっています。 
この状態でジョブステップを実行し、リソースプールの状態を確認すると以下のようになっています。 
バックアップジョブ用に用意しているリソースプールを使用してステップが実行されているのが確認できますね。
ステップの順番変更や再作成をした場合には名称が変わってしまうのであまり汎用的な方法でないのですが…。
# 海外のフォーラムで同様の実装についての話題があったのですが、そこでもこの投稿のように APP_NAME を使用したらどうかという回答になっていました。
T-SQL のステップではなく、外部に SQLCMD を使用してクエリを実行するバッチを用意し、SQL Server Agent (またはタスクスケジューラー) から、そのバッチを実行して制御した方がシンプルかもしれないですね。
# この場合、SQLCMD で接続をするログインの情報を使用すれば適切なリソースプールに割り当てが可能ですので。
定期的なバックアップ取得で、バックアップ圧縮をする際の CPU の使用率の調整する方法はどういうのが良いかなと思って考えてみたのですが、SQL Server Agent の T-SQL のステップで実行するのは運用を考慮するとなかなか厳しいのかもしれないですね。