教程：配置 tempdb 空间资源治理的示例

适用于： SQL Server 2025 （17.x） 预览版和更高版本

本文中的示例演示如何设置空间消耗限制 tempdb ，并查看每个工作负荷组的空间消耗 tempdb 量。

有关空间资源治理的 tempdb 简介，请参阅 Tempdb 空间资源治理。

这些示例旨在帮助你在测试的非生产环境中熟悉 tempdb 空间资源治理。

示例假定资源调控器最初未启用，并且其配置不会从默认值更改。 它们还假定 SQL Server 实例上的任何其他工作负荷在执行脚本时不会严重导致 tempdb 空间消耗。

为 default 工作负荷组设置固定限制

此示例将工作负荷组中请求（查询）tempdb的总default空间消耗量限制为固定限制。

1. 修改 default 工作负荷组，以配置固定的 20 GB 空间消耗限制 tempdb 。

   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”的 MODIFY FILE 失败，您可能需要重启 SQL Server 实例或减少 tempdb 使用量，因为 文件的大小...大于 MAXSIZE ...。

   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 工作负载组，以配置空间消耗的限制为 5%。 最大大小为 1 GB tempdb 时，这会将 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. 在 “连接到数据库引擎 ”对话框中，指定在前面步骤中创建工作负荷组和分类器函数的相同数据库引擎实例。

      选择 其他连接参数 选项卡，然后输入 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 而中止，因为它试图超过工作负荷组的 1 MB 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 在 “其他连接参数 ”选项卡中保留连接参数，因此请确保下次连接到同一数据库引擎实例时删除该 App 参数。 这样可避免连接被分类到 limited_tempdb_space_group 工作负荷组中（如果存在）。

相关内容

• Tempdb 空间资源治理
• 资源管理器
• 教程：资源调控器配置示例和最佳做法
• ALTER RESOURCE GOVERNOR （Transact-SQL）
• 创建工作负荷组 （Transact-SQL）
• ALTER WORKLOAD GROUP （Transact-SQL）
• DROP WORKLOAD GROUP（Transact-SQL）
• sys.resource_governor_workload_groups资源管理器工作负载组
• sys.dm_resource_governor_workload_groups