首页 > 代码库 > 浅谈数据库资源使用的按需分配方法
浅谈数据库资源使用的按需分配方法
SQL Server资源调节器的作用
如果有5个业务都很繁忙的数据库需要部署在一台服务器上，为了避免相互的资源抢占，我们通常会在服务器上安装5个SQL Server实例来分别承载这5个数据库，然后分别设置每个实例的最大和最小内存、CPU掩码等，用以s控制这5个业务数据库的资源分配。
笔者的公司曾经就有这样的案例。这种方式的缺点显而易见：首先是授权，在上述例子中，5个SQL Server实例的费用应该是比1个要高；其次是管理成本提高，DBA不得不安装和维护5个实例。那么，有没有更好的方法呢？
其实从SQL Server2008（企业版）开始，微软就已经考虑过这种情况，并提出解决方案——“SQL Server的资源调节器”。SQL Server的资源调节器引入了一种多租户的理念，它将一个SQL Server实例的资源“出租”给不同的客户端负载，每个客户端负载所分配到的资源彼此隔离，互不影响。
下面我们看看SQL Server的资源调节器的原理和一个实例的来了解下SQL Server的资源调节器：
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的必杀技之一。
浅谈数据库资源使用的按需分配方法