SQL Server 2008新特性——资源调控器

╰+攻爆jí腚メ 2022-09-28 15:23 173阅读 0赞

资源调控器是sql server 2008新增中的功能，可以限制某些用户访问sql server所消耗的cpu、内存资源或是对某个库访问所所消耗的cpu、内存资源,可以在SQL Server 的 Enterprise Edition、Developer Edition 和 Evaluation Edition中使用。  
  
配置资源调控器基本分为以下步骤：  
1. 创建并配置一个资源调控器资源池，发生 CPU 争用时，该资源池将限制分配给资源池中的请求的最大平均 CPU 带宽。  
2. 创建并配置一个使用该池的资源调控器工作负荷组。  
3. 创建一个“分类器函数”，它是一个用户定义函数 (UDF)，其返回值供资源调控器用来对会话进行分类，以便将它们路由到适当的工作负荷组。  
4. 将分类器函数注册到资源调控器。  
5. 将更改应用于资源调控器内存中配置。  
  
本测试示例是限制某查询用户USER\_READONLY限制cpu最大为10%.  
示例代码(代码参考msdn联机文档)：

\--  配置资源调控器.   
 BEGIN   TRAN   
 USE  master;  
 \--  创建并配置一个资源调控器资源池，发生 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%   
 CREATE   FUNCTION  dbo.rgclassifier\_MAX\_CPU()  RETURNS  sysname  
 WITH  SCHEMABINDING  
 AS   
 BEGIN   
    DECLARE   @workload\_group\_name   AS  sysname  
       IF  ( SUSER\_NAME ()  =   ' USER\_READONLY ' )  
           SET   @workload\_group\_name   =   ' gMAX\_CPU\_PERCENT\_10 '   
     RETURN   @workload\_group\_name   
 END ;  
 GO   
![None.gif][]  
 \--  将分类器函数rgclassifier\_MAX\_CPU注册到资源调控器   
 ALTER  RESOURCE GOVERNOR  WITH  (CLASSIFIER\_FUNCTION =  dbo.rgclassifier\_MAX\_CPU);  
 COMMIT   TRAN ;  
 GO   
  
 \--  将更改应用于资源调控器内存中配置 ALTER  RESOURCE GOVERNOR  RECONFIGURE ;  
 GO

\--包含资源调控器的当前内存中配置状态的行  
select \* from sys.dm\_resource\_governor\_configuration

![2009-07-26_184744.jpg][]

\--当前资源池状态、资源池的当前配置以及资源池统计信息的相关信息

select \* from sys.dm\_resource\_governor\_resource\_pools

![2009-07-26_184854.jpg][]  
\--工作负荷组统计信息和工作负荷组当前在内存中的配置

select   \*   from  sys.dm\_resource\_governor\_workload\_groups

![2009-07-26_184908.jpg][]  
  
这样，资源资源调控器配置完成，下面进行一下测试  
创建一个public帐号:

USE  master  
 CREATE  LOGIN USER\_READONLY  WITH  PASSWORD  = ' Nzperfect '   
 GO

我们用这个USER\_READONLY帐号登陆sql server,  
![2009-07-26_185547.jpg][]  
  
然后测试一下，执行下面的T-sql循环脚本

DECLARE   @CNT   INT   
 WHILE   **1** = **1**   
 BEGIN   
     SELECT   @CNT = COUNT ( \* )  FROM  sys.tables  
 END   
![None.gif][]

打开性能监视器,并添加资源调器资源池cpu使用计数器,如下多图：  
![2009-07-26_185106.jpg][]  
![2009-07-26_185120.jpg][]  
![2009-07-26_185136.jpg][]  
![2009-07-26_185212.jpg][]  
![2009-07-26_185246.jpg][]

在以USER\_READONLY登陆执行T-sql脚本后,pMAX\_CPU\_PERCENT\_10资源池占用cpu为50%如下图：  
![2009-07-26_185651.jpg][]

然后，我们以sa帐号登陆sql server ,同时也执行上面的T-sql循环脚本，再看性能监视器计数，如图：![2009-07-26_185723.jpg][]

由上图可以看到,gMAX\_CPU\_PERCENT\_10所占用的cpu立即下降到5%左右，说明我们配置的配置资源调控器已生效。  
这时，如果取消sa执行的T-sql循环，则结果如下：  
![2009-07-26_185744.jpg][]  
  
由上面的测试说明，当系统资源够用时,USER\_READONLY像正常情况一下，sql server不会限制其使用的cpu资源，但当存在资源竞争时，资源调控器将跟据配置的资源池及组信息自动调节，限制USER\_READONLY使用的资源，以确保其它进程拥用更多的资源。  
  
\--测试结束,删除测试

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   
 DROP   FUNCTION   \[ dbo \] . \[ rgclassifier\_MAX\_CPU \]   
 GO

[None.gif]: /images/20220708/dc0c936cf1ed43b99c7d4c102748e976.png
[2009-07-26_184744.jpg]: /images/20220708/1fb8aa3308a84377979e866f27c447c6.png
[2009-07-26_184854.jpg]: /images/20220708/3e70738b9dfc45959302d315e6a29cd3.png
[2009-07-26_184908.jpg]: /images/20220708/a0ca1f1ddd97443fb671fc0504a846b1.png
[2009-07-26_185547.jpg]: /images/20220708/1d1be9d0ce1b49959dca56492b88a0ec.png
[2009-07-26_185106.jpg]: /images/20220708/89e2d006aab14438afe488602f5cee57.png
[2009-07-26_185120.jpg]: /images/20220708/635ffd78bc774f5390551c79daa59945.png
[2009-07-26_185136.jpg]: /images/20220708/9b85e78ca8884baf8dd3a8abcd6c37b4.png
[2009-07-26_185212.jpg]: /images/20220708/b624b93cda5b4e07a7de3c04e217c5f1.png
[2009-07-26_185246.jpg]: /images/20220708/d4aa2b0ae4e6472aae6483776a60fb7f.png
[2009-07-26_185651.jpg]: /images/20220708/0f08eb104b0c4f1a8400e7dba1d3febf.png
[2009-07-26_185723.jpg]: /images/20220708/aa8984acbc1f4a4db632c20375832a9a.png
[2009-07-26_185744.jpg]: /images/20220708/b92feb763aab4a26ba55e42fe2150863.png