本文主要向大家介绍了SQLServer数据库 2014新特性：IO资源调控，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。

    在数据库服务器上，有三种硬件资源一直是影响数据库性能好坏的关键，甚至会影响到整个生产系统的使用，这三种资源分别是内存、CPU和物理IO。当数据库服务器上挂载了多个数据库的时候，极有可能发生资源的争夺，如何能保证重要数据库在拥有足够资源的前提下再把多余的资源提供给其它数据库呢?资源调控器应运而生。

　　在SQL Server 2014中已经增加了对物理IO资源的控制，这个功能在私有云的数据库服务器上的作用体现得尤为重要，它能够为私有云用户提供有效的控制、分配，并隔离物理IO资源。

　　一、生活中资源调控器

　　在讲述SQL Server 2014的资源调控器(Resource Governor)之前，让我们来找一找生活中资源调控器的影子。大家都知道一线城市的交通可以用拥堵一词来形容

　　为了缓解拥堵的交通，北京、上海、广州等城市都修建了一个称之为BRT的交通设施，BRT的全称为Bus Rapid Transit，意为快速公交，就是在拥堵的公路上分配一条专门提供给公交车使用的车道，尽量保证大部分人出行的通畅。

　　交通部门规定：在出行高峰期，BRT专用道只供公交车使用，其它机动车只能使用BRT专用道之外的车道资源。人有专门的人行道，自行车有自行车道。根据这个逻辑，可以得出如图3所示的人与车在使用道路资源的逻辑结构图

　　二、SQL Server中资源调控器

　　了解了生活中的资源调控器之后，我们马上进入今天的主题：SQL Server资源调控器，它与生活中的资源调控器有着惊人的相似之处。

　　SQL Server 2014的资源调控器默认包含了两个工作负荷组(internal、default)和两个资源池(internal、default)，在没有手动设置资源调控器的情况下，创建的数据库会默认放到default资源池当中。

　　SQL Server资源调控器接收到会话请求后，通过用户定义的分类器函数把会话进行划分并路由到相应的工作负荷组，再通过工作负荷组找到对应的资源池，由资源池中设置的内存、CPU和物理IO资源的阀值来决定会话请求的资源分配。根据描述，可以得出如图4所示的SQL Server资源调控器逻辑结构图：

三、SQL Server资源调控器运用场景—CPU

　　理解了SQL Server资源调控器的原理之后，接着讲述资源调控器在CPU方面的运用场景：假设数据库服务器上有一个重要的数据库ImportantDB和一个普通的数据库GeneralDB，从业务出发，希望服务器上的资源在满足了ImportantDB之后才考虑分配多余的资源给GeneralDB，保证重要业务系统的正常运行。

　　需要说明的是：笔者的机器是Azure上的虚拟机，机器的配置如图5所示，下面的测试都是基于这个环境的，所以读者在自行测试的时候应该根据自己的机器环境进行调整。

　　假设GeneralDB 数据库占用的CPU最大值为10%，ImportantDB数据库占用的CPU最大值为90%，实现这个需求的步骤如下：

　　1.创建测试数据库

　　--创建重要业务数据库

　　CREATE DATABASE ImportantDB

　　GO

　　--创建普通业务数据库

　　CREATE DATABASE GeneralDB

　　GO

　　2.创建并配置新的资源池和工作负荷组

　　--创建重要业务数据库的资源池

　　CREATE RESOURCE POOL rpImportantDB

　　WITH

　　(

　　MAX_CPU_PERCENT = 90,

　　MIN_CPU_PERCENT = 10

　　)

　　GO

　　--创建重要业务数据库的工作负荷组

　　CREATE WORKLOAD GROUP wgImportantDB

　　WITH

　　(

　　IMPORTANCE = MEDIUM

　　)

　　USING rpImportantDB

　　GO

　　--创建普通业务数据库的资源池

　　CREATE RESOURCE POOL rpGeneralDB

　　WITH

　　(

　　MAX_CPU_PERCENT = 10,

　　MIN_CPU_PERCENT = 0

　　)

　　GO

　　--创建重要业务数据库的工作负荷组

　　CREATE WORKLOAD GROUP wgGeneralDB

　　WITH

　　(

　　IMPORTANCE = LOW

　　)

　　USING rpGeneralDB

　　GO

　　3.更新内存中资源调控器的配置

　　--更新内存中的配置

　　ALTER RESOURCE GOVERNOR RECONFIGURE

　　GO

　　4.查询资源调控器中资源池和工作负荷组的配置信息，返回结果如图6所示：

　　--查询获取资源池和工作负荷组配置

　　USE master

　　SELECT * FROM sys.resource_governor_resource_pools

　　SELECT * FROM sys.resource_governor_workload_groups

　　GO

　　5.创建分类器函数，这是一个用户自定义函数 (UDF)，它供资源调控器用来对会话进行分类，以便将它们路由到对应的工作负荷组中，函数返回工作负荷组的名称;

　　--创建分类器函数

　　CREATE FUNCTION fn_Classifier()

　　RETURNS SYSNAME

　　WITH SCHEMABINDING

　　AS

　　BEGIN

　　DECLARE @strGroupName SYSNAME

　　IF ORIGINAL_DB_NAME()='ImportantDB'

　　SET @strGroupName='wgImportantDB'

　　ELSE IF ORIGINAL_DB_NAME()='GeneralDB'

　　SET @strGroupName='wgGeneralDB'

　　ELSE

　　SET @strGroupName='default'

　　RETURN @strGroupName

　　END

　　GO

　　6.将分类器函数fn_Classifier注册到资源调控器并更新内存中的配置

　　--注册分类器函数到资源调控器并更新内存中的配置

　　ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_Classifier)

　　ALTER RESOURCE GOVERNOR RECONFIGURE

　　GO

　　7.接下来就是对CPU进行测试，创建能占用CPU的测试脚本，把下面的脚本分别保存为GeneralDB.sql和ImportantDB.sql：

　　--测试CPU

　　DECLARE @Counts INT

　　WHILE 1=1

　　BEGIN

　　SELECT @Counts=COUNT(*) FROM SYS.COLUMNS A,SYS.COLUMNS B

　　END

　　8.Windows操作系统为SQL Server资源调控器提供了大量的性能计数器帮助了解资源的使用情况，查看SQLServer:Resource Pool Stats对象下的CPU usage%计数器，里面包括四个对象实例：default、internal和刚刚创建的rpGeneralDB、rpImportantDB

　　9.因为分类器函数fn_Classifier 通过函数ORIGINAL_DB_NAME()返回用户在数据库连接字符串中指定的数据库名称，所以可以通过使用SQLCMD来模拟用户输入，SQLCMD调用GeneralDB.sql和ImportantDB.sql脚本的命令如下：

　　SQLCMD -S . -d GeneralDB -i GeneralDB.sql

　　SQLCMD -S . -d ImportantDB -i ImportantDB.sql

　　10.性能计数器CPU usage%记录了在测试过程中CPU的使用情况

　　执行GeneralDB.sql后，蓝色实例rpGeneralDB占用了25%左右的CPU;接着在另外的窗口执行ImportantDB.sql后，紫色实例rpImportantDB同样占用了25%左右的CPU，以同样的方式继续增加紫色实例rpImportantDB的CPU占用量，当占用量超过了设置的90%，可以发现蓝色实例rpGeneralDB占用CPU百分比马上下降了。从这个测试的结果来看，SQL Server资源调控器已经达到我们预期对CPU资源的规划效果。

　　值得一提的是，大家可能已经发现rpImportantDB资源池的CPU usage%计数器的最新值高达97.470%，资源池不是设置了MAX_CPU_PERCENT = 90吗?怎么会超过这个限制呢?其实这是因为rpGeneralDB资源池的MIN_CPU_PERCENT = 0，所以在没有其它最小CPU的限制的话，rpImportantDB资源池占用的CPU是有可能达到100%的。

四、SQL Server资源调控器运用场景—IO

　　前面讲述关于CPU资源的控制更多的是为下面讲述SQL Server 2014在IO资源上的调控做铺垫，上面的例子是以数据库为单位规划各个库占用CPU的百分比，下面的例子我们就将以登陆用户来划分磁盘的IOPS;

　　假设generalUser用户占用的IOPS最大值为10，importantUser用户占用IOPS的最大值为20，实现这个需求的步骤如下：

　　1.首先，解除分类器函数与资源调控器注册关系;

　　USE [master]

　　GO

　　--解除分类器函数注册

　　ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)

　　GO

　　2.接着，修改rpImportantDB、rpGeneralDB资源池，添加MAX_IOPS_PER_VOLUME和MIN_IOPS_PER_VOLUME两个属性值;

　　--修改重要业务数据库的资源池

　　ALTER RESOURCE POOL rpImportantDB

　　WITH

　　(

　　MAX_CPU_PERCENT = 90,

　　MIN_CPU_PERCENT = 10,

　　MAX_IOPS_PER_VOLUME = 20,

　　MIN_IOPS_PER_VOLUME = 0

　　)

　　--修改普通业务数据库的资源池

　　ALTER RESOURCE POOL rpGeneralDB

　　WITH

　　(

　　MAX_CPU_PERCENT = 10,

　　MIN_CPU_PERCENT = 0,

　　MAX_IOPS_PER_VOLUME = 10,

　　MIN_IOPS_PER_VOLUME = 0

　　)

　　GO

　　3.接着修改分类器函数fn_Classifier()，把它修改成按照登陆用户：importantUser和generalUser(这两个登陆用户请自行创建，为了方便测试，这两个用户都是管理员身份)返回对应的工作负荷组名称;

　　--修改分类器函数

　　ALTER FUNCTION fn_Classifier()

　　RETURNS SYSNAME

　　WITH SCHEMABINDING

　　AS

　　BEGIN

　　DECLARE @strGroupName SYSNAME

　　IF SUSER_SNAME()='importantUser'

　　SET @strGroupName='wgImportantDB'

　　ELSE IF SUSER_SNAME()='generalUser'

　　SET @strGroupName='wgGeneralDB'

　　ELSE

　　SET @strGroupName='default'

　　RETURN @strGroupName

　　END

　　GO

　　4.重新把分类器函数fn_Classifier()注册到资源调控器并更新内存中的配置;

　　--注册分类器函数到资源调控器并更新内存中的配置

　　ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnClassifier)

　　ALTER RESOURCE GOVERNOR RECONFIGURE

　　GO

　　5.在GeneralDB数据库中创建一个名为TestIOPS的表，并向表中插入10000行记录;

　　USE [GeneralDB]

　　GO

　　--创建表

　　CREATE TABLE [dbo].[TestIOPS](

　　[Id] [int] IDENTITY(1,1) NOT NULL,

　　[MyStr] [nchar](450) NULL

　　) ON [PRIMARY]

　　GO

　　--插入测试数据

　　SET NOCOUNT ON;

　　GO

　　DECLARE @count INT = 0;

　　WHILE (@count < 10000)

　　BEGIN

　　INSERT INTO [GeneralDB].[dbo].[TestIOPS](MyStr)

　　VALUES(REPLICATE('a',450));

　　SET @count += 1;

　　END

　　GO

　　6.使用generalUser用户登陆SSMS，通过在TestIOPS表的MyStr字段创建一个非聚集索引来测试IOPS，在执行创建索引的脚本之前，打开性能计数器帮助监控创建索引时的IOPS，找到SQLServer:Resource Pool Stats对象下的Disk Write IO/sec计数器

　　USE [GeneralDB]

　　GO

　　--创建索引

　　CREATE NONCLUSTERED INDEX idx_MyStr_1 ON [GeneralDB].[dbo].[TestIOPS] ([MyStr]);

　　GO

　　从图9可以看出资源池rpGeneralDB 中Disk Write IO/sec计数器的最大值为10，发生Disk Write IO/sec的时间从22:54:52持续到22:55:02，持续时间大概为20秒;

　　7.接着，使用importantUser用户登陆SSMS，创建上一步骤中一样的索引结构，只需要修改索引名称即可，同样需要监控SQLServer:Resource Pool Stats对象下的Disk Write IO/sec计数器

　　USE [GeneralDB]

　　GO

　　--创建索引

　　CREATE NONCLUSTERED INDEX idx_MyStr_2 ON [GeneralDB].[dbo].[TestIOPS] ([MyStr]);

　　GO

　　从图10可以看出资源池rpImportantDB 中Disk Write IO/sec计数器的最大值为20，发生Disk Write IO/sec的时间从22:59:52持续到23:00:11，持续时间大概为9秒;

　　通过上面资源调控器的IOPS的测试，可以发现即使两个用户执行了相同操作，但IOPS却可以根据不同用户而有所区别，这完全归功于资源调控器对IO资源的控制，这将为数据库主机或者私有云上运行IO密集型工作负载提供解决方案。

　　五、总结

　　资源调控器(Resource Governor)在没有资源争夺的时候，那么运用可以分配到100%资源，如果达到了分类器函数的临界值，会按照预先分配的比例进行调配，从而保证重要业务在资源紧缺的情况下顺利进行，增强对数据库的管理性。SQL Server 2014的新功能中提供了一个非常重要的IO资源控制，这将为私有云用户提供更人性化的管理和服务。

    以上就介绍了SQL Server的相关知识，希望对SQL Server有兴趣的朋友有所帮助。了解更多内容，请关注职坐标数据库SQL Server频道！