チュートリアル: tempdb 領域リソースガバナンスを構成する例

2025-06-17

適用対象: SQL Server 2025 (17.x) プレビュー以降のバージョン

この記事の例では、 tempdb 領域の使用量に制限を設定し、各ワークロードグループによる tempdb 領域の使用量を表示する方法を示します。

tempdbスペースリソースガバナンスの概要については、Tempdb の領域リソースガバナンスに関するページを参照してください。

これらの例は、テスト環境の非運用環境で tempdb スペースリソースガバナンスに慣れるために役立ちます。

例では、リソースガバナーが最初に有効になっていないこと、およびその構成が既定から変更されていないことを前提としています。また、SQL Server インスタンス上の他のワークロードが、スクリプトの実行中に tempdb 領域の消費に大きく寄与していないと想定しています。

`default` ワークロードグループの固定制限を設定する

次の使用例は、tempdb ワークロードグループ内の要求 (クエリ) によるdefault領域の合計消費量を固定の制限に制限します。

default ワークロードグループを変更して、tempdb領域の使用量に対する固定の 20 GB の制限を構成します。
```
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
```
リソースガバナーを有効にして、現在の構成を有効にします。
```
ALTER RESOURCE GOVERNOR RECONFIGURE;
```

tempdb領域の使用量の制限を表示します。

SELECT group_id,
       name,
       group_max_tempdb_data_mb,
       group_max_tempdb_data_percent
FROM sys.resource_governor_workload_groups
WHERE name = 'default';

tempdb ワークロードグループによる現在のdefault領域の消費量を確認し、一時テーブルを作成して 1 行挿入してtempdbにデータを追加してから、領域消費量をもう一度確認して増加を確認します。

SELECT group_id,
       name,
       tempdb_data_space_kb
FROM sys.dm_resource_governor_workload_groups
WHERE name = 'default';

SELECT REPLICATE('A', 1000) AS c
INTO #t;

SELECT group_id,
       name,
       tempdb_data_space_kb
FROM sys.dm_resource_governor_workload_groups
WHERE name = 'default';

必要に応じて、default グループの制限を削除し、リソースガバナーを無効にして、tempdbにおける非統制スペース消費に戻します。
```
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);

ALTER RESOURCE GOVERNOR DISABLE;
```

`default` ワークロードグループの割合制限を設定する

次の使用例は、tempdbデータファイルを構成して、パーセント制限を使用できるようにし、tempdb ワークロードグループ内の要求 (クエリ) によるdefault領域の合計消費量をパーセント制限に制限します。

すべてのFILEGROWTHデータファイルのMAXSIZEとtempdbを要件を満たすように設定し、tempdbの最大サイズを 1 GB に制限します。

この例では、 tempdb に 4 つのデータファイルがあることを前提としています。 tempdb構成で使用するファイルの数が異なる場合や、ファイルの論理名が異なる場合は、スクリプトの調整が必要になる場合があります。このスクリプトの実行時に、エラー 5040 tempdbエラーが表示された場合、SQL Server インスタンスを再起動して、使用量を減らす必要があります。
```
ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp2', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp3', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp4', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
```
default ワークロードグループを変更して、tempdb領域の使用量に対して 5% の制限を構成します。最大 tempdb サイズが 1 GB の場合、 default グループは約 51 MB の tempdb 領域に制限されます。
```
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_PERCENT = 5);
```
固定の制限が設定されている場合は、パーセント制限をオーバーライドしないように削除します。
```
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);
```
リソースガバナーを有効にして、構成を有効にします。
```
ALTER RESOURCE GOVERNOR RECONFIGURE;
```

tempdb領域の使用量の制限を表示します。

SELECT group_id,
       name,
       group_max_tempdb_data_mb,
       group_max_tempdb_data_percent
FROM sys.resource_governor_workload_groups
WHERE name = 'default';

tempdbにデータを追加して制限に達します。
```
SELECT *
INTO #m
FROM sys.messages;
```
ステートメントはエラー 1138 で中止されます。
ワークロードグループの統計をtempdbでチェックします。
```
SELECT group_id,
       name,
       tempdb_data_space_kb,
       peak_tempdb_data_space_kb,
       total_tempdb_data_limit_violation_count
FROM sys.dm_resource_governor_workload_groups
WHERE name = 'default';
```
total_tempdb_data_limit_violation_count列の値が 1 ずつインクリメントされ、default ワークロードグループ内の 1 つの要求が、リソースガバナーによってtempdb領域の消費が制限されたために中止されたことを示しています。
必要に応じて、default グループの制限を削除し、リソースガバナーを無効にして、tempdbにおける非統制スペース消費に戻します。
```
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);

ALTER RESOURCE GOVERNOR DISABLE;
```
必要に応じて、この例で前に行った tempdb データファイル構成の変更を元に戻します。

ユーザー定義ワークロードグループの固定制限を設定する

この例では、新しいワークロードグループを作成し、特定のアプリケーション名を持つセッションをこのワークロードグループに割り当てる分類子関数を作成します。

この例では、ワークロードグループの tempdb 領域使用量に対する固定制限は、1 MB の小さな値に設定されています。この例では、制限を超える領域を tempdb に割り当てようとすると中止されることを示しています。

ワークロードグループを作成し、その tempdb 領域の消費量を 1 MB に制限します。
```
CREATE WORKLOAD GROUP limited_tempdb_space_group
WITH (GROUP_MAX_TEMPDB_DATA_MB = 1);
```
master データベースに分類子関数を作成します。分類子は、組み込みの APP_NAME 関数を使用して、クライアント接続文字列で指定されたアプリケーション名を決定します。アプリケーション名が limited_tempdb_application に設定されている場合、関数は使用するワークロードグループの名前として limited_tempdb_space_group を返します。それ以外の場合、この関数はワークロードグループ名として 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_tempdb_application'
    SELECT @WorkloadGroupName = N'limited_tempdb_space_group';

RETURN @WorkloadGroupName;

END;
GO
```
分類子関数を使用するようにリソースガバナーを変更し、新しい構成を使用するようにリソースガバナーを再構成します。
```
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
```
limited_tempdb_space_group ワークロードグループに分類された新しいセッションを開きます。
1. SQL Server Management Studio (SSMS) で、メインメニューの [ファイル ]、[新規 ]、[データベースエンジンクエリ ]を選択します。
2. [ データベースエンジンへの接続 ] ダイアログで、前の手順でワークロードグループと分類子関数を作成したのと同じデータベースエンジンインスタンスを指定します。
  
  [追加接続パラメータ] タブを選択し、「App=limited_tempdb_application」と入力します。これにより、SSMS はインスタンスに接続するときにアプリケーション名として limited_tempdb_application を使用します。分類子の APP_NAME() 関数もこの値を返します。
3. [ 接続] を選択して新しいセッションを開きます。
前の手順で開いたクエリウィンドウで、次のステートメントを実行します。出力には、セッションが limited_tempdb_space_group ワークロードグループに分類されていることが示されます。
```
SELECT wg.name AS workload_group_name
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id = @@SPID;
```
同じクエリウィンドウで次のステートメントを実行します。
```
SELECT REPLICATE('S', 100) AS c
INTO #t1;
```
ステートメントは正常に完了します。同じクエリウィンドウで次のステートメントを実行します。
```
SELECT REPLICATE(CAST ('F' AS NVARCHAR (MAX)), 1000000) AS c
INTO #t2;
```
このステートメントは、ワークロードグループの 1 MB tempdb 領域消費制限を超えようとするため、エラー 1138 で中止されます。
tempdb ワークロードグループによる現在およびピークlimited_tempdb_space_group領域の消費量を確認します。
```
SELECT group_id,
       name,
       tempdb_data_space_kb,
       peak_tempdb_data_space_kb,
       total_tempdb_data_limit_violation_count
FROM sys.dm_resource_governor_workload_groups
WHERE name = 'limited_tempdb_space_group';
```
total_tempdb_data_limit_violation_count列の値は 1 で、リソースガバナーによってtempdb領域の消費量が制限されたため、このワークロードグループ内の 1 つの要求が中止されたことを示しています。

必要に応じて、この例の初期構成に戻すには、 limited_tempdb_space_group ワークロードグループを使用してすべてのセッションを切断し、次の T-SQL スクリプトを実行します。

/* Disable resource governor so that the classifier function can be dropped. */
ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP FUNCTION IF EXISTS dbo.rg_classifier;

/* Drop the workload group. This requires that no sessions are using this workload group. */
DROP WORKLOAD GROUP limited_tempdb_space_group;

/* Reconfigure resource governor to reload the effective configuration without the classifier function and the workload group. This enables resource governor. */
ALTER RESOURCE GOVERNOR RECONFIGURE;

/* Disable resource governor to revert to the initial configuration. */
ALTER RESOURCE GOVERNOR DISABLE;

SSMS では [追加の接続パラメーター] タブに接続パラメーター が保持されるため、次に同じデータベースエンジンインスタンスに接続するときに、必ず App パラメーターを削除してください。これにより、接続が存在する場合に、 limited_tempdb_space_group ワークロードグループに分類されるのを回避できます。

次の方法で共有

チュートリアル: tempdb 領域リソース ガバナンスを構成する例

default ワークロード グループの固定制限を設定する

default ワークロード グループの割合制限を設定する

ユーザー定義ワークロード グループの固定制限を設定する

関連コンテンツ

フィードバック

その他のリソース

チュートリアル: tempdb 領域リソースガバナンスを構成する例

`default` ワークロードグループの固定制限を設定する

`default` ワークロードグループの割合制限を設定する

ユーザー定義ワークロードグループの固定制限を設定する