SQLServer 资源调控器

SQL Server 资源调控器是一项可用于管理 SQL Server 工作负荷和系统资源使用情况的功能。您可使用资源调控器指定传入应用程序请求可以使用的 CPU、物理 IO 和内存的数量限制。

创建新的资源池步骤如下：

1.?创建资源池

2.创建工作负荷组

3.创建分类器函数

4.分类器函数注册到资源调控器

资源概念：

资源池：

资源池表示服务器的物理资源。您可以将池看作 SQL Server 实例内部的一个虚拟 SQL Server 实例。当安装 SQL Server 时，会创建两个资源池（内部资源池和默认资源池）。

工作负荷组：

工作负荷组充当具有相似分类标准的会话请求的容器。工作负荷允许对会话进行聚合监视，并定义会话的策略。每个工作负荷组都处于一个资源池中。当安装 SQL Server 时，会创建两个工作负荷组（内部工作负荷组和默认工作负荷组）并将其映射到对应的资源池。资源调控器还支持用户定义的工作负荷组。

分类：

分类过程会根据传入会话的特征将其分配给工作负荷组。您可以通过编写用户定义函数（称为分类器函数）来定制分类逻辑。资源调控器还支持用于实现分类规则的分类器用户定义的函数。

【资源调控器】

--【资源调控器】
--  https://msdn.microsoft.com/zh-cn/library/bb933866.aspx


USE master
GO

--  启用资源调控器，或者重新刷新配置
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO


/*【资源池】*/

--  资源调控器预定义两个资源池：内部池和默认池。
--  https://msdn.microsoft.com/zh-cn/library/hh510189.aspx


--  创建资源池
CREATE RESOURCE POOL [poolAdhoc] 
WITH(
	min_cpu_percent=0,--较低优先级的查询无法争用CPU，此处可设置
	max_cpu_percent=50,--如果有可用 CPU 容量，可用至 100% 
	min_memory_percent=0,--授予查询执行的内存，不可用于任何其他池
	max_memory_percent=100
)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

--  更改资源池设置
ALTER RESOURCE POOL [poolAdhoc] 
WITH(
	min_cpu_percent=0,max_cpu_percent=40,min_memory_percent=0,max_memory_percent=100
)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

--(新参数:适用范围：SQL Server 2012 及后期版本)
ALTER RESOURCE POOL [poolAdhoc] 
WITH(
	cap_cpu_percent=60			--限制最高使用 CPU 的60%（resource pool statsCPU usage targetpoolAdhoc）,affinity scheduler = auto 	--指定资源池在某CPU运行，避免争用
--,min_iops_per_volume=20		--IOPS，设置IO饱和时避免争用（2014才可以次功能）
--,max_iops_per_volume=100
)
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

--  删除资源池(先删除其工作组）。此时测试先不删除！
DROP RESOURCE POOL poolAdhoc;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO


/*【工作负荷组】*/
--  https://msdn.microsoft.com/zh-cn/library/hh510228.aspx


--  创建工作负荷组
CREATE WORKLOAD GROUP [groupAdhoc] 
WITH(
	group_max_requests=0,importance=Medium,--相对重要性{ LOW | MEDIUM | HIGH }
	request_max_cpu_time_sec=0,--请求可以使用的最长 CPU 时间，以秒为单位
	request_max_memory_grant_percent=25,--单个请求可以从池中获取的最大内存量（对资源池的MAX_MEMORY_PERCENT 而言）
	request_memory_grant_timeout_sec=0,--查询等待内存授予（工作缓冲区内存）变为可用的最长时间（以秒为单位）。
	max_dop=0						--并行请求的最大并行度 (DOP)，（0 到 255）
) USING [poolAdhoc]--指定工作组
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

--  更改工作负荷组设置
ALTER WORKLOAD GROUP [groupAdhoc] 
WITH(
	group_max_requests=0,request_max_cpu_time_sec=0,request_max_memory_grant_percent=25,request_memory_grant_timeout_sec=0,max_dop=0
) USING [poolAdhoc]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

--  删除工作负荷组，此时测试先不删除！
DROP WORKLOAD GROUP groupAdhoc;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO


/*【分类器函数】*/
--  https://msdn.microsoft.com/zh-cn/library/hh510208.aspx

--  创建分类器函数(如果是 sysadmin 角色，则分配工作组groupAdhoc ，否则使用默认工作组 default)
--  DROP FUNCTION dbo.fn_Classifier
USE master
GO
CREATE FUNCTION dbo.fn_Classifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
	IF (IS_SRVROLEMEMBER('sysadmin',SUSER_SNAME()) = 0)
		RETURN N'groupAdhoc'
	RETURN N'default'
END
GO
/*
若函数已经注册到资源调控器，更改函数定义则错误！
Msg 10920,Level 16,State 2,Procedure fn_Classifier,Line 1
Cannot alter user-defined function 'fn_Classifier'. It is being used as a resource governor classifier.

解决：先把资源调控器的分类函数设置为NULL。
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
*/

--	分类器函数注册到资源调控器
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_Classifier)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

【测试】

--	1. 打开性能监视器,监控所有资源池的 CPU 使用百分比情况
Processor Information(_Total)% Processor Time
SQLServer:Resource Pool Stats(poolAdhoc)CPU usage %
SQLServer:Resource Pool Stats(default)CPU usage %
SQLServer:Resource Pool Stats(internal)CPU usage %


--	2. 现在可以创建登录名 [kk],不授予服务器角色 sysadmin
--	EXEC sp_addsrvrolemember @loginame= 'kk',@rolename = 'sysadmin'   
EXEC sp_dropsrvrolemember @loginame = 'kk',@rolename = 'sysadmin'  


--	3. 用该账号[kk]登录数据库,执行循环语句消耗 CPU
SELECT SUSER_SNAME(),IS_SRVROLEMEMBER('sysadmin',SUSER_SNAME())

SET NOCOUNT ON
DECLARE @I INT
WHILE 1=1
BEGIN
	SELECT @I=COUNT(*) FROM sys.objects
END
SET NOCOUNT OFF

--	4. 再用当前的系统管理员账号依次打开 2 个查询窗口，执行以下循环语句消耗CPU
SELECT SUSER_SNAME(),SUSER_SNAME()),dbo.fn_Classifier()

SET NOCOUNT ON
DECLARE @I INT
WHILE 1=1
BEGIN
	SELECT @I=COUNT(*) FROM sys.objects
END
SET NOCOUNT OFF

可以看到，当?sysadmin 角色的用户执行时，默认池 default 的cpu 使用率升高，资源池?poolAdhoc 的则下降，但总体 CPU 是不变的，CPU 处理时间仍然是 100% 。

【资源调控器设置】

/*【资源调控器设置】*/
--	https://msdn.microsoft.com/zh-cn/library/bb934013(v=sql.120).aspx


--	启用资源调控器，或者重新刷新配置
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

--	禁用资源调控器
ALTER RESOURCE GOVERNOR DISABLE;
GO

--	重置有关所有工作负荷组和资源池的统计信息
ALTER RESOURCE GOVERNOR RESET STATISTICS;
GO

--	分类器函数注册到资源调控器
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_Classifier);
GO

--	设置每个磁盘卷的最大排队 I/O 操作数 (最大100 ，不是百分比；SQL Server 2014 及后期版本使用)
ALTER RESOURCE GOVERNOR WITH (MAX_OUTSTANDING_IO_PER_VOLUME = 100 );
GO

/*【资源调控器目录视图】*/
--	https://msdn.microsoft.com/zh-cn/library/bb934093(v=sql.120).aspx

SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_workload_groups
SELECT * FROM sys.dm_resource_governor_configuration

SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups
SELECT * FROM sys.resource_governor_configuration

参考：资源调控器

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!