首页 > 代码库 > 控制 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_20PercentWITH (     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_20PercentWITH(    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中的资源时,更容易获胜。

3,创建 Classifier Function

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

CREATE FUNCTION dbo.rgClassifierFunction_20Percent() RETURNS sysnameWITH SCHEMABINDINGASBEGIN    DECLARE @Workload_Group_Name AS sysname      IF (SUSER_NAME() = USER_READONLY)          SET @workload_group_name = wg_20Percent    RETURN @workload_group_nameEND;

4,启动Resource Governor

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

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

 

MSDN示例脚本:

技术分享
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname WITH SCHEMABINDINGASBEGIN-- 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_nameEND;GO-- Register the classifier user-defined function and update the -- the in-memory configuration.ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);GOALTER 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)

控制 Memory 和 CPU 资源的使用