チュートリアル: リソース ガバナーの構成例とベスト プラクティス
適用対象:
SQL ServerAzure SQL Managed Instance
この記事には、リソース ガバナーを構成し、構成が期待どおりに動作することを検証するのに役立つチュートリアルの例が含まれています。 それは簡単な例から始まり、より複雑なものに進みます。
この記事には、リソース ガバナー
すべての例では、最初はリソース ガバナーが無効になり、既定の設定が使用され、ユーザー定義のリソース プール、ワークロード グループ、分類子関数が存在しないことを前提としています。
手記
Azure SQL Managed Instance の場合、リソース ガバナーの構成を変更するには、master データベースのコンテキストにある必要があります。
既定のグループを変更する
この例では、リソース ガバナーを使用して、すべてのユーザー クエリのメモリ許可の最大サイズを制限します。 これを行うには、default ワークロード グループの REQUEST_MAX_MEMORY_GRANT_PERCENT 設定を既定の 25% から 10%に減らします。 この例では、分類子関数は使用しません。 つまり、ログイン処理は影響を受けず、すべてのユーザー セッションは引き続き default ワークロード グループに分類されます。
他のクエリが多すぎるメモリを予約しているため、クエリがメモリを待機している場合は、メモリ許可のサイズを制限する必要がある場合があります。 詳細については、「SQL Serverのメモリ許可によって発生するパフォーマンスの低下またはメモリ不足の問題のトラブルシューティング」を参照してください。
既定のワークロード グループを変更します。
ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 10);リソース ガバナーを有効にして、構成を有効にします。
ALTER RESOURCE GOVERNOR RECONFIGURE;メモリ許可の新しい最大サイズを含め、新しい設定を検証します。
SELECT group_id, wg.name AS workload_group_name, rp.name AS resource_pool_name, wg.request_max_memory_grant_percent_numeric AS request_max_memory_grant_percent, rp.max_memory_kb * wg.request_max_memory_grant_percent_numeric AS request_max_memory_grant_size_kb FROM sys.resource_governor_workload_groups AS wg INNER JOIN sys.dm_resource_governor_resource_pools AS rp ON wg.pool_id = rp.pool_id;初期構成に戻すには、次のスクリプトを実行します。
ALTER RESOURCE GOVERNOR DISABLE; ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25);
ユーザー定義ワークロード グループを使用する
この例では、リソース ガバナーを使用して、特定のアプリケーション名を持つセッションのすべての要求が、並列処理の次数 (DOP) が 4 より高い状態で実行されないようにします。 これを行うには、MAX_DOP 設定が 4 に設定されたワークロード グループにセッションを分類します。
並列処理の最大次数の構成の詳細については、「サーバーの構成: 並列処理の最大次数」を参照してください。
DOP を制限するワークロード グループを作成します。 グループは、特定のアプリケーションに対して DOP のみを制限し、CPU、メモリ、または I/O リソースを予約または制限しないため、
defaultリソース プールを使用します。CREATE WORKLOAD GROUP limit_dop WITH ( MAX_DOP = 4 ) USING [default];分類子関数を作成します。 この関数は、組み込みの APP_NAME() 関数を使用して、クライアント接続文字列で指定されたアプリケーション名を決定します。 アプリケーション名が
limited_dop_applicationに設定されている場合、この関数は DOP を制限するワークロード グループの名前を返します。 それ以外の場合、この関数はワークロード グループ名としてdefaultを返します。USE master; GO CREATE FUNCTION dbo.rg_classifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN DECLARE @WorkloadGroupName sysname = N'default'; IF APP_NAME() = N'limited_dop_application' SELECT @WorkloadGroupName = N'limit_dop'; RETURN @WorkloadGroupName; END; GOリソース ガバナーの構成を変更して構成を有効にし、リソース ガバナーを有効にします。
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier); ALTER RESOURCE GOVERNOR RECONFIGURE;クエリ sys.resource_governor_configuration を使用して、リソース ガバナーが有効になっており、作成した分類子関数を使用していることを検証します。
SELECT OBJECT_SCHEMA_NAME(classifier_function_id) AS classifier_schema_name, OBJECT_NAME(classifier_function_id) AS classifier_object_name, is_enabled FROM sys.resource_governor_configuration;classifier_schema_name classifier_object_name is_enabled ---------------------- ---------------------- ---------- dbo rg_classifier 1特定のアプリケーション名を持つセッションが
limit_dopワークロード グループに分類され、他のセッションは引き続きdefaultワークロード グループに分類されていることを検証します。 sys.dm_exec_sessions および sys.resource_governor_workload_groups システム ビューを使用して、現在のセッションのアプリケーション名とワークロード グループ名を返すクエリを使用します。SQL Server Management Studio (SSMS) で、メイン メニューの [ファイル ]、[新規 ]、[データベース エンジン クエリ ]を選択します。
[データベース エンジンへの接続] ダイアログで、ワークロード グループと分類子関数を作成したのと同じデータベース エンジン インスタンスを指定します。 [追加接続パラメータ] タブを選択し、「
App=limited_dop_application」と入力します。 これにより、SSMS はインスタンスに接続するときにアプリケーション名としてlimited_dop_applicationを使用します。接続 を選んで新しい接続を開きます。
同じクエリ ウィンドウで、次のクエリを実行します。
SELECT s.program_name AS application_name, wg.name AS workload_group_name, wg.max_dop FROM sys.dm_exec_sessions AS s INNER JOIN sys.resource_governor_workload_groups AS wg ON s.group_id = wg.group_id WHERE s.session_id = @@SPID;次の出力が表示されます。これは、セッションが最大 DOP が 4 に設定された
limit_dopワークロード グループに分類されたことを示しています。application_name workload_group_name max_dop ---------------- ------------------- ------- limited_dop_application limit_dop 4上記の手順を繰り返しますが、[追加接続パラメーター] タブのボックスには何も入力しないでください。出力が変更され、既定の SSMS アプリケーション名と、最大 DOP の既定の
0値を持つdefaultワークロード グループが表示されます。application_name workload_group_name max_dop ---------------- ------------------- ------- Microsoft SQL Server Management Studio - Query default 0
このサンプルの初期構成に戻すには、次の T-SQL スクリプトを実行します。
ALTER RESOURCE GOVERNOR DISABLE; ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL); DROP FUNCTION IF EXISTS dbo.rg_classifier; DROP WORKLOAD GROUP limit_dop;
複数のリソース プールとワークロード グループを使用する
この例では、リソース ガバナーを使用して、注文処理アプリケーションのセッションを、時間帯に応じて異なるワークロード グループとリソース プールに分類します。 この構成では、ピーク時の処理時間にさらに多くのリソースがアプリケーションに割り当てられ、時間外にそのリソースが制限されます。 この例では、アプリケーションが実行時間の長いセッションを使用しないことを前提としています。
ピーク時と勤務時間外の処理用に 2 つのリソース プールを作成します。
peak_hours_poolプールは、MIN_CPU_PERCENTを介して平均 CPU 帯域幅の最低 20% を保証 (予約) し、MAX_CPU_PERCENTを100に設定することで CPU 帯域幅を制限しません。off_hours_poolプールは、MIN_CPU_PERCENTを0に設定することで CPU 帯域幅を予約しませんが、MAX_CPU_PERCENTを50に設定することで、CPU の競合が存在する場合は CPU 帯域幅を 50% に制限します。
CREATE RESOURCE POOL peak_hours_pool WITH ( MIN_CPU_PERCENT = 20, MAX_CPU_PERCENT = 100 ); CREATE RESOURCE POOL off_hours_pool WITH ( MIN_CPU_PERCENT = 0, MAX_CPU_PERCENT = 50 );リソース プールは、CPU、メモリ、I/O などのシステム リソースを予約および制限できます。 詳細については、「CREATE RESOURCE POOL」を参照してください。
リソース プールごとに 1 つずつ、2 つのワークロード グループを作成します。
peak_hours_groupでは、GROUP_MAX_REQUESTSを既定値の0に設定しても、同時要求の数は制限されません。off_hours_groupでは、GROUP_MAX_REQUESTSを200に設定することで、このグループに分類されるすべてのセッションで同時要求の数を制限します。
CREATE WORKLOAD GROUP peak_hours_group WITH ( GROUP_MAX_REQUESTS = 0 ) USING peak_hours_pool; CREATE WORKLOAD GROUP off_hours_group WITH ( GROUP_MAX_REQUESTS = 200 ) USING off_hours_pool;ワークロード グループは、要求の最大数、並列処理の最大限度、最大メモリ許可サイズなどのポリシーを定義します。 詳細については、「CREATE WORKLOAD GROUP」を参照してください。
ピーク時とオフ時間の時間間隔を定義するテーブルを作成して設定します。
- テーブル内の各行は、間隔の開始時刻と終了時刻、および間隔中に使用するワークロード グループの名前を定義します。
- 各間隔の開始時刻と終了時刻は含まれます。
- テーブルは、スキーマ バインド分類子関数で使用できるように、
masterデータベースに作成されます。
USE master; GO CREATE TABLE dbo.workload_interval ( workload_group_name sysname NOT NULL, start_time time(7) NOT NULL, end_time time(7) NOT NULL, CONSTRAINT pk_workload_interval PRIMARY KEY (start_time, workload_group_name), CONSTRAINT ak_workload_interval_1 UNIQUE (end_time, workload_group_name), CONSTRAINT ck_workload_interval_1 CHECK (start_time < end_time) ); GO INSERT INTO dbo.workload_interval VALUES (N'off_hours_group', '00:00', '06:29:59.9999999'), (N'peak_hours_group', '06:30', '18:29:59.9999999'), (N'off_hours_group', '18:30', '23:59:59.9999999');分類子関数を作成します。
- テーブル内のデータには、特定の時刻に対して 1 つの一致する行が必要です。 データがその規則に違反している場合、関数はワークロード グループ名として
defaultを返します。 - 次の例の関数は、組み込みの APP_NAME() 関数によって返されるアプリケーション名が
order_processing以外の場合にもdefaultを返します。
USE master; GO CREATE OR ALTER FUNCTION dbo.rg_classifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN DECLARE @WorkloadGroupName sysname = N'default'; SELECT @WorkloadGroupName = workload_group_name FROM dbo.workload_interval WHERE APP_NAME() = N'order_processing' AND CAST(GETDATE() AS time(7)) BETWEEN start_time AND end_time; IF @@ROWCOUNT > 1 SELECT @WorkloadGroupName = N'default'; RETURN @WorkloadGroupName; END; GO- テーブル内のデータには、特定の時刻に対して 1 つの一致する行が必要です。 データがその規則に違反している場合、関数はワークロード グループ名として
これは省略可能な手順です。
masterデータベースにテーブルを作成する代わりに、テーブル値コンストラクター を使用して、分類子関数で時間間隔を直接定義できます。 これは、データ サイズが小さく、分類子関数の条件が頻繁に変更されない場合に推奨される方法です。 次に示すのは、masterのテーブルではなく、テーブル値コンストラクターを使用するのと同じ分類子の例です。USE master; GO CREATE OR ALTER FUNCTION dbo.rg_classifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN DECLARE @WorkloadGroupName sysname = N'default'; SELECT @WorkloadGroupName = workload_group_name FROM ( VALUES (CAST(N'off_hours_group' AS sysname), CAST('00:00' AS time(7)), CAST('06:29:59.9999999' AS time(7))), (CAST(N'peak_hours_group' AS sysname), CAST('06:30' AS time(7)), CAST('18:29:59.9999999' AS time(7))), (CAST(N'off_hours_group' AS sysname), CAST('18:30' AS time(7)), CAST('23:59:59.9999999'AS time(7))) ) AS wg (workload_group_name, start_time, end_time) WHERE APP_NAME() = N'order_processing' AND CAST(GETDATE() AS time(7)) BETWEEN start_time AND end_time; IF @@ROWCOUNT > 1 SELECT @WorkloadGroupName = N'default'; RETURN @WorkloadGroupName; END; GOリソース ガバナーの構成を変更して構成を有効にし、リソース ガバナーを有効にします。
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier); ALTER RESOURCE GOVERNOR RECONFIGURE;前の例と同様の手順を使用して、リソース ガバナーが有効であること、指定された分類子関数を使用していること、および分類子関数が期待どおりに動作することを検証
。 ここでは、SSMS 接続ダイアログの [追加の接続パラメーター] タブに App=order_processingを入力して、分類子関数のアプリケーション名と一致させます。 次のクエリを実行して、アプリケーション名、ワークロード グループ、リソース プール、および現在のセッションの CPU 予約と制限を決定します。SELECT s.program_name AS application_name, wg.name AS workload_group_name, wg.group_max_requests, rp.name AS resource_pool_name, rp.min_cpu_percent, rp.max_cpu_percent FROM sys.dm_exec_sessions AS s INNER JOIN sys.resource_governor_workload_groups AS wg ON s.group_id = wg.group_id INNER JOIN sys.resource_governor_resource_pools AS rp ON wg.pool_id = rp.pool_id WHERE s.session_id = @@SPID;結果は、時刻によって異なります。 たとえば、現在の時刻が 14:30 の場合、
peak_hours_groupとpeak_hours_poolが使用されていることが結果に示されます。application_name workload_group_name group_max_requests resource_pool_name min_cpu_percent max_cpu_percent ----------------- -------------------- ------------------ ------------------- --------------- --------------- order_processing peak_hours_group 0 peak_hours_pool 20 100初期構成に戻すには、次の T-SQL スクリプトを実行します。
ALTER RESOURCE GOVERNOR DISABLE; ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL); DROP FUNCTION IF EXISTS dbo.rg_classifier; DROP TABLE IF EXISTS dbo.workload_interval; DROP WORKLOAD GROUP peak_hours_group; DROP WORKLOAD GROUP off_hours_group; DROP RESOURCE POOL peak_hours_pool; DROP RESOURCE POOL off_hours_pool;
システム ビューを使用してリソース ガバナーを監視する
このセクションのクエリ例では、リソース ガバナーランタイムの統計と動作を監視する方法を示します。
リソース ガバナーの統計情報は、前回のサーバーの再起動以降に累積されます。 特定の時刻から統計を収集する必要がある場合は、ALTER RESOURCE GOVERNOR RESET STATISTICS ステートメントを使用して統計をリセットできます。
リソース プールランタイムの統計
リソース プールごとに、リソース ガバナーは CPU とメモリの使用率、メモリ不足イベント、メモリ許可、I/O、およびその他の統計情報を追跡します。 詳細については、「sys.dm_resource_governor_resource_pools」を参照してください。
次のクエリは、すべてのリソース プールで使用可能な統計情報のサブセットを返します。
SELECT rp.pool_id,
rp.name AS resource_pool_name,
wg.workload_group_count,
rp.statistics_start_time,
rp.total_cpu_usage_ms,
rp.target_memory_kb,
rp.used_memory_kb,
rp.out_of_memory_count,
rp.active_memgrant_count,
rp.total_memgrant_count,
rp.total_memgrant_timeout_count,
rp.read_io_completed_total,
rp.write_io_completed_total,
rp.read_bytes_total,
rp.write_bytes_total,
rp.read_io_stall_total_ms,
rp.write_io_stall_total_ms
FROM sys.dm_resource_governor_resource_pools AS rp
OUTER APPLY (
SELECT COUNT(1) AS workload_group_count
FROM sys.dm_resource_governor_workload_groups AS wg
WHERE wg.pool_id = rp.pool_id
) AS wg;
ワークロード グループのランタイム統計
リソース ガバナーは、ワークロード グループごとに、CPU 時間、要求の数、ブロックされたタスク、ロック待機時間、クエリの最適化、およびその他の統計情報を追跡します。 詳細については、sys.resource_governor_workload_groupsを参照してください。
次のクエリは、すべてのワークロード グループで使用可能な統計情報のサブセットを返します。
SELECT wg.name AS workload_group_name,
rp.name AS resource_pool_name,
wg.statistics_start_time,
wg.total_request_count,
wg.total_cpu_usage_ms,
wg.blocked_task_count,
wg.total_lock_wait_time_ms,
wg.total_query_optimization_count,
wg.max_request_grant_memory_kb,
wg.active_parallel_thread_count,
wg.effective_max_dop,
wg.request_max_memory_grant_percent_numeric
FROM sys.dm_resource_governor_workload_groups AS wg
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
ワークロード グループとセッション属性別にセッションを集計する
次のクエリは、ワークロード グループ間のセッションの分布を返し、各ワークロード グループのセッション統計を集計します。
preconnect 状態のセッションの数が多いと、分類子の実行が遅くなっている可能性があります。
SELECT wg.name AS workload_group_name,
rp.name AS resource_pool_name,
s.program_name AS application_name,
s.login_name,
s.host_name,
s.status,
d.name AS database_name,
MIN(s.login_time) AS first_login_time,
MAX(s.login_time) AS last_login_time,
MAX(s.last_request_start_time) AS last_request_start_time,
COUNT(1) AS session_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
INNER JOIN sys.databases AS d
ON s.database_id = d.database_id
GROUP BY wg.name,
rp.name,
s.program_name,
s.login_name,
s.host_name,
s.status,
d.name;
ワークロード グループと要求属性別に要求を集計する
次のクエリは、ワークロード グループ間での要求の分散と、各ワークロード グループの要求統計の集計を返します。
SELECT wg.name AS workload_group_name,
rp.name AS resource_pool_name,
r.command,
r.status,
d.name AS database_name,
COUNT(1) AS request_count,
MIN(r.start_time) AS first_request_start_time,
MAX(r.start_time) AS last_request_start_time,
SUM(CAST(r.total_elapsed_time AS bigint)) AS total_elapsed_time_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON r.group_id = wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
INNER JOIN sys.databases AS d
ON r.database_id = d.database_id
GROUP BY wg.name,
rp.name,
r.command,
r.status,
d.name;
リソース ガバナーのベスト プラクティス
- 専用管理者接続 (DAC) を構成し、その使用方法について説明します。 詳細については、「データベース管理者の診断接続」を参照してください。 リソース ガバナーの構成が正常に動作しない場合は、DAC を使用してトラブルシューティングを行ったり、リソース ガバナーのを無効
できます。 - リソース プールを構成するときは、
MIN_CPU_PERCENT、MIN_MEMORY_PERCENT、およびMIN_IOPS_PER_VOLUMEに大きな値を指定するように注意してください。MIN構成設定では、リソース プールのリソースが予約され、defaultプールを含む他のリソース プールでは使用できなくなります。 詳細については、「リソース プールの作成」を参照してください。 - 分類子関数は、ログイン処理時間を拡張します。 特にクエリで大規模なテーブルを使用する場合は、複雑なロジックや、実行時間の長いクエリやリソースを集中的に使用するクエリを分類子で回避します。 過度に複雑な関数では、ログインの遅延や接続タイムアウトが発生する可能性があります。
- 分類子でテーブルを使用する必要があり、テーブルが小さく、ほとんど静的である場合は、この記事の前半
例に示すように、代わりにテーブル値コンストラクター 使用することを検討してください。 - 分類子で頻繁に変更されるテーブルを使用しないでください。 これにより、ログインを遅らせ、接続タイムアウトを引き起こす可能性のあるブロックのリスクが高まります。 次の回避策を使用すると、リスクを軽減できますが、分類が正しくないリスクなど、欠点があります。
NOLOCKテーブル ヒント、または同等のREADUNCOMMITTEDヒントの使用を検討してください。 詳細については、READUNCOMMITTED を参照してください。- 分類子関数の開始時に
LOCK_TIMEOUT設定を使用し、1,000 ミリ秒などの低い値に設定することを検討してください。 詳細については、「SET LOCK_TIMEOUT」を参照してください。
- 分類子関数は、リソース ガバナー構成で参照されている間は変更できません。 ただし、別の分類子関数を使用するように構成を変更できます。 分類子に変更を加える場合は、分類子関数のペアを作成することを検討してください。 たとえば、
dbo.rg_classifier_A()とdbo.rg_classifier_B()を作成できます。 分類子ロジックの変更が必要な場合は、次の手順に従います。- ALTER FUNCTION ステートメントを使用して、リソース ガバナー構成で現在 "使用されていない" 関数に変更を加えます。
- ALTER RESOURCE GOVERNOR ステートメントを使用して、変更された分類子をアクティブにしてから、リソース ガバナーを再構成します。 例えば：
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier_B); ALTER RESOURCE GOVERNOR RECONFIGURE; - もう一度変更が必要な場合は、他の関数 (
dbo.rg_classifier_A()) を使用して同じ手順に従います。
- リソース ガバナーの構成は、
masterデータベースに格納されます。masterを定期的にバックアップし、復元方法を確認してください。 詳細については、「バックアップと復元: システム データベース」を参照してください。masterの復元には制限があるため、リソース ガバナー構成スクリプトのコピーも個別に保存することをお勧めします。masterデータベースを再構築する必要がある場合は、スクリプトからリソース ガバナー構成を再作成できます。