当前位置：首页 > 系统运维 > 正文内容

详解SQL Server 2008新特性Resource Governor

a8116255310年前 (2015-11-05)系统运维11

相信大家都遇到过，一个服务器上面运行多个数据库的情况，假如1个数据库占用资源过多，很可能直接导致另外一个数据库无法处理，直到超时的情况。过去这种情况基本无法处理（当然不排除使用三方程序处理的 *** ）。嘿嘿，在新的Sql Server 2008 中，就可以完美的解决这个问题了。答案就是Resource governor。

Resource Governor可以通过创建资源池（Resource Pool）的方式，对不同资源池分别分配服务器资源（cpu，内存），这里设置的是，资源池最繁忙时期的分配值。简单的说，现在我有两个数据库，DataBaseA和DataBaseB，那么我们可以创建两个资源池PoolA和PoolB，给PoolA分配10%的CPU和内存，PoolB分配90%的CPU和内存。那么当DataBaseA和DataBaseB都繁忙的时候，系统会分别分配相应的资源给他们，让他们都能够完成自己的工作（当然，性能可能有所下降，毕竟只使用10%的资源），从而避免了高并发性时，资源独占的情况。很好吧，下面我们就通过一个实例来给大家演示一下。

1. 首先可以查看一下当前资源池的配置情况

USE master;
GO 
SELECT * FROM sys.dm_resource_governor_configuration;
GO

假如如上图所示，那么说明目前没有创建任何资源池。（创建资源池的时候需要绑定一个classifier的函数，假如绑定了，会显示函数的ObjectID）

2. 创建2个资源池MarketingPool和DevelopmentPool,

CREATE RESOURCE POOL MarketingPool
WITH (MAX_CPU_PERCENT = 10);
GO 
CREATE RESOURCE POOL DevelopmentPool
WITH (MAX_CPU_PERCENT = 90);
GO

MarketingPool分配了10%CPU资源，DevelopmentPool分配了90%CPU资源。（当然你也可以加上内存的分配，不过个人感觉一般CPU比较重要，内存这东西不好说）

3. 可以先看一下当前Resource Governor的配置情况

SELECT * FROM sys.dm_resource_governor_resource_pools;
GO

你会发现，如上所示结果，只有两个资源池，并且不是我们创建的。

internal和default资源池是系统默认的资源池，假如没有手动设置Resource Governor的话，创建的数据库会被默认放到default资源池当中。（后面你还会看到对应的 WORKLOAD GROUP）

为什么我们创建的资源池没有看到呢？其实是我们的配置还没有设定上去，Sql Server08的设置修改后，需要手动 RECONFIGURE 来重置配置信息。

4. RECONFIGURE RESOURCE GOVERNOR 来让我们新建的资源池生效。

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO 
加下去执行
SELECT * FROM sys.dm_resource_governor_resource_pools;
GO

来看一下现在的配置信息。

你会发现新创建的MarketingPool和DevelopmentPool已经被添加了进去，并且max_cpu_percent为相应的10和90。

只有Resource Pool 是没用效果的，因为Pool下面还有下级单位，叫做 WORKLOAD GROUP , 所以接下去我们需要创建Group。

5. 创建 WORKLOAD GROUP

CREATE WORKLOAD GROUP MarketingGroup
USING MarketingPool;
GO

CREATE WORKLOAD GROUP DevelopmentGroup
USING DevelopmentPool;
GO

分别给两个Pool创建对应的 WorkLoad Group 。并且使用 Using 关键字绑定Group到对应的Pool。

6. 查看WorkLoad Group 的配置信息

SELECT * FROM sys.dm_resource_governor_workload_groups;
GO

会发现，和Pool中的情况相同，只有两个Group。（就是系统默认的Group，分别与默认的Pool对应）

同样是由于没有ReConfigure造成的。

7. 更新配置信息

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

接着在查看Group信息。

新的Group已经被添加进去了。

8. 创建两个数据库，用来做测试

IF DB_ID ('MarketingDB') IS NULL
CREATE DATABASE MarketingDB;
GO
IF DB_ID ('DevelopmentDB') IS NULL
CREATE DATABASE DevelopmentDB;
GO

9. 创建classifier function

IF OBJECT_ID ('dbo.MyClassifier') IS NOT NULL
DROP FUNCTION dbo.MyClassifier;
GO 
CREATE FUNCTION dbo.MyClassifier ()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName SYSNAME;
IF ORIGINAL_DB_NAME () = 'MarketingDB'
SET @GroupName = 'MarketingGroup';
ELSE IF  ORIGINAL_DB_NAME () = 'DevelopmentDB'
SET @GroupName = 'DevelopmentGroup';
ELSE SET @GroupName = 'Default';
RETURN @GroupName;
END;
GO

这里，Sqlserver2008对于 Resource Governor 的设计方式，很类似 *** 。Resource Governor 通过一个 *** 来绑定数据库对象到WorkLoad Group ，这个 *** 需要外部给出定义，并且手动绑定到Resource Governor 上面，定义方式可以参照上面代码。分别将两个测试用数据库绑定到各自的Group中，其他的数据库绑定到Default上面。

10. 绑定Classifier Function 到Resource Governor上面。

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

11. 更新Resource Governor 配置信息

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

12. 查看Resource Governor配置信息，确认设置

SELECT * FROM sys.dm_resource_governor_configuration;
GO

假如如下图所示，说明配置成功。

到这里，资源池的创建完成，然后使用Server2003的Performance来查看CPU的使用情况

1. 在管理工具中启动Performance

2. 点击Action菜单中的New windowForm Here

3. 点击加号添加监视

4. 选中对应SQL实例的Resource Governor ，并选定两个资源池

5. 点击Add添加2个资源池连接

目前由于没有操作，所以占用资源为0

接下去将使用两个循环脚本，不断更新数据库，来测试效果

1. 创建RunQueries.sql脚本，代码如下
IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1;
GO 
CREATE TABLE t1 (
c1 INT IDENTITY,
c2 VARCHAR (8000) DEFAULT (REPLICATE ('a', 8000)));
CREATE CLUSTERED INDEX t1c1 ON t1 (c1);
GO 
SET NOCOUNT ON;
GO
DECLARE @count INT = 0;
WHILE (@count < 1000)
BEGIN
INSERT INTO t1 DEFAULT VALUES;
SET @count +=  1;
END; 
WHILE (1 = 1) ALTER INDEX t1c1 ON t1 REORGANIZE; 
不断更新Index以消耗资源。

2. 创建RunQueries2.sql脚本，代码如下
IF OBJECT_ID ('t2') IS NOT NULL DROP TABLE t2;
GO 
CREATE TABLE t2 (
c1 INT IDENTITY,
c2 VARCHAR (8000) DEFAULT (REPLICATE ('a', 8000)));
CREATE CLUSTERED INDEX t2c1 ON t2 (c1);
GO 
SET NOCOUNT ON;
GO
DECLARE @count INT = 0;
WHILE (@count < 1000)
BEGIN
INSERT INTO t2 DEFAULT VALUES;
SET @count +=  1;
END; 
WHILE (1 = 1) ALTER INDEX t2c1 ON t2 REORGANIZE; 
与之一个脚本一样，因为测试发现，一个脚本消耗的资源不足以体现出9：1效果，所以同时运行2个脚本。

3. 使用sqlcmd来运行脚本（因为是死循环）

a) cd 脚本所在目录

b) sqlcmd -S .SQLDEV01 -d MarketingDB -iRunQueries.sql

通过sqlcmd运行脚本 –S 指定使用的SQL实例 -d 指定使用数据库 -i 指定运行脚本

分别使用4个sqlcmd运行4分脚本，直接给出sqlcmd命令（注意替换SQL实例名称）
sqlcmd -S .SQLDEV01 -d MarketingDB -iRunQueries.sql 
sqlcmd -S .SQLDEV01 -d DevelopmentDB -i RunQueries.sql 
sqlcmd -S .SQLDEV01 -d MarketingDB -i RunQueries2.sql 
sqlcmd -S .SQLDEV01 -d DevelopmentDB -i RunQueries2.sql 
4. 观察Performance中曲线的变化，如下所示则操作成功

可以看到，CPU使用趋于 1：9 Resource Governor 成功应用了

备注：

Resource Governor 能够自动判断资源分配比例，假如没有竞争资源池的话，那么会分配100%资源应用，假如竞争激烈，会按照比例分配。从而使得各个资源池中的对象都能够使用一定的服务器资源完成自己的工作，从而可以使得我们预测系统的最差情况，增强对数据库的管理性。