首页 > 数据库 > SQL Server的资源调节器的原理
2015
01-04

SQL Server的资源调节器的原理

在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的必杀技之一。

编程技巧