内存和CPU资源控制

数据库系统的资源是指内存和CPU（处理器）资源，拥有资源的多寡，决定了数据查询的性能。当一个SQL Server实例上，拥有多个独立的工作负载（workload）时，使用资源管理器（Resource Governor），能够实现系统资源在逻辑上的隔离，解决在一台SQL Server实例上，管理多用户工作负载的需求。资源管理器允许数据库管理员（DBA）通过编程设置资源池，配置资源池拥有资源的上限，资源池是每一个请求能够使用的资源，这样设置之后，强制系统在处理用户发送的请求（Requsts）时所耗费的CPU 和 Memory资源的数量不能超过限制，在一定程度上，限制用户能够使用的资源数量，隔离了失控（runaway）的查询对系统的影响。对于SQL Server 2012来说，用户能够基于工作负载，实现CPU资源的完全隔离，并能设置CPU资源使用量的硬上限（CAP Usage，Hard Limit）。在一个多用户、高并发的SQL Server实例上，管理员使用Resource Governor，控制不同工作负载对内存和CPU资源的使用量，使不同的应用程序在资源的使用上相互隔离，使系统性能得到可预测性的控制和保证。

一，资源管理器的基本构成

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）

分类函数根据指定的规则（Rule），例如，根据Login，应用程序名称，数据库名字等属性，将接收的Request分配（路由）到不同的负载分组中，可以指定用户定义的负载分组或default负载分组。

4，处理流程

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

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

二，创建和使用资源管理器

资源管理器默认是关闭的，在使用之前，必须启用。用户可以通过SSMS启用资源管理器，展开Management，选择Resource Governor，右击弹出快捷菜单，点击“Enable”，启用资源管理器：

或者，使用TSQL命令，重新配置，启用资源管理器：

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

1，创建自定义的资源池（Resource Pool）

SQL Server 内置两个资源池：internal和default，internal是系统内部使用的，default是默认的资源池，当一个查询请求没有指定资源池时，使用默认的资源池。为了管理用户不同的工作负载，DBA需要根据业务需求创建自定义的资源池。在创建资源池时，需要注意，资源池的选项都是比例关系，所有资源的下限（MIN_MEMORY_PERCENT或MIN_CPU_PERCENT）的加和不能超过系统的物理资源总量，即不能超过100。

CREATE RESOURCE POOL rp_20Percent
WITH 
(
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 40,
     CAP_CPU_PERCENT = 40,
     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选项：有三个可选值：Low，Medium和High，默认值是Medium，该选项用于指定该Workload Group在关联的资源池（Resource Pool）中相对的重要性，由于同一个可以Resource Pool关联多个Workload Group，当一个工作负载组（Workload Group）的Importance数值高于其他工作负载组（Workload Group）时，在竞争Resource Pool中的资源时，更容易获胜。当前工作负载组的IMPORTANCE的选项不会使用其他资源池，也不会影响其他资源组中的工作负载。

REQUEST_MAX_MEMORY_GRANT_PERCENT：默认值是25，用于指定工作负载组能够从资源组中申请到的最大授予内存（Grant Memory）资源，由于授予内存跟执行的SORT和HASH JOIN操作有关，如果设置REQUEST_MAX_MEMORY_GRANT_PERCENT为0，这意味着，SQL Server引擎将不允许执行排序和哈希链接操作。

MAX_DOP：用于指定并发查询的最大并发度（Degree of Parallelism），默认值是0，表示使用全局配置（Global Setting）。

3，创建分类函数（Classifier Function）

分类函数根据特定的规则（Rule）把接收的request路由到不同的资源池中，用户应该把分类函数创建在master数据库中，这样，函数的作用域就是整个SQL Server实例，分类函数的执行上下文是master 数据库；如果分类函数返回NULL，default或不存在的工作负载组的名称，那么查询被路由导default资源池：

If the user-defined function returns NULL, default, or the name of non-existent group the session is given the default workload group context. The session is also given the default context if the function fails for any reason.
The function should be defined with server scope (master database).

例如，根据用户名称，将Request划分到wg_20Percent负载分组。默认情况下，任何没有指定Workload Group的Request，都分发到Default资源池。在实际产品环境中，也可以使用APP_NAME()获取应用程序名字，或者SUSER_NAME()获取用户的登陆（Login）名称，根据应用程序或登陆名称划分负载分组。

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;

常用于分类函数（Classifier Function）中的函数如下，这些函数的执行上下文是整个Server：

APP_NAME()：返回当前Session所在的应用程序的名称
HOST_NAME()：返回主机名称；
SUSER_NAME()：返回登陆名；
IS_SVROLEMEMBER()：当用户是特定的服务器角色时，返回True；

注意：列表中函数执行的上下文和分类函数的上下文相同，如果分类函数被创建在master数据库，那么分类函数的查询的范围是在master数据库。

执行上下文是单个数据库的函数是：

IS_MEMBER('role | group')
IS_ROLEMEMBER ( 'role' [ , 'database_principal' ] )

函数：IS_MEMBER('role | group')，用于检测当前的用户（User）是否属于当前数据库的role或windows group。通常情况下，我们把分类函数创建在master系统数据库中，这会导致函数的执行上下文是master数据库，而不是用户数据库，而master数据库中可能不存在windows group（数据库用户User），这会导致函数无法起到分类的作用：

IS_MEMBER doesn’t work here as it’ll be firing in the context of the master db, not the user database you’re interested in.

为了解决这个问题，必须在master数据库中创建相应的windows group（数据库用户User）。

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

5，修改分类函数

修改分类函数，需要先禁用资源管理器，然后解除分类函数和资源管理器的关联关系，然后修改分类函数，把资源管理器和分类函数重新绑定：

ALTER RESOURCE GOVERNOR
DISABLE;
GO
ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION=NULL);
GO

ALTER FUNCTION dbo.rgClassifierFunction_20Percent() 
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    ....
END;
GO

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

View Code

三，查看资源管理器

通过以下三个视图查看资源管理器的元数据：

sys.resource_governor_configuration
sys.resource_governor_workload_groups
sys.resource_governor_resource_pools

通过以下三个DMV查看资源管理器的统计数据：

sys.dm_resource_governor_configuration
sys.dm_resource_governor_workload_groups
sys.dm_resource_governor_resource_pools

DMV sys.dm_exec_requests 和 sys.dm_exec_sessions 中包含group_id字段，用于表示当前session或request处于的工作负载分组。

MSDN示例脚本：

CREATE FUNCTION dbo.rgclassifier_v1() 
RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
-- Declare the variable to hold the value returned in sysname.
    DECLARE @grp_name AS sysname
-- If the user login is 'sa', map the connection to the groupAdmin  workload group. 
    IF (SUSER_NAME() = 'sa')
        SET @grp_name = 'groupAdmin'
-- Use application information to map the connection to the groupAdhoc workload group.
    ELSE IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
        OR (APP_NAME() LIKE '%QUERY ANALYZER%')
            SET @grp_name = 'groupAdhoc'
-- If the application is for reporting, map the connection to the groupReports workload group.
    ELSE IF (APP_NAME() LIKE '%REPORT SERVER%')
        SET @grp_name = 'groupReports'
-- If the connection does not map to any of the previous groups, put the connection into the default workload group.
    ELSE
        SET @grp_name = 'default'
    RETURN @grp_name
END;
GO

-- Register the classifier user-defined function and update the 
-- the in-memory configuration.
ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

View Code

参考文档：

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)

posted @ 2017-01-16 20:30 悦光阴阅读(3209) 评论(0) 编辑收藏举报

刷新页面返回顶部

悦光阴

请不要假装很努力，因为结果不会陪你演戏。

内存和CPU资源控制

公告