教程：资源调控器配置示例和最佳做法

适用于：SQL ServerAzure SQL 托管实例

本文包含演练示例，可帮助你配置资源调控器并验证配置是否按预期工作。 它从一个简单的示例开始，并进展到更复杂的示例。

本文还包括资源调控器 监视查询的示例 以及资源调控器列表 最佳做法。

所有示例都假定最初禁用资源调控器并使用默认设置，并且不存在用户定义的资源池、工作负荷组和分类器函数。

注意

对于 Azure SQL 托管实例，必须处于 master 数据库的上下文中才能修改 Resource Governor 配置。

修改默认组

此示例使用资源调控器限制所有用户查询的最大内存授予大小。 为此，将 default 工作负荷组的 REQUEST_MAX_MEMORY_GRANT_PERCENT 设置从默认 25% 减少到 10%。 该示例不使用 分类器函数。 这意味着登录处理不会受到影响，并且所有用户会话将继续在 default 工作负荷组中进行分类。

如果查询正在等待内存，则可能需要限制内存授予的大小，因为其他查询保留过多的内存。 有关详细信息，请参阅 排查 SQL Server中内存授予导致的性能缓慢或内存不足问题。

1. 修改默认工作负荷组。

   ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 10);
   

2. 启用资源调控器使我们的配置生效。

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

3. 验证新设置，包括内存分配的最新的最大值。

   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;
   

4. 若要还原到初始配置，请执行以下脚本：

   ALTER RESOURCE GOVERNOR DISABLE;
   ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25);
   

使用用户定义的工作负荷组

此示例使用资源调控器来确保所有具有特定应用程序名称的会话请求的执行并行度（DOP）不会超过 4。 为此，请将会话分类为工作负载组，并将 MAX_DOP 设置设置为 4。

有关配置最大并行度的详细信息，请参阅 服务器配置：最大并行度。

1. 创建限制 DOP 的工作负载组。 组使用 default 资源池，因为我们只想限制特定应用程序的 DOP，但不保留或限制 CPU、内存或 I/O 资源。

   CREATE WORKLOAD GROUP limit_dop
   WITH (
        MAX_DOP = 4
        )
   USING [default];
   

2. 创建分类器函数。 该函数使用内置 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
   

3. 修改资源调控器配置以使配置有效，并启用资源调控器。

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

4. 查询 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
   

5. 验证具有特定应用程序名称的会话是否被分类到 limit_dop 工作负荷组，而其他会话将继续在 default 工作负荷组中进行分类。 我们将使用一个查询，利用 sys.dm_exec_sessions 和 sys.resource_governor_workload_groups 系统视图，返回当前会话的应用程序名称和工作负载组名称。

   1. 在 SQL Server Management Studio（SSMS）的主菜单上选择 文件，新建，数据库引擎查询。

   2. 在 连接到数据库引擎 对话框中，指定创建工作负荷组和分类器函数的同一数据库引擎实例。 选择 其他连接参数 选项卡，然后输入 App=limited_dop_application。 这使得 SSMS 在连接到实例时使用 limited_dop_application 作为应用程序名称。

   3. 选择 连接 打开新连接。

   4. 在同一查询窗口中，执行以下查询：

      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;
      

      应会看到以下输出，显示会话已分类到 limit_dop 工作负荷组，最大 DOP 设置为 4：

      application_name            workload_group_name     max_dop
      ----------------            -------------------     -------
      limited_dop_application     limit_dop               4
      

   5. 重复上述步骤，但不要在“附加连接参数”选项卡中的框中输入任何内容。输出会发生变化，显示默认的 SSMS 应用程序名称和 default 工作负载组，其中包括表示最大 DOP 的默认 0 值。

      application_name                                    workload_group_name     max_dop
      ----------------                                    -------------------     -------
      Microsoft SQL Server Management Studio - Query      default                 0
      

6. 若要还原到此示例的初始配置，请执行以下 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;
   

使用多个资源池和工作负载组

此示例使用资源管理器根据一天中的不同时间，将来自订单处理应用程序的会话分类到不同的工作负荷组和资源池中。 此配置在高峰处理时间将更多资源分配给应用程序，并在非工作时间限制其资源。 该示例假定应用程序不使用长时间运行的会话。

1. 为高峰时段和非工作时间处理创建两个资源池。

   • peak_hours_pool 池通过 MIN_CPU_PERCENT保证（保留）至少 20% 的平均 CPU 带宽，并且不会通过将 MAX_CPU_PERCENT 设置为 100来限制 CPU 带宽。
   • off_hours_pool 池通过将 MIN_CPU_PERCENT 设置为 0不保留任何 CPU 带宽，但在出现 CPU 争用时，通过将 MAX_CPU_PERCENT 设置为 50，将 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。

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。

3. 创建并填充一个表，该表定义高峰和休假时间间隔。

   • 表中的每一行定义间隔的开始和结束时间，以及要在间隔期间使用的工作负荷组的名称。
   • 每个间隔的开始和结束时间都是包含在内的。
   • 该表在 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');
   

4. 创建分类器函数。

   • 表中的数据预计在任何给定的时间点都有一个匹配的行。 如果数据违反该规则，该函数将返回 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
   

5. 这是一个可选步骤。 可以使用 表值构造函数 直接在分类器函数中定义时间间隔，而不是在 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
   

6. 修改资源调控器配置以使配置有效，并启用资源调控器。

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

7. 验证资源调控器是否已启用、是否使用指定的分类器函数，以及分类器函数是否按预期工作，如上一示例 所示。 这一次，我们在 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
   

8. 若要还原到初始配置，请执行以下 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;

Resource Governor 最佳做法

• 配置专用管理员连接（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()。 需要更改分类器逻辑时，请执行以下步骤：
  1. 使用 ALTER FUNCTION 语句对 Resource Governor 配置中当前未使用的函数进行更改。
  2. 使用 ALTER RESOURCE GOVERNOR 语句使修改后的分类器处于活动状态，然后重新配置资源调控器。 例如：

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

  3. 如果再次需要更改，请使用其他函数执行相同的步骤（dbo.rg_classifier_A()）。
• 资源调控器配置存储在 master 数据库中。 请确保定期备份 master，并知道如何还原它。 有关详细信息，请参阅 备份和还原：系统数据库。 由于还原 master存在限制，因此建议单独保存资源调控器配置脚本的副本。 如果需要重新生成 master 数据库，可以从脚本重新创建资源调控器配置。

相关内容

• Resource Governor
• 启用资源调控器
• Resource Governor 资源池
• Resource Governor 工作负载组
• 使用模板 配置资源调控器
• 查看和修改资源调控器属性
• ALTER RESOURCE GOVERNOR
• 创建资源池
• CREATE WORKLOAD GROUP
• CREATE FUNCTION