三、SQL Server资源调控器运用场景—CPU
理解了SQL Server资源调控器的原理之后，接着讲述资源调控器在CPU方面的运用场景：假设数据库服务器上有一个重要的数据库ImportantDB和一个普通的数据库GeneralDB，从业务出发，希望服务器上的资源在满足了ImportantDB之后才考虑分配多余的资源给GeneralDB，保证重要业务系统的正常运行。
需要说明的是：笔者的机器是Azure上的虚拟机，机器的配置如图5所示，下面的测试都是基于这个环境的，所以读者在自行测试的时候应该根据自己的机器环境进行调整。
▲图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
▲图6
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，如图7所示：
▲图7：添加计数器
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的使用情况，结果如图8所示：
▲图8：性能计数器
执行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%的。
