共用方式為

教學課程：設定 tempdb 空間資源治理的範例

適用於： SQL Server 2025 （17.x） 預覽和更新版本

本文中的範例說明如何設定空間耗用量的限制 tempdb ，並檢視每個工作負載群組的空間耗用量 tempdb 。

如需空間資源治理的 tempdb 簡介，請參閱 Tempdb空間資源治理。

這些範例旨在協助您熟悉 tempdb 測試、非生產環境中的空間資源治理。

範例假設資源管理員一開始未啟用，且其設定不會從預設值變更。 它們也會假設，在您執行腳本時，您 SQL Server 伺服器執行個體上的任何其他工作負載都不會大幅度地貢獻tempdb空間耗用量。

設定工作負載群組的 default 固定限制

此範例會將工作負載群組中tempdb要求 （queries） 的總default空間耗用量限製為固定限制。

  1. 修改default工作量群組，以設定tempdb空間使用量的固定 20 GB 限制。

    ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);

  2. 啟用資源管理員，讓目前的組態生效。

    ALTER RESOURCE GOVERNOR RECONFIGURE;

  3. 檢視空間耗用量的限制 tempdb 。

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

  4. 檢查工作負載群組目前的 tempdb 空間耗用量 default 、藉由建立臨時表並插入一個數據列來新增數據 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';

  5. 可選地，移除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空間耗用量限制限制為百分比限制。

  1. 請將所有FILEGROWTH資料檔的MAXSIZE和tempdb設定為符合需求，並限制tempdb的最大大小為 1 GB。

    此範例假設 tempdb 有四個數據檔。 如果您的 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);

  2. 修改 default 工作負載群組，以設定 tempdb 空間耗用量的 5% 限制。 大小上限 tempdb 為 1 GB，群組空間 default 限制在約 51 MB tempdb。

    ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_PERCENT = 5);

  3. 如果已設定固定限制，請將其移除，使其不會取代百分比限制。

    ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);

  4. 啟用資源管理員，讓設定生效。

    ALTER RESOURCE GOVERNOR RECONFIGURE;

  5. 檢視空間耗用量的限制 tempdb 。

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

  6. 在tempdb中添加數據以達到上限。

    SELECT *
INTO #m
FROM sys.messages;

    語句已因錯誤 1138 中止。

  7. 檢查 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 有一個請求被中止，因為其 tempdb 空間使用量受到資源管理器的限制。

  8. 可選地，移除default群組的限制，並停用資源管理器，以恢復tempdb中的非受控空間使用。

    ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);

ALTER RESOURCE GOVERNOR DISABLE;

  9. 選擇性地還原 tempdb 本範例稍早所做的數據檔組態變更。

設定用戶定義工作負載群組的固定限制

此範例會建立新的工作負載群組，然後建立分類器函式，將具有特定應用程式名稱的會話指派給此工作負載群組。

基於此範例的目的，工作負載群組空間耗用量的 tempdb 固定限制會設定為 1 MB 的小型值。 然後，此範例會顯示嘗試在 tempdb 分配超過限制的空間時，這個操作會被中止。

  1. 建立工作負載群組，並將其 tempdb 空間耗用量限製為1 MB。

    CREATE WORKLOAD GROUP limited_tempdb_space_group
WITH (GROUP_MAX_TEMPDB_DATA_MB = 1);

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

  3. 修改資源管理員以使用分類器函式，然後重新設定資源管理員以使用新的組態。

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

  4. 開啟分類至 limited_tempdb_space_group 工作負載群組的新會話。

    1. 在 SQL Server Management Studio（SSMS）中，選取主功能表上的 [檔案]，[新增]，[資料庫引擎查詢]。

    2. 在 [ 連接到 Database Engine] 對話框中，指定您在先前步驟中建立工作負載群組和分類器函式的相同 Database Engine 實例。

      選擇 其他連線參數 索引標籤，然後輸入 App=limited_tempdb_application。 這可讓 SSMS 在連線到實例時，使用 limited_tempdb_application 作為應用程式名稱。 分類器中的函 APP_NAME() 式也會傳回此值。

    3. 選取 [連線 ] 以開啟新的工作階段。

  5. 在上一個步驟中開啟的查詢視窗中執行下列語句。 輸出應該會顯示您的工作階段已分類至 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;

  6. 在同一個查詢視窗中執行下列語句。

    SELECT REPLICATE('S', 100) AS c
INTO #t1;

    陳述成功完成。 在相同的查詢視窗中執行下列語句：

    SELECT REPLICATE(CAST ('F' AS NVARCHAR (MAX)), 1000000) AS c
INTO #t2;

    陳述式已因錯誤 1138 而中止，原因是它嘗試超過工作負載組的一兆位元組 tempdb 的空間耗用限制。

  7. 查看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 空間耗用量受限於資源管理員。

  8. 或者，若要還原為此範例的初始設定，請使用 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 會在 [ 其他連接參數 ] 索引標籤中保留連線參數，因此請務必在下一次連線到相同的 Database Engine 實例時移除 App 參數。 這可以避免您的連線被分類到limited_tempdb_space_group工作負載群組中，如果它存在。

相關內容