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
参考:资源调控器