在SQL Server的资源调节器有三个重要的概念：

1. 资源池

在上文多租户的理念中，不同的客户端负载之所以能够分配彼此隔离的资源，其原因就是他们拥有各自独立的资源池。

在资源池里面，DBA可以设置最大、最小内存和CPU资源，也就是说，资源池实际上就是内存和CPU资源的集合，DBA根据业务的需要，可以为重要业务设置较大的资源池，而SLA较低的业务设置小的资源池。

2. 工作组的负载

工作组负载是一些具有共同特征的客户端请求的集合。SQL Server将客户端请求按照一定的规则分类后，形成多个工作组负载。

工作组负载是资源池利用的主体。将不同的资源池绑定到不同的工作组负载，从而实现了各个工作组负载的资源隔离。

3. 分类器

分类器提供一套划分工作组负载的规则，在此规则基础上，SQL Server将客户端的请求进行分类形成多个工作组。

举个例子：把SQL Server的用户名作为一个分类器，来自A用户的所有请求划分到工作组负载1，来自B用户的所有请求划分到工作组负载2。然后工作组负载1和2分别对应不同的资源池。

原理图如下：

 

用资源调节器来分配两个数据库的CPU资源

假设有市场和销售两个部门，各自使用不同的数据库，但两个数据库在同一个SQL Server实例上，为了确保两个部门的数据库彼此不受对方资源使用的影响，我们使用SQL Server的资源调控器来控制两个数据库的资源使用。

1. 创建测试数据库

create database Sales

create database Marketing

 

2. 配置CPU环境

为了更好的查看CPU资源的争用情况，我们先设置SQL Server的CPU affinity mask，使SQL Server只能使用一个逻辑核心，所有的工作组负载共享同一个CPU核心资源。

sp_configure 'show advanced', 1

GO

RECONFIGURE

GO

sp_configure 'affinity mask', 1

GO

RECONFIGURE

GO

 

3. 配置资源调控器

1) 创建资源池

CREATE RESOURCE POOL SalesPool

CREATE RESOURCE POOL MarketingPool

2) 创建工作组负载

--创建SalesGroup的工作组，并绑定到SalesPool

CREATE WORKLOAD GROUP SalesGroup

USING SalesPool

--创建MarketingGroup的工作组，并绑定到MarketingPool

CREATE WORKLOAD GROUP MarketingGroup

USING MarketingPool

GO

3) 创建分类器（分类函数）

--创建自定义的二分类函数，按照客户端连接字符串中的数据库分类并返回数据库的名字

CREATE FUNCTION CLASSIFIER_DbName()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

DECLARE @val varchar(32)

SET @val = 'default';

IF 'Sales' = ORIGINAL_DB_NAME()

SET @val = 'SalesGroup';

ELSE IF 'Marketing' = ORIGINAL_DB_NAME()

SET @val = 'MarketingGroup';

RETURN @val;

END

GO

--将分类函数绑定到资源调节器中

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = dbo.CLASSIFIER_DbName)

GO

4) 启用资源调控器

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

 

4. 测试sales数据库的负载

Ø 创建如下测试SQL语句，并保存到workload.sql文件中

SET NOCOUNT ON

DECLARE @i INT

DECLARE @s VARCHAR(100)

SET @i = 100000000

WHILE @i > 0

BEGIN

SELECT @s = @@version;

SET @i = @i - 1;

END

Ø 以管理员身份运行cmd

Ø 然后以sqlcmd的方式加载workload.sql语句

sqlcmd -S localhost\sql2014 -U sa -P 95938 -d Sales –I "d:\workload.sql"

这条语句的意思是在sales数据库中执行workload.sql，

Ø 打开性能计数器

SQLServer:Resource Pool Stats->CPU usage

可以看到salespool的CPU资源占用情况如下图：

下图中，salespool CPU资源的使用率为25%，（相当于完全使用了使用了笔者电脑4核CPU中1核资源（前面设置了SQL Server的affinity mask为1））。因为我们在前面的分类器的中把sales数据库绑定到了资源池salespool上了，sales数据库的CPU资源占用情况就是25%。

 

5. 再测试下sales库和marketing库的资源争用情况

Ø 新开一个cmd窗口，执行如下语句：

sqlcmd -S localhost\sql2014 -U sa -P 95938 -d Marketing -i "d:\workload.sql"

这条语句的意思是在Marketing数据库中执行workload.sql，

此时出现如下结果：

salespool和marketingPool共享25%的CPU资源（且两者占用的资源量相同（接近），这是因为他们都是采用默认的资源分配权重）。这里的marketingPool同上文中介绍的salespool一样，它也是绑定到了一个数据库，不同的是这里绑定了marketing数据库。

因此，这两个资源池的CPU资源争用反映的就是sales数据库和Marketing数据库的资源争用。

备注：红色的曲线代表sales pool

绿色曲线代表marketingPool

Ø 现在，我们更改销售系统的资源池的CPU资源的权重为70

ALTER RESOURCE POOL SalesPool

WITH (MIN_CPU_PERCENT = 70)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

结果如下：

此时salesPool占用的资源将增加到70%，而marketingPool的CPU资源降低到30%，两者按照7:3进行分配。

这样就相当于给sales数据库分配了更多的CPU资源，而削减了marketing的CPU资源。

 

6. 最后，我们来看一个设置最大CPU资源权重的例子

--设置MarketingPool的最大CPU占用

ALTER RESOURCE POOL MarketingPool

WITH (MAX_CPU_PERCENT = 5)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

此时，在marketingPool占用的资源如下图：

如果此时在sales数据库中执行的workload.sql已经执行完毕，则marketing数据库会突破之前设置的MAX_CPU_PERCENT = 5的限制，使用到全部CPU资源，这样的好处就是可以充分利用资源，避免资源浪费。如下图的蓝色框中的曲线。

当然，如果你一定要限制Marketing数据库的CPU资源必须在5%以内，可以通过如下语句实现：

ALTER RESOURCE POOL MarketingPool

WITH (CAP_CPU_PERCENT=5)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

总结

SQL Server资源调节器通过资源池、工作组负载、分类器等机制可以为不同的数据库、不同的请求、不同的用户分配彼此独立的CPU、内存、IO等资源，起到资源调节和隔离的作用。通过该技术，我们可以在确保每个数据库分配到合理的资源的前提下，合并多个数据库到同一个实例，也可以为某些SQL代码预定义服务器资源及避免某些SQL语句占用过多资源，还可以按照业务的优先级分配资源等。总之，使用SQL Server资源调节器可以更为细腻的分配服务器资源，是DBA的必杀技之一。