外观
SQLServer 资源调控器配置
资源调控器概述
SQL Server 资源调控器是一种工作负载管理工具，用于管理 SQL Server 实例的资源使用。它允许 DBA 根据工作负载类型分配 CPU、内存和 I/O 资源，确保关键业务工作负载获得足够的资源，同时防止非关键工作负载消耗过多资源。
资源调控器架构
核心组件
资源池 (Resource Pool)：
- 定义资源限制，如 CPU 百分比、内存限制等
- 包括默认池、内部池和用户定义池
- 控制 CPU、内存和 I/O 资源
工作负载组 (Workload Group)：
- 包含一组具有相似资源需求的会话
- 映射到资源池
- 定义会话级别的资源限制
分类器函数 (Classifier Function)：
- T-SQL 函数，用于将会话分配到工作负载组
- 基于会话属性，如登录名、应用程序名称、主机名等
- 在会话建立时执行
资源管理器 (Resource Manager)：
- 协调资源池和工作负载组
- 监控资源使用情况
- 强制执行资源限制
资源调控器工作原理
- 会话建立：用户连接到 SQL Server
- 分类：分类器函数执行，将会话分配到工作负载组
- 资源分配：会话从工作负载组关联的资源池获取资源
- 资源监控：资源管理器监控资源使用情况
- 资源限制：强制执行资源池和工作负载组的资源限制
资源调控器配置
步骤 1：启用资源调控器
sql
-- 启用资源调控器
ALTER RESOURCE GOVERNOR RECONFIGURE;
-- 检查资源调控器状态
SELECT * FROM sys.dm_resource_governor_configuration;步骤 2：创建资源池
sql
-- 创建 CPU 和内存资源池
CREATE RESOURCE POOL [CriticalWorkloadPool]
WITH (
MIN_CPU_PERCENT = 60, -- 最小 CPU 百分比
MAX_CPU_PERCENT = 100, -- 最大 CPU 百分比
CAP_CPU_PERCENT = 100, -- CPU 上限
AFFINITY SCHEDULER = AUTO, -- CPU 亲和性
MIN_MEMORY_PERCENT = 60, -- 最小内存百分比
MAX_MEMORY_PERCENT = 100, -- 最大内存百分比
CAP_MEMORY_PERCENT = 100 -- 内存上限
);
-- 创建 I/O 资源池（SQL Server 2014 及以上版本）
CREATE RESOURCE POOL [ReportingPool]
WITH (
MIN_CPU_PERCENT = 20,
MAX_CPU_PERCENT = 50,
MIN_MEMORY_PERCENT = 20,
MAX_MEMORY_PERCENT = 50,
IOPS_PER_VOLUME = 100, -- 每卷 IOPS 限制
MIN_IOPS_PER_VOLUME = 20, -- 每卷最小 IOPS
CAP_IOPS_PER_VOLUME = 200 -- 每卷 IOPS 上限
);步骤 3：创建工作负载组
sql
-- 创建工作负载组
CREATE WORKLOAD GROUP [CriticalWorkloadGroup]
USING [CriticalWorkloadPool]
WITH (
IMPORTANCE = HIGH, -- 重要性级别：LOW, MEDIUM, HIGH
REQUEST_MAX_MEMORY_GRANT_PERCENT = 25, -- 单个请求最大内存授予百分比
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60, -- 内存授予超时（秒）
MAX_DOP = 8, -- 最大并行度
GROUP_MAX_REQUESTS = 0, -- 工作负载组最大请求数（0 表示无限制）
THROTTLING_PRIORITY = 1 -- 限流优先级（1-64，1 最高）
);
-- 创建报告工作负载组
CREATE WORKLOAD GROUP [ReportingGroup]
USING [ReportingPool]
WITH (
IMPORTANCE = MEDIUM,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 50,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 120,
MAX_DOP = 4,
GROUP_MAX_REQUESTS = 0
);步骤 4：创建分类器函数
sql
-- 创建分类器函数
CREATE FUNCTION dbo.ResourceGovernorClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup sysname;
-- 根据应用程序名称分类
IF APP_NAME() LIKE '%SQL Server Management Studio%' OR APP_NAME() LIKE '%Microsoft SQL Server Management Studio%'
SET @WorkloadGroup = 'AdministratorGroup';
-- 根据登录名分类
ELSE IF SUSER_SNAME() = 'sa' OR SUSER_SNAME() LIKE '%Admin%'
SET @WorkloadGroup = 'AdministratorGroup';
-- 根据主机名分类
ELSE IF HOST_NAME() = 'ReportServer'
SET @WorkloadGroup = 'ReportingGroup';
-- 根据应用程序名称分类
ELSE IF APP_NAME() LIKE '%CriticalApp%'
SET @WorkloadGroup = 'CriticalWorkloadGroup';
-- 默认分类
ELSE
SET @WorkloadGroup = 'default';
RETURN @WorkloadGroup;
END;
GO
-- 注册分类器函数
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier);步骤 5：应用配置
sql
-- 应用资源调控器配置
ALTER RESOURCE GOVERNOR RECONFIGURE;
-- 验证配置
SELECT * FROM sys.resource_governor_resource_pools;
SELECT * FROM sys.resource_governor_workload_groups;
SELECT * FROM sys.dm_resource_governor_configuration;资源调控器监控
监控资源池
sql
-- 查看资源池统计信息
SELECT
rp.name AS resource_pool_name,
rp.min_cpu_percent,
rp.max_cpu_percent,
rp.min_memory_percent,
rp.max_memory_percent,
rps.avg_cpu_percent,
rps.avg_memory_percent,
rps.max_memory_used_mb,
rps.active_session_count
FROM sys.dm_resource_governor_resource_pools rp
JOIN sys.dm_resource_governor_resource_pool_stats rps ON rp.pool_id = rps.pool_id;监控工作负载组
sql
-- 查看工作负载组统计信息
SELECT
wg.name AS workload_group_name,
rp.name AS resource_pool_name,
wgs.request_count,
wgs.active_request_count,
wgs.total_cpu_usage_ms,
wgs.total_memory_usage_mb,
wgs.queued_request_count,
wgs.max_request_cpu_time_ms
FROM sys.dm_resource_governor_workload_groups wg
JOIN sys.dm_resource_governor_workload_group_stats wgs ON wg.group_id = wgs.group_id
JOIN sys.dm_resource_governor_resource_pools rp ON wg.pool_id = rp.pool_id;监控会话分类
sql
-- 查看会话分类情况
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
wg.name AS workload_group_name,
rp.name AS resource_pool_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups wg ON s.group_id = wg.group_id
JOIN sys.dm_resource_governor_resource_pools rp ON wg.pool_id = rp.pool_id
WHERE s.session_id > 50; -- 排除系统会话资源调控器优化
1. 资源池优化
CPU 配置：
- 为关键工作负载分配足够的
MIN_CPU_PERCENT - 使用
MAX_CPU_PERCENT限制非关键工作负载 - 考虑使用
CAP_CPU_PERCENT限制突发使用
- 为关键工作负载分配足够的
内存配置：
- 为关键工作负载分配足够的
MIN_MEMORY_PERCENT - 使用
MAX_MEMORY_PERCENT限制非关键工作负载 - 避免内存过度分配
- 为关键工作负载分配足够的
I/O 配置：
- 为 I/O 密集型工作负载设置适当的 IOPS 限制
- 使用
MIN_IOPS_PER_VOLUME确保关键工作负载获得足够的 I/O 资源 - 考虑使用
CAP_IOPS_PER_VOLUME限制非关键工作负载
2. 工作负载组优化
重要性设置：
- 为关键工作负载设置
IMPORTANCE = HIGH - 为非关键工作负载设置
IMPORTANCE = LOW或MEDIUM
- 为关键工作负载设置
内存授予设置：
- 为大型查询设置适当的
REQUEST_MAX_MEMORY_GRANT_PERCENT - 调整
REQUEST_MEMORY_GRANT_TIMEOUT_SEC避免查询超时
- 为大型查询设置适当的
并行度设置：
- 为 CPU 密集型工作负载设置适当的
MAX_DOP - 考虑使用
GROUP_MAX_REQUESTS限制并发请求数
- 为 CPU 密集型工作负载设置适当的
3. 分类器函数优化
简化分类逻辑：
- 避免复杂的分类逻辑，减少分类开销
- 优先使用索引列进行分类
- 考虑使用
APP_NAME()、SUSER_SNAME()、HOST_NAME()等内置函数
测试分类准确性：
- 验证会话是否被正确分类
- 监控分类器函数的执行开销
- 定期审查分类规则
资源调控器最佳实践
1. 从简单开始
- 从基本配置开始，逐渐增加复杂性
- 首先关注 CPU 和内存资源
- 只对关键工作负载进行分类
2. 监控和调整
- 定期监控资源使用情况
- 根据实际负载调整资源池和工作负载组配置
- 记录配置变更和性能影响
3. 测试配置
- 在测试环境中测试资源调控器配置
- 模拟真实工作负载
- 评估配置对性能的影响
4. 考虑工作负载特性
- 根据工作负载类型设计资源调控器配置
- 区分 OLTP 和数据仓库工作负载
- 考虑批处理和交互式工作负载的差异
5. 结合其他优化技术
- 结合索引优化和查询优化
- 考虑使用读写分离
- 结合 Always On 可用性组
6. 定期审查配置
- 定期审查资源池和工作负载组配置
- 审查分类器函数
- 移除不再需要的配置
常见资源调控器场景
场景 1：OLTP 和数据仓库混合环境
配置建议：
- 创建两个资源池：OLTP 池和数据仓库池
- OLTP 池：
MIN_CPU_PERCENT = 70,MAX_CPU_PERCENT = 100 - 数据仓库池：
MIN_CPU_PERCENT = 30,MAX_CPU_PERCENT = 50 - 分类器函数根据应用程序名称或数据库名称分类
场景 2：关键业务应用和报告系统
配置建议：
- 创建关键业务应用资源池和报告系统资源池
- 关键业务应用池：
MIN_CPU_PERCENT = 60,MAX_CPU_PERCENT = 100 - 报告系统池：
MIN_CPU_PERCENT = 20,MAX_CPU_PERCENT = 50 - 分类器函数根据应用程序名称或主机名分类
场景 3：多租户环境
配置建议：
- 为每个租户创建资源池和工作负载组
- 根据租户级别分配资源
- 使用分类器函数根据数据库名称或登录名分类
资源调控器故障排查
1. 分类器函数问题
症状：会话未被正确分类或分类开销过大
排查步骤：
- 检查分类器函数逻辑
- 测试分类器函数：sql
SELECT dbo.ResourceGovernorClassifier() AS ClassifiedGroup; - 监控分类器函数执行开销
- 简化分类器函数逻辑
2. 资源池配置问题
症状：工作负载未获得足够资源或资源限制过严
排查步骤：
- 检查资源池配置
- 监控资源池统计信息
- 调整
MIN_CPU_PERCENT和MAX_CPU_PERCENT设置 - 调整内存和 I/O 限制
3. 工作负载组配置问题
症状：查询超时或性能下降
排查步骤：
- 检查工作负载组配置
- 调整
REQUEST_MAX_MEMORY_GRANT_PERCENT设置 - 调整
REQUEST_MEMORY_GRANT_TIMEOUT_SEC设置 - 调整
MAX_DOP设置
常见问题 (FAQ)
Q1: 资源调控器支持哪些资源类型？
A: SQL Server 资源调控器支持以下资源类型：
- CPU 资源（所有版本）
- 内存资源（所有版本）
- I/O 资源（SQL Server 2014 及以上版本）
Q2: 资源调控器对性能有影响吗？
A: 资源调控器会带来一定的性能开销，主要包括：
- 分类器函数执行开销
- 资源监控开销
- 资源限制强制执行开销
但在大多数情况下，这些开销可以忽略不计，而资源调控器带来的好处远大于开销。
Q3: 如何禁用资源调控器？
A: 可以使用以下命令禁用资源调控器：
sql
-- 禁用资源调控器
ALTER RESOURCE GOVERNOR DISABLE;
-- 检查资源调控器状态
SELECT * FROM sys.dm_resource_governor_configuration;Q4: 资源调控器可以与 Always On 可用性组一起使用吗？
A: 是的，资源调控器可以与 Always On 可用性组一起使用。需要在每个副本上单独配置资源调控器，或使用中央管理服务器统一配置。
Q5: 如何备份资源调控器配置？
A: 可以使用以下方法备份资源调控器配置：
sql
-- 备份资源调控器配置
SELECT * FROM sys.resource_governor_resource_pools;
SELECT * FROM sys.resource_governor_workload_groups;
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ResourceGovernorClassifier')) AS ClassifierFunction;Q6: 资源调控器支持 Azure SQL Database 吗？
A: Azure SQL Database 提供了类似的功能，称为资源治理，但与 SQL Server 资源调控器的实现有所不同。Azure SQL Database 使用服务级别目标 (SLO) 和资源限制来管理资源。
总结
SQL Server 资源调控器是一种强大的工作负载管理工具，可以帮助 DBA 管理 SQL Server 实例的资源使用。通过合理配置资源池、工作负载组和分类器函数，可以确保关键业务工作负载获得足够的资源，同时防止非关键工作负载消耗过多资源。
建议 DBA 在实施资源调控器时，遵循以下最佳实践：
- 从简单配置开始，逐渐增加复杂性
- 定期监控资源使用情况，根据实际负载调整配置
- 在测试环境中测试配置
- 考虑工作负载特性设计配置
- 结合其他优化技术
- 定期审查配置
通过实施资源调控器，可以提高 SQL Server 实例的资源利用率，确保关键业务工作负载的性能，同时为非关键工作负载提供适当的资源，实现更好的工作负载管理和服务质量。