通过

  • 项目

资源调控器工作负荷管理方案

可以按各种方式使用资源调控器来监视和管理 SQL Server 系统上的工作负荷。本主题概述了如何配置资源调控器并举例说明如何使用资源调控器。所提供的方案包括用于创建和更改工作负荷组和资源池的 Transact-SQL 代码示例。

配置资源调控器

安装 SQL Server 2008 后，即可使用资源调控器，但需要先启用。内部和默认工作负荷组及其相应资源池已存在。

若要创建和使用自己的资源池和工作负荷组，必须完成以下步骤：

  • 创建一个具有指定限制的资源池。

  • 创建一个具有指定限制和策略的工作负荷组，并标识该工作负荷组所属的资源池。

  • 定义并激活一个要用于传入请求的分类器函数。

完成上述步骤后，便可看到活动的资源调控器配置和已分类的所有活动请求的状态。

确定资源池和工作负荷组要求

问题是要确定希望在 SQL Server 环境中使用的工作负荷组和资源池配置。为此，必须了解应用程序目前对 SQL Server 资源的占用情况。

解决方案是创建一个位于默认资源池中的工作负荷组。先运行应用程序若干天，然后使用收集的信息来回答以下问题。

  • CPU 要求是什么？
    确定 CPU 带宽估计值，方法是通过每个请求的平均 CPU 总使用率乘以每秒平均请求数。

    可以使用最大、平均和标准 CPU 使用率偏差来确定是否需要最大 CPU 限制。

  • 并发请求数是多少？
    使用最大和平均并发请求数统计信息来确定最小并发因子。

  • 所需的总内存是多少？
    借助所用的最大和平均内存总量来确定内存要求。

  • 单个查询需要多少内存？
    使用每个查询的最大和平均内存统计信息来确定所需的内存量。

  • 是否存在查询因内存不足而未运行？
    使用平均内存队列等待时间统计信息来确定是否有任何查询因内存可用性而被阻止。

方案

我们提供了七个方案作为示例。方案 1 到 5 是累积性的；每个方案都基于前面的方案。方案 6 与前面的方案无关，而方案 7 基于方案 6。

方案 1

问：我刚刚安装了 SQL Server 新版本，希望使用资源调控器。如何在我的环境下使用？

答：请考虑使用资源调控器监视工作负荷的资源消耗情况。请执行下列步骤来设置资源调控器环境。下面是一个配置示例。

  1. 针对您的工作负荷创建工作负荷组。

  2. 创建一个分类函数。

  3. 使用资源调控器注册该分类函数。

  4. 启用资源调控器。

  5. 监视资源调控器性能计数器并查询将返回有关工作负荷组的资源使用情况信息的 DMV。

示例

注意注意

下面的配置没有指定工作负荷组应使用的资源池。默认情况下，工作负荷组将使用默认池。

 
BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

方案 2

问：根据方案 1 的监视结果，我希望随时查看特殊组 (GroupAdhoc) 中查询的运行时间超过 30 秒的事件。

答：按照下列步骤更改当前的资源调控器配置。下面是一个配置示例。

  1. 为特殊组设置 CPU 使用率限制。

  2. 监视 SQL 跟踪事件（资源调控器管理类事件）。

  3. 对事件执行操作。例如，忽略事件、发送电子邮件、发送页面或对请求执行 KILL 命令。

示例

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

方案 3

问：我希望进一步限制特殊组，以便在累积所有请求时该组的 CPU 使用率不超过 50%。

答：由于前面的方案使用的是默认池，因此必须创建新的资源池。按照下列步骤更改当前的资源调控器配置。下面是一个配置示例。

  1. 创建一个新的资源池并配置 CPU 限制。

  2. 配置特殊工作负荷组用于新的资源池。

示例

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

方案 4

问：现在我想保证管理组始终有运行诊断查询的资源，但是这些查询占用的服务器内存资源不能超过 10%。

答：为了执行此操作，必须创建新的资源池。按照下列步骤更改当前的资源调控器配置。下面是一个配置示例。

  1. 创建一个新的资源池并设置资源限制。

  2. 配置管理工作负荷组用于新的池。

示例

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

方案 5

问：由于我的报表当前位于默认资源池中，但使用单独的工作负荷组 (GroupReports)，因此，我希望确保默认资源组中的所有内容的资源使用优先级都比报表低。

答：若要实现此目的，必须在默认组中配置 IMPORTANCE 设置。

注意注意

您可以更改默认组的设置，但不能更改默认池。如果您认为需要更改默认池，则充分说明了您需要创建新的资源池。

按照下列步骤更改当前的资源调控器配置。下面是一个配置示例。

  1. 更改默认组的设置。

示例

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

方案 6

问：我想要创建一个使用现有资源池的新工作负荷组。然后我希望更改分类器函数，以便将该组移到新的资源池。

答：使用以下步骤来设置此新的资源调控器环境。下面是一个配置示例。

  1. 使用默认设置创建新的资源池。

  2. 创建一个位于现有池中的新工作负荷组。

  3. 创建并注册一个用于处理请求的新分类器函数。

示例

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

方案 7

问：我决定不再需要工作负荷组（在方案 6 中创建），以便删除工作负荷组及其所在的资源池。

答：使用以下步骤可以更改资源调控器环境。下面是一个配置示例。

  1. 创建并注册新的分类器函数，将请求分类到其余的工作负荷组。

  2. 删除工作负荷组。

  3. 删除资源池。

  4. 应用配置更改。

示例

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO