SQLSERVER中的资源调控器

SQLSERVER中的资源调控器

一个资源池可以有多个组
resource_pools有多个workload_groups
一个resource_pools只有一个workload_groups情况下dm_resource_governor_resource_pools的pool_id==sys.dm_exec_sessions的group_id

--查看Session所在的资源池
select s.session_id,s.login_name ,s.program_name,s.group_id,g.name 
from 
sys.dm_exec_sessions s join sys.dm_resource_governor_workload_groups g
on s.group_id=g.group_id
where session_id>50 and s.group_id=256

--查看资源池情况
select * from sys.dm_resource_governor_resource_pools

select *  from sys.dm_resource_governor_workload_groups


--查看资源池情况
select * from sys.dm_resource_governor_resource_pools

select *  FROM sys.dm_resource_governor_workload_groups wg INNER JOIN sys.dm_resource_governor_resource_pools rp ON [rp].[pool_id] = [wg].[pool_id]

转载自：

http://wenku.baidu.com/view/0d92380cf78a6529647d5375.html

http://www.cnblogs.com/fygh/archive/2012/05/07/2489190.html

资源调控器是sql server 2008新增的功能，可以限制某些用户访问sql server所消耗的cpu、内存资源或是对某个库访问

所消耗的cpu、内存资源,可以在SQL Server 的 Enterprise Edition、Developer Edition 和 Evaluation Edition中使用。

配置资源调控器基本分为以下步骤：

（1） 创建并配置一个资源调控器资源池，发生 CPU 争用时，该资源池将限制分配给资源池中的请求的最大平均 CPU 带宽。

（2） 创建并配置一个使用该池的资源调控器工作负荷组。

（3） 创建一个“分类器函数”，它是一个用户定义函数 (UDF)，其返回值供资源调控器用来对会话进行分类，

以便将它们路由到适当的工作负荷组。

（4）将分类器函数注册到资源调控器。

（5）将更改应用于资源调控器进行配置。

你可以使用IS_MEMBER()、APP_NAME()、SUSER_SNAME()、CONNECTIONPROPERTY()等函数在分类器函数里编写您的业务逻辑

msdn中的示例

本测试示例是限制某查询用户USER_READONLY限制cpu最大为10%.

--配置资源调控器.
USE master
GO
-- 创建并配置一个资源调控器资源池，发生 CPU 争用时，
-- 该资源池将限制分配给资源池中的请求的最大平均 CPU 带宽为 10%
CREATE RESOURCE POOL pMAX_CPU_PERCENT_10   WITH (MAX_CPU_PERCENT = 10)
GO
-- 创建并配置一个使用该池的资源调控器工作负荷组。
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_10 USING pMAX_CPU_PERCENT_10;
GO
-- 创建一个“分类器函数”，它是一个用户定义函数 (UDF)，
-- 其返回值供资源调控器用来对会话进行分类，以便将它们路由到适当的工作负荷组
-- 本例是限制用户为:USER_READONLY所使用的cpu资源不超过10%

USE master
GO
CREATE FUNCTION rgclassifier_MAX_CPU ( )
RETURNS SYSNAME
    WITH SCHEMABINDING
AS 
    BEGIN
        DECLARE @workload_group_name SYSNAME
    --Handle workload groups defined by login names
        IF SUSER_SNAME() = 'USER_READONLY' 
            BEGIN
                SET @workload_group_name = 'gMAX_CPU_PERCENT_10';--gMAX_CPU_PERCENT_10 负载组
                RETURN @workload_group_name;
            END
        RETURN @workload_group_name;
    END





-- 将分类器函数rgclassifier_MAX_CPU注册到资源调控器 一定要加dbo架构名 不然会报错
Alter Resource Governor With(Classifier_Function=dbo.rgclassifier_MAX_CPU);
GO
-- 将更改应用于资源调控器
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO



--包含资源调控器的当前内存中配置状态的行
SELECT * FROM SYS.[dm_resource_governor_configuration]


--当前资源池状态、资源池的当前配置以及资源池统计信息的相关信息
SELECT  
[pool_id],
[name],
[statistics_start_time],
[max_cpu_percent] 
FROM SYS.[dm_resource_governor_resource_pools]



--工作负荷组统计信息和工作负荷组当前在内存中的配置
SELECT * FROM SYS.[dm_resource_governor_workload_groups]

一个资源池可以有多个组
resource_pools有多个workload_groups
一个resource_pools只有一个workload_groups情况下dm_resource_governor_resource_pools的pool_id==sys.dm_exec_sessions的group_id

--查看Session所在的资源池
select s.session_id,s.login_name ,s.program_name,s.group_id,g.name 
from 
sys.dm_exec_sessions s join sys.dm_resource_governor_workload_groups g
on s.group_id=g.group_id
where session_id>50 and s.group_id=256

--查看资源池情况
select * from sys.dm_resource_governor_resource_pools

select *  from sys.dm_resource_governor_workload_groups


--查看资源池情况
select * from sys.dm_resource_governor_resource_pools

select *  FROM sys.dm_resource_governor_workload_groups wg INNER JOIN sys.dm_resource_governor_resource_pools rp ON [rp].[pool_id] = [wg].[pool_id]




--这样，资源资源调控器配置完成，下面进行一下测试创建一个public帐号:
USE master
GO
CREATE LOGIN USER_READONLY WITH PASSWORD ='Nzperfect'
GO

--然后测试一下，执行下面的T-sql循环脚本
DECLARE @CNT INT
WHILE 1=1
BEGIN    
SELECT @CNT=COUNT(*) FROM sys.tables
END


--测试结束,删除测试
USE master
GO
DROP WORKLOAD GROUP gMAX_CPU_PERCENT_10
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
DROP RESOURCE POOL pMAX_CPU_PERCENT_10
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= null);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
--禁用并重置
ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR RESET STATISTICS
GO
DROP FUNCTION [dbo].[rgclassifier_MAX_CPU]
GO

这样，资源资源调控器配置完成，下面进行一下测试，创建一个登录帐号:USER_READONLY

我们用这个USER_READONLY帐号登陆SQLSERVER

在新建查询窗口执行一下脚本

可以看到,gMAX_CPU_PERCENT_10所占用的cpu立即下降到5%左右不会超过10%，说明我们配置的配置资源调控器已生效。

由上面的测试说明，当系统资源够用时，USER_READONLY像正常情况一下，sql server不会限制其使用的cpu资源，

但当存在资源竞争时，资源调控器将跟据配置的资源池及组信息自动调节，限制USER_READONLY使用的资源，以确保其它进程拥用更多的资源。

---

网上另一个流行的示例

USER_READONLY用户和sa用户分别调用同一个脚本，sa用户CPU为90%  USER_READONLY用户CPU为10%

------------------------------------------------------------------------

--创建资源池
Create Resource Pool AdminQueries with(max_cpu_percent=10)
Create Resource Pool UserQueries with(max_cpu_percent=90)

--创建负载组
Create WorkLoad Group [sa] USING UserQueries;
Create WorkLoad Group USER_READONLY USING AdminQueries;


--创建分类器函数
USE master
GO
create FUNCTION class_func_load()
Returns sysname with schemabinding
begin
    Declare @val sysname
    --Handle workload groups defined by login names
    IF SUSER_SNAME()='sa'
    begin
        SET @val='sa';--sa 负载组
        Return @val;
    end
        
    IF SUSER_SNAME()='USER_READONLY'
    begin
        Set @val='USER_READONLY';  --USER_READONLY负载组
        Return @val;
    end
    Return @val;
END

--将分类器函数绑定到资源调控器上 一定要加dbo架构名 不然会报错
Alter Resource Governor With(Classifier_Function = [dbo].class_func_load)
GO
--启用
ALTER RESOURCE GOVERNOR RECONFIGURE;




 
 
--测试脚本(分别使用sa和USER_READONLY用户调用此脚本）
set nocount on 
Declare @i int=100000000;
Declare @s varchar(100),@count int;
While @i>0
begin
Select @s=@@VERSION;
select @count=COUNT(0) from sys.sysobjects 
set @i=@i-1;
end    


--查看Session所在的资源池
select s.session_id,s.login_name ,s.program_name,s.group_id,g.name 
from 
sys.dm_exec_sessions s join sys.dm_resource_governor_workload_groups g
on s.group_id=g.group_id
where session_id>50
--查看资源池情况
select * from sys.dm_resource_governor_resource_pools

----------------------------------------

--查看Session所在的资源池
select s.session_id,s.login_name ,s.program_name,s.group_id,g.name 
from 
sys.dm_exec_sessions s join sys.dm_resource_governor_workload_groups g
on s.group_id=g.group_id
where session_id>50
--查看资源池情况
select * from sys.dm_resource_governor_resource_pools

--测试结束,删除测试
USE master
GO
--删除负荷组
DROP WORKLOAD GROUP [sa]
DROP WORKLOAD GROUP [USER_READONLY]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
--删除资源池
DROP RESOURCE POOL AdminQueries
DROP RESOURCE POOL UserQueries
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= null);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
--禁用并重置
ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR RESET STATISTICS
GO
--删除自定义函数
DROP FUNCTION [dbo].[class_func_load]
GO

---

介绍：

SQL2012增强了资源调控器的功能，反映了对集中管理的数据库服务的需求不断增长，

以便为需要隔离工作负荷的客户提供多租户模式。
SQLSERVER资源调控器在SQL2008 企业版引入的，用于为支持多个客户端工作负荷的单个SQLSERVER实例提供多租户模式和资源隔离。

通过资源调控器，您可对传入请求可使用的内存量和CPU资源设置限制，并且他提供一个方法来隔离和限制失控查询，

添加细粒度资源跟踪以便用于退款和提供可预测性能。

使用SQL2012，您可为工作负荷提供更完全的CPU资源隔离，对CPU使用量设置上限以便实现更高程度的可预测性，

并且可以控制更大比例的SQLSERVER内存分配

资源池、工作负荷组和分类
SQLSERVER资源调控器引入了“资源池”概念，作为在SQL实例内实现资源隔离的基本方式
可以通过TSQL以及SMO之类的其他管理界面对资源池进行控制，并且可以通过
CREATE RESOURCE POOL和ALTER RESOURCE POOL语句向资源池分配最小和最大CPU以及内存资源

SQLSERVER2012支持最多62个用户可定义资源池，与SQL2008中的18个相比大幅增加。
SQL2012还提供两个内置的资源池：一个叫“内部”，他是为系统任务保留的，不可配置
一个用户可配置的资源池叫“默认”，默认情况下在此资源池中运行工作负荷

每个用户资源池可与一个或多个“工作负荷组”相关联，这些工作负荷组是表示一个或多个客户端工作负荷的逻辑实体。

传入会话可通过在登录后运行的用户可定义的“分类器”函数与这些工作负荷组相关联，并且可调用系统函数以便评估不同登录属性，

例如用户名、工作站名称，数据库名称等

下面是一个例子，判断用户名、工作站、数据库名

CREATE FUNCTION rgclassifier_MAX_CPU ( )
RETURNS SYSNAME
    WITH SCHEMABINDING
AS 
BEGIN
    DECLARE @workload_group_name SYSNAME
    --Handle workload groups defined by login names
    IF SUSER_SNAME() = 'USER_READONLY'  --用户名是USER_READONLY
        BEGIN
            SET @workload_group_name = 'gMAX_CPU_PERCENT_10'
            RETURN @workload_group_name;
        END
    IF HOST_ID() = 'joe' --工作站名称是joe
        BEGIN
            SET @workload_group_name = 'gMAX_CPU_PERCENT_10';
            RETURN @workload_group_name;
        END
    IF DB_NAME() = 'GPOSDB' --数据库名称是GPOSDB
        BEGIN
            SET @workload_group_name = 'gMAX_CPU_PERCENT_10';
            RETURN @workload_group_name;
        END

    RETURN @workload_group_name;
END

 

SQL2012中的新资源调控器功能

为什麽更改资源调控器？

随着每个插槽中CPU内核数目的增加以及大型计算机成本的降低，我们听到了来自使用

资源调控器为客户隔离SQL资源的SQLSERVER托管合作伙伴的意见：

“在使用每个CPU具有10个和12个内核的新型商用服务器之后，

服务器上的CPU资源大量空闲，因为我们受制于资源池的最大数目。”

想要基于SQLSERVER向客户提供经济合算的数据库服务产品的主机托管服务提供商需要更为灵活地为工作负荷划分大型计算机上的可用资源，

并且尽可能减少工作负荷彼此争用资源以及一个工作负荷对其他工作负荷使用的资源有负面影响（即所谓的扰民邻居）等情况的发生

随着计算机以及虚拟化软件性能的增强，在私有化中虚拟化和管理SQLSERVER实例的数目和大小也在增加。

因此，向共享资源的用户提供更有效的隔离变得更加重要，工作负荷需要在SQLSERVER实例内的分区资源上运行，以避免发生“扰民邻居”问题

我们还看到了另一个趋势，就是针对资源使用的退款情况的增加。

现在，越来越多的企业和公共托管提供商对内部资源的使用进行跟踪和收费，而这就需要向资源的使用者提供可预测的计费

为了满足这些需要，在SQL2012中添加了下面功能：

（1）将资源池的最大数目从20增加到64（包括默认和内部），这样就能对更大的计算机进行分区，以便适用于更多的工作负荷。

（2）向资源池添加了CAP_CPU_PERCENT选项以便对CPU资源使用情况设置硬上限，从而提供更好的可预测性

（3）向资源池添加了AFFINITY选项，允许单独的资源池关联到一个或多个计划程序和NUMA节点，从而提供更好的CPU资源隔离

（4）SQLSERVER内存管理器负责调控多页分配以及单页分配。之所以这样是因为SQLSERVER2012中进行了一个重大的设计改动，

内存管理器现已成为用于页分配的中心操作系统接口。

设置资源池中的CPU容量上限

ALTER RESOURCE POOL pMAX_CPU_PERCENT_10   WITH (MAX_CPU_PERCENT = 10)

MAX_CPU_PERCENT设置是一种“投机性质”的最大值。如果有可用CPU容量，该工作负荷会一直用到100%。这个“最大值”仅存在CPU争用时才适用

如果你要硬性规定设置CPU上限为10%，可以使用CAP_CPU_PERCENT属性来设置资源池

该属性对CPU使用率设置硬上限

ALTER RESOURCE POOL pMAX_CPU_PERCENT_10   WITH (CAP_CPU_PERCENT = 10)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

所以为什麽在示例一里出现CPU占用超过10%就是这个原因

设置了ALTER RESOURCE POOL pMAX_CPU_PERCENT_10   WITH (CAP_CPU_PERCENT = 10)之后

我在SSMS里开了7个新建查询窗口，在性能监视器里看到CPU占用率始终保持在10%或以下

 

如果客户希望专门有一个CPU内核能够100%供他们随时使用。他们可以使用SQL2012中的另一个新的资源调控器功能：计划程序关联。

为了说明这个新功能，我们删除了应用于SQLSERVER的关联掩码，并且让SQLSERVER引擎使用该计算机的两个CPU

EXEC [sys].[sp_configure] @configname = 'affinity mask', -- varchar(35)
    @configvalue = 0 -- int
RECONFIGURE
GO

现在，您可以使用新资源池的AFFINITY选项将各资源池与自己的计划程序相关联。

在此情况下，该选项会使单个CPU内核有效地用于各工作负荷

ALTER RESOURCE POOL AdminQueries WITH(AFFINITY SCHEDULER=(0))
GO
ALTER RESOURCE POOL UserQueries WITH(AFFINITY SCHEDULER=(1))
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

在您运行此代码后，将在不同的CPU上计划工作负荷。

上面的示例说明了一些新的SQL2012资源调控器功能提供更有效的资源隔离

最佳做法
考虑应用程序资源整体消耗量
对于内存，资源调控器可以提供硬上限并且划分计算机资源。MAX_MEMORY_PERCENT资源池设置是针对内存使用率的一种有效的硬上限，

而不是“投机性质”的设置。内存调控的一个例外是缓冲池，他是共享的以便针对整体查询性能进行优化。资源调控器当前不管理I/O，

因此，需要使用其他方法以便尽量减少与I/O有关的“扰民邻居”问题，例如最大化内存、条带化、隔离I/O以及

使用有效的SAN针对随机I/O进行优化

避免在相同的CPU上共享工作负荷 在工作符号彼此争用时，计划程序可以很容易地划分公平和类似上限的工作负荷。

如果您需要为变化非常大的工作负荷调控资源，例如，高CPU与高I/O混用，请考虑使用计划程序关联对这些工作负荷进行划分，

而不是设置CPU最大值和上限

始终在更改CPU配置后重新配置池关联

您将资源池与一组SQLSERVER计划程序相关联，期望与他们隔离到特定CPU。在关闭计算机并且移除CPU后将会发生什么情况？

例如：您的SQL实例正在某一虚拟机中运行并且CPU使用率低，因此，您重新配置了虚拟机，减少了CPU数量

最好的情况是，计划程序关联设置不再精确，因为某一个资源池关联到的计划程序现在可能会指向不同CPU。

最糟的情况是，这些计划程序可能会指向不存在的CPU，这将导致资源调控器失败，或者导致关联默认为“自动”

因此，在您对CPU配置进行更改后，应该始终重新配置计划程序关联设置并且重新启动资源调控器

故障排除

sys.dm_resource_governor_resource_pool_ffinity

查看确切的计划程序CPU分配

我看不到SMO对象模型中定义的新的资源调控器功能 ？

如果您使用POWERSHELL或者C#来通过SMO SQLSERVER管理对象配置资源调控器，

将不能利用SQL2012中的新功能，新的SMO类在SQL2012 SP1中提供

当我在SSMS中编写资源池定义脚本时，他不显示新功能 ？

在SQL2012 SP1中添加了对SSMS的脚本编写功能的增强，以便显示CAP_CPU_PERCENT和计划程序关联之类的

新的资源调控器功能

---

创建资源调控器时的一些参数

USE master
GO
 
CREATE RESOURCE POOL [rp_WebApp]
WITH
(
MIN_CPU_PERCENT=50,
MAX_CPU_PERCENT =100,
MIN_MEMORY_PERCENT =50,
MAX_MEMORY_PERCENT =100
)
GO
 
CREATE WORKLOAD GROUP [rg_WebApp]
WITH
(
GROUP_MAX_REQUESTS=0,
IMPORTANCE=MEDIUM,
REQUEST_MAX_CPU_TIME_SEC=300,
REQUEST_MAX_MEMORY_GRANT_PERCENT=25,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,
MAX_DOP=0
)USING [rp_WebApp]
GO
 
CREATE RESOURCE POOL [rp_ReportApp]
WITH
(
MIN_CPU_PERCENT=25,
MAX_CPU_PERCENT =100,
MIN_MEMORY_PERCENT =25,
MAX_MEMORY_PERCENT =100
)
GO
 
 
CREATE WORKLOAD GROUP [rg_ReportApp]
WITH
(
GROUP_MAX_REQUESTS=0,
IMPORTANCE=MEDIUM,
REQUEST_MAX_CPU_TIME_SEC=300,
REQUEST_MAX_MEMORY_GRANT_PERCENT=25,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,
MAX_DOP=0
)USING [rp_ReportApp]
GO 
 
 
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=[dbo].[RGClassifier]
);
GO
 
ALTER RESOURCE GOVERNOR RECONFIGURE
GO 

 

简要说明一下：

1、  IMPORTANCE:定义工作负荷组中对请求处理的重要性，其值为LOW/MEDIUM/HIGH。

2、  GROUP_MAX_REQUESTS:定义在一个工作负荷组中最大并行执行的请求数量。

3、  MAX_DOP:在一个工作负荷组中并行请求的最大并行度。

4、  REQUEST_MAX_MEMORY_GRANT_PERCENT:对于一个工作负荷组中，一个单独的请求能用的最大内存数。

5、  REQUEST_MAX_CPU_TIME_SEC: 对于一个工作负荷组中，一个单独的请求可以使用的最大秒数。

6、  REQUEST_MEMORY_GRANT_TIMEOUT_SEC: 指定查询等待内存授予（工作缓冲区内存）变为可用的最长时间（以秒为单位）。

---

 

相关文章：

http://blog.csdn.net/dba_huangzj/article/details/8792468

http://blog.csdn.net/dba_huangzj/article/details/8792530

http://www.docin.com/p-676251001.html