数据库系统的资源是指内存和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,修改分类函数
修改分类函数,需要先禁用资源管理器,然后解除分类函数和资源管理器的关联关系,然后修改分类函数,把资源管理器和分类函数重新绑定:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
三,查看资源管理器
通过以下三个视图查看资源管理器的元数据:
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示例脚本:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
参考文档:
Resource Governor in SQL Server 2012
使用sql server 2008 资源调控器限制指定用户查询所使用CPU资源
CREATE RESOURCE POOL (Transact-SQL)