关闭

SQLServer 资源调控器

1183人阅读 评论(0) 收藏 举报
分类:

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 stats\CPU usage target\poolAdhoc)
	,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, 
	importance=Medium, 
	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(),IS_SRVROLEMEMBER('sysadmin',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 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name],
      is_reconfiguration_pending --1表示会话尚未更新
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


参考:资源调控器



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1219168次
    • 积分:15955
    • 等级:
    • 排名:第754名
    • 原创:458篇
    • 转载:48篇
    • 译文:3篇
    • 评论:151条