43、SQL Server查询性能管理与专业DBA成长秘诀

于 2025-08-18 13:39:13 发布
阅读量48 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： SQL Server 2012管理精华 文章标签： SQL Server 查询性能管理 资源调控器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/go5gopher/article/details/150568941

SQL Server查询性能管理与专业DBA成长秘诀

1. 工作负载组

资源调控器使用工作负载组将具有预定义共同特征的所有传入请求作为一个单元进行隔离。工作负载组允许你为每个单独的工作负载组定义和监控特定属性，还能根据监控统计信息在资源组之间动态移动工作负载组。工作负载组包含两个预定义组：内部组和默认组，它们与相应的资源池映射。

创建工作负载组时可设置以下属性：
| 属性 | 描述 | 有效值 | 默认值 |
| ---- | ---- | ---- | ---- |
| IMPORTANCE | 工作负载组在资源池内的重要性权重 | LOW、MEDIUM、HIGH | MEDIUM |
| REQUEST_MAX_MEMORY_GRANT_PERCENT | 单个请求允许从给定资源池消耗的最大内存百分比 | 0 - 100 | 25 |
| REQUEST_MAX_CPU_TIME_SEC | 请求在生成CPU阈值超出事件之前运行的最大时间（秒） | 0或更大 | 0（无限制） |
| REQUEST_MEMORY_GRANT_TIMEOUT_SEC | 查询等待内存授予可用的最大时间（秒） | 0或更大 | 0（基于查询成本的内部计算） |
| MAX_DOP | 工作负载组的最大并行度 | | 0（使用全局SQL Server设置） |
| GROUP_MAX_REQUESTS | 工作负载组在任何给定时间可以执行的最大并发请求数 | 0或更大 | 0（无限制） |

以下是创建备份、报告和测试工作负载组的脚本： 

CREATE WORKLOAD GROUP BackupGroup
USING BackupPool
GO

CREATE WORKLOAD GROUP ReportingGroup  
WITH (IMPORTANCE = MEDIUM,
      REQUEST_MAX_MEMORY_GRANT_PERCENT = 30,
      REQUEST_MAX_CPU_TIME_SEC = 0,
      REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
      MAX_DOP = 0,
      GROUP_MAX_REQUESTS = 0)  
USING ReportingPool  
GO

CREATE WORKLOAD GROUP TestingGroup  
WITH (IMPORTANCE = LOW,
      REQUEST_MAX_MEMORY_GRANT_PERCENT = 20,
      REQUEST_MAX_CPU_TIME_SEC = 180,
      REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
      MAX_DOP = 1,
      GROUP_MAX_REQUESTS = 20)  
GO
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

如果不手动为工作负载组分配特定资源池，它将自动分配到默认资源池。例如， TestingGroup 未分配特定资源池，将在默认资源池创建，并且对其进行了较多限制，如低优先级、单个请求仅使用20%的资源池、单处理器以及最多同时运行20个进程，还配置为在查询执行时间超过180秒时触发事件。

2. 分类器函数

分类器函数是一个T - SQL函数，为每个登录执行，可编写逻辑将传入请求路由到适当的工作负载组。必须在 master 数据库中创建分类器函数，并且资源调控器在任何给定时间只能使用一个分类器函数，但可以创建多个并动态更改使用的函数。

创建分类器函数可使用以下函数：
- H0ST_NAME()
- APP_NAME()
- SUSER_NAME()
- SUSER_SNAME()
- IS_SRVROLEMEMBER()
- IS_MEMBER()
- LOGINPROPERTY(suser_name(),’DefaultDatabase’)
- LOGINPROPERTY(suser_name(),’DefaultLanguage’)
- ORIGINAL_DB_NAME()
- CONNECTIONPROPERTY( )

也可以创建查找表辅助分类器函数。需注意，SQL Server会为每个登录执行分类器函数，所以应尽可能高效以避免性能问题，且专用管理员连接不会被分类。

以下是创建分类器函数的脚本： 

USE master  
GO
CREATE FUNCTION RG_Classifier_V1()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @GroupName sysname
  IF (IS_MEMBER('KEN - PC\TestingGroup') = 1)  
    SET @GroupName = 'TestingGroup'
  ELSE IF (APP_NAME() LIKE '%REPORT SERVER%')  
    SET @GroupName = 'ReportingGroup'
  ELSE IF (SUSER_NAME() = 'BackupUser')  
    SET @GroupName = 'BackupGroup'
  ELSE  
    SET @GroupName = 'default'
  RETURN @GroupName
END  
GO
--Assign the function to the Resource Governor and reconfigure
ALTER RESOURCE GOVERNOR  
WITH (CLASSIFIER_FUNCTION = dbo.RG_Classifier_V1)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

要从资源调控器中移除分类器函数，可运行以下脚本： 

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
3. 监控资源调控器

监控资源调控器配置可验证设置过程中的选择，资源调控器监控提供必要数据，以便基于明智决策调整配置。可使用性能计数器、跟踪事件和视图进行监控。

3.1 性能计数器

有两个性能计数器可用于收集资源组和工作负载组的信息：SQLServer:Workload Group Stats和SQLServer:Resource Pool Stats。可运行以下查询查看默认工作负载组和资源池的性能计数器属性列表： 

SELECT *
FROM sys.dm_os_performance_counters  
WHERE object_name IN ('MSSQL$SQL11:Workload Group Stats',
                      'MSSQL$SQL11:Resource Pool Stats') AND
      instance_name = 'default'
3.2 跟踪事件

有三个有价值的跟踪事件可用于获取资源调控器的信息：CPU Threshold Exceeded、PreConnect:Starting和PreConnect:Completed。
- CPU Threshold Exceeded：当查询超过为工作负载组设置的REQUEST_MAX_CPU_TIME_SEC值时触发。
- PreConnect:Starting：分类器函数开始执行时触发。
- PreConnect:Completed：分类器函数执行完成后触发。

3.3 视图

有七个视图，包括四个动态管理视图（DMV）和三个目录视图，可深入了解资源调控器配置。DMV提供统计信息，目录视图提供当前配置信息。

资源调控器DMV： 

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

资源调控器目录视图： 

SELECT * FROM sys.resource_governor_workload_groups  
SELECT * FROM sys.resource_governor_resource_pools  
SELECT * FROM sys.resource_governor_configuration
4. 专业DBA成长秘诀
4.1 做好本职工作

要在任何职业中出类拔萃，必须擅长自己的工作。如果不是优秀的数据库管理员，就无法在DBA角色中取得卓越成就。设定目标并努力实现，即使现在不是优秀的DBA，几年内也可能成为杰出的DBA。

4.2 面对现实

作为优秀的DBA，要面对现实，对自己的知识和能力有清晰认识，对他人也要诚实。知道何时能独立完成任务，何时需要寻求帮助，并做好妥协的准备。

4.3 接受自己不是最好的

不必成为组织中最好的DBA才能超越其他DBA。理解在SQL Server的某些主题上会有比自己更了解的DBA，努力拥有比大多数人更全面的SQL Server知识，能明智地讨论各种主题，并在某个感兴趣的领域深入钻研。

4.4 接受并从批评中学习

职业生涯中会因表现不佳受到批评，要接受批评并从中学习，不要将批评个人化，避免怀疑自己的能力。犯错不可耻，但不要重复犯错。

4.5 接受事情不会总是如你所愿

除非自己经营公司，否则要接受事情不会总是按自己的意愿发展。决策制定者不一定总是同意你的建议，此时要做到：
- 学会妥协：当事情可能不按自己的意愿发展时，找出决策者倾向的计划中自己认可的部分，积极讨论，然后争取加入自己计划中不可或缺的部分。
- 记住层级关系：无论多不同意决策者的计划，只能提供建议。决策者做出决定后，记录决定及其原因，尝试让计划变得更好。

4.6 接受有些目标不现实

设定职业目标时，要判断目标是否现实。很多人因第一个目标不现实或耗时过长而无法开始执行目标列表。如果目标需要大量个人时间和精力，而自己无法投入，可从列表中删除该目标或分解为多个小目标，如加入SQL Server用户组或完成认证，通过快速实现小目标建立动力，最终实现看似无法实现的目标。

SQL Server查询性能管理与专业DBA成长秘诀

5. 总结

SQL Server 2012提供了多种管理查询性能的方法。像动态管理视图（DMVs）这类功能，能轻松从当前运行和历史角度追踪性能不佳的查询。查询提示和资源调控器等功能则能对查询进行更精细的控制。不过要记住，“能力越大，责任越大”，使用影响查询优化器的选项时需谨慎。

通过对查询性能进行更精细的控制，SQL Server 2012能让你维护一个更强大、可预测的环境，减少因失控的异常查询而产生的问题。

以下通过一个mermaid流程图展示整个查询性能管理和DBA成长的关键路径： 

graph LR
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    A(查询性能管理):::process --> B(工作负载组配置):::process
    A --> C(分类器函数设置):::process
    A --> D(资源调控器监控):::process
    B --> B1(创建工作负载组):::process
    B --> B2(设置工作负载组属性):::process
    C --> C1(创建分类器函数):::process
    C --> C2(分配分类器函数):::process
    D --> D1(使用性能计数器):::process
    D --> D2(利用跟踪事件):::process
    D --> D3(查看视图):::process
    E(专业DBA成长):::process --> F(提升非技术技能):::process
    F --> F1(做好本职工作):::process
    F --> F2(面对现实):::process
    F --> F3(接受不完美):::process
    F --> F4(从批评中学习):::process
    F --> F5(接受事情不如意):::process
    F --> F6(设定现实目标):::process
6. 操作步骤总结

为了让大家更清晰地了解整个操作流程，下面对前面提到的关键操作步骤进行总结：

6.1 工作负载组创建步骤
  1. 确定工作负载组的名称和所属资源池。
  2. 根据需求设置工作负载组的属性，如重要性、内存授予百分比、CPU时间限制等。
  3. 使用 CREATE WORKLOAD GROUP 语句创建工作负载组。
  4. 运行 ALTER RESOURCE GOVERNOR RECONFIGURE 语句使配置生效。

示例代码： 

CREATE WORKLOAD GROUP BackupGroup
USING BackupPool
GO

CREATE WORKLOAD GROUP ReportingGroup  
WITH (IMPORTANCE = MEDIUM,
      REQUEST_MAX_MEMORY_GRANT_PERCENT = 30,
      REQUEST_MAX_CPU_TIME_SEC = 0,
      REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
      MAX_DOP = 0,
      GROUP_MAX_REQUESTS = 0)  
USING ReportingPool  
GO

CREATE WORKLOAD GROUP TestingGroup  
WITH (IMPORTANCE = LOW,
      REQUEST_MAX_MEMORY_GRANT_PERCENT = 20,
      REQUEST_MAX_CPU_TIME_SEC = 180,
      REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
      MAX_DOP = 1,
      GROUP_MAX_REQUESTS = 20)  
GO
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO
6.2 分类器函数创建与分配步骤
  1. 在 master 数据库中创建分类器函数，使用合适的函数和逻辑来确定请求的目标工作负载组。
  2. 使用 ALTER RESOURCE GOVERNOR 语句将分类器函数分配给资源调控器。
  3. 运行 ALTER RESOURCE GOVERNOR RECONFIGURE 语句使配置生效。

示例代码： 

USE master  
GO
CREATE FUNCTION RG_Classifier_V1()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @GroupName sysname
  IF (IS_MEMBER('KEN - PC\TestingGroup') = 1)  
    SET @GroupName = 'TestingGroup'
  ELSE IF (APP_NAME() LIKE '%REPORT SERVER%')  
    SET @GroupName = 'ReportingGroup'
  ELSE IF (SUSER_NAME() = 'BackupUser')  
    SET @GroupName = 'BackupGroup'
  ELSE  
    SET @GroupName = 'default'
  RETURN @GroupName
END  
GO
--Assign the function to the Resource Governor and reconfigure
ALTER RESOURCE GOVERNOR  
WITH (CLASSIFIER_FUNCTION = dbo.RG_Classifier_V1)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
6.3 资源调控器监控操作步骤
  • 性能计数器监控 ：运行查询语句从 sys.dm_os_performance_counters 视图中获取相关性能计数器信息。
    示例代码： 
SELECT *
FROM sys.dm_os_performance_counters  
WHERE object_name IN ('MSSQL$SQL11:Workload Group Stats',
                      'MSSQL$SQL11:Resource Pool Stats') AND
      instance_name = 'default'
  • 跟踪事件监控 ：在SQL Server Profiler中关注 CPU Threshold Exceeded 、 PreConnect:Starting 和 PreConnect:Completed 事件。
  • 视图监控 ：运行查询语句从资源调控器的DMV和目录视图中获取统计信息和配置信息。
    DMV查询示例： 
SELECT * FROM sys.dm_resource_governor_workload_groups  
SELECT * FROM sys.dm_resource_governor_resource_pools  
SELECT * FROM sys.dm_resource_governor_configuration 
SELECT * FROM sys.dm_resource_governor_resource_pool_affinity

目录视图查询示例： 

SELECT * FROM sys.resource_governor_workload_groups  
SELECT * FROM sys.resource_governor_resource_pools  
SELECT * FROM sys.resource_governor_configuration
7. 关键要点回顾

为了方便大家快速回顾，下面以表格形式总结关键要点：
| 主题 | 关键内容 |
| ---- | ---- |
| 工作负载组 | 包含内部和默认预定义组，创建时可设置重要性、内存和CPU使用限制等属性 |
| 分类器函数 | 在 master 数据库创建，为每个登录执行，用于路由请求到工作负载组，使用时注意大小写敏感 |
| 资源调控器监控 | 可通过性能计数器、跟踪事件和视图进行监控，获取统计和配置信息 |
| 专业DBA成长 | 需具备做好本职工作、面对现实、接受批评等非技术技能，设定现实目标 |

通过以上对SQL Server查询性能管理和专业DBA成长秘诀的介绍，希望能帮助大家更好地管理数据库和提升自身能力，在数据库领域取得更好的成绩。

评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值