控制 Memory 和 CPU 资源的使用

转载自我的博客：控制 Memory 和 CPU 资源的使用

Resource Governor的出现，解决了在一台SQL Server实例上，管理多用户工作负载和资源隔离的需求，它允许管理员限制系统处理Requsts时所耗费的CPU 和 Memory资源的数量，在一定程度上，限制和隔离了runaway查询。对于SQL Server 2012，用户能够基于工作负载，实现CPU资源的完全隔离，并能设置CPU资源使用量的硬上限（CAP Usage，Hard Limit）。在一个多用户，高并发的SQL Server实例上，管理员使用Resource Governor，控制不同工作负载对内存和CPU资源的使用量，使不同的应用程序在使用的资源上相互隔离，使系统性能得到可预测性的控制和保证。

一，基本实现（fundamental implementation）

Resource Governor的可编程部分由三部分组成：Resource Pool，Workload Group 和 Classifier Function，每个部分实现不同的功能。

1，资源池（Resource Pool）

在SQL Server实例中，资源隔离的基本单位是Resource Pool，正如其名，该对象是资源池，在创建Resource Pool时，指定该Pool拥有的CPU和Memory资源的数量范围。在SQL Server 2012版本中，最多可以创建62个用户自定义的Resource Pool。SQL Server内置了两个Resource Pools：internal用于系统Task，用户不能配置；default是默认的Resource Pool，用于任何没有指定Resource Pool的Request；
2，负载分组（Workload Group）

Workload Group是逻辑上的实体，用于表示一个或多个工作负载。实际上，一个工作负载是SQL Server实例接收到的一个查询请求（Request），通过Classifier Function将多个具有共同属性的Requests划分到相同的Workload Group中。每一个Resource Pool服务于一个或多个工作负载分组，这就是说，这些工作负载分组能够共享同一个Resource Pool中拥有的资源。

SQL Server内置两个负载分组：internal和default，关联到相应的internal和default资源池，internal负载分组用于系统Task，SQL Server将没有被分类函数显式指定负载分组的Request划分到default 分组中。

3，分类函数（Classifier Function）

分类函数根据Login，应用程序名称，数据库名字等属性，将Request划分到不同的负载分组中，可以指定用户定义的负载分组或default负载分组。

4，处理流程

Resource Governor各个部分相互配合，控制内存和CPU资源的使用：Classification将SQL Server实例接收到的Requests进行分类，划分到不同的负载组中，负载组与之关联的Resource Pool中包含的CPU和内存资源来处理Request，Resource Governor的处理流程如下图： 

Resource Pool 是SQL Server实例中物理资源的子集，由于位于同一个实例上的所有数据库共享该实例的所有资源，因此，最好将Resource Pool的三个组成对象创建在master 数据库中。

二，使用示例

1， 创建Resource Pool

CREATE RESOURCE POOL rp_20Percent
WITH 
(
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 20,
     CAP_CPU_PERCENT = 20,
     AFFINITY SCHEDULER = auto,
     MIN_MEMORY_PERCENT = 0,
     MAX_MEMORY_PERCENT = 20
);

CAP_CPU_PERCENT选项设置资源池拥有CPU资源的硬上限，任何Workload Group使用的CPU数量不可能超过该上限，而资源池使用的CPU资源有可能超过 MAX_CPU_PERCENT 选项指定的比例。

2，创建Workload Group

创建工作负载分组，通过using子句关联该分组能够使用的资源池，一个工作负载分组只能关联一个资源池，一个资源池服务一个或多个工作负载分组。

CREATE WORKLOAD GROUP wg_20Percent
WITH
(
    IMPORTANCE = MEDIUM,
    REQUEST_MAX_MEMORY_GRANT_PERCENT=20,
    REQUEST_MAX_CPU_TIME_SEC=0,
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,
    MAX_DOP=0,
    GROUP_MAX_REQUESTS=0
)
USING rp_20Percent;

IMPORTANCE选项： 该选项指定该Workload Group在Resource Pool中相对的重要性，由于同一个Resource Pool关联多个Workload Group，Importance数值高的Workload Group，在竞争Resource Pool中的资源时，更容易获胜。该选项有三个可选值：Low，Medium和High，默认值是Medium。

3，创建 Classifier Function

该分类函数根据用户名称，将Request划分到wg_20Percent负载分组，默认情况下，任何没有指定Workload Group的Request，都使用Default资源池。在实际产品环境中，也可以使用APP_NAME()获取应用程序名字，根据应用程序划分负载分组。

CREATE FUNCTION dbo.rgClassifierFunction_20Percent() 
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Workload_Group_Name AS sysname
      IF (SUSER_NAME() = 'USER_READONLY')
          SET @workload_group_name = 'wg_20Percent'
    RETURN @workload_group_name
END;

4，启动Resource Governor

首先，配置Resource Governor使用的分类函数；然后，执行重新配置命令，启动Resource Governor，SQL Server使用分类函数对SQL Server实例接收的Requests，划分到不同的负载分组。

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

参考文档：

Resource Governor

Resource Governor in SQL Server 2012

使用sql server 2008 资源调控器限制指定用户查询所使用CPU资源

CREATE RESOURCE POOL (Transact-SQL)

CREATE WORKLOAD GROUP (Transact-SQL)

ALTER RESOURCE GOVERNOR (Transact-SQL)