37、SQL Server 性能监控全解析

职场萌新987

于 2025-08-12 09:25:46 发布

阅读量166

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server 2012管理精华文章标签： SQL Server 性能监控内存监控

本文链接：https://blog.youkuaiyun.com/go5gopher/article/details/150568925

SQL Server 2012管理精华专栏收录该内容

46 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

SQL Server 性能监控全解析

1. 内存监控基础指标

1.1 系统内存相关指标

在监控系统内存时，有几个关键指标需要关注：
- Page Faults/Sec ：表示系统每秒发生的页面错误率，它同时跟踪硬页面错误和软页面错误。硬页面错误需要访问磁盘来检索请求的数据，而软页面错误则从物理内存中检索数据。由于该值因系统而异，所以要使用基线来判断页面错误率何时偏离正常范围。不过，大量的页面错误不一定需要惊慌，系统通常可以处理，但要密切关注硬页面错误的数量。可以结合物理 IO 计数器来确保硬页面错误不会导致磁盘 IO 问题。
- Pages Input/Sec ：跟踪为解决硬页面错误而从磁盘读取页面的速率。理想情况下，每秒为解决硬页面错误而读入内存的页面不应超过 10 个。该计数器在确定每秒硬页面错误的数量时非常有用，可以使用以下公式计算硬页面错误的百分比：Pages input per second / page faults per second = percentage hard page faults。当硬页面错误百分比长时间超过 40% 时，系统可能存在内存问题。
- Pages/Sec ：标识为解决不在内存中的页面的内存请求而从磁盘读取和写入磁盘的页面速率，即代表 Pages input/sec 和 Pages output/sec。Pages output 表示从内存移除并前往磁盘的页面。Pages/Sec 计数器的高数值不一定意味着存在内存问题，需要结合其他计数器和基线来判断。
- Page Reads/Sec ：表示为解决硬页面错误而对物理磁盘进行读取的速率，该计数器捕获每秒的读取次数，而非每秒读入内存的页面数。可以结合 Pages Input/Sec 计数器来确定为解决硬页面错误而读取磁盘的次数以及插入内存的页面数。每秒读入内存的页面不应超过 5 个，大于 5 的值可能表示存在过度分页问题。

1.2 SQL Server 内存计数器

1.3 内存监控的 DMV 和 DBCC 命令

SQL Server 还提供了一些动态管理视图（DMV）和数据库一致性检查（DBCC）命令来监控内存：
- sys.dm_os_sys_memory ：返回有关操作系统内存的信息，包括系统总内存、可用内存和使用的页面文件总量等。其中，system_memory_state_desc 列描述了内存的当前状态，如 Available Physical Memory Is High、Available Physical Memory Is Low、Physical Memory Is Steady 和 Physical Memory Is Transitioning。可以使用以下查询检索信息：

SELECT total_physical_memory_kb,available_physical_memory_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory

sys.dm_os_process_memory ：列出 SQL Server 实例内使用的内存信息，包括物理内存使用情况、可用提交限制、总虚拟地址大小等。该视图还有两个指标 Process Physical Memory Low 和 Process Virtual Memory Low，可让你知道 SQL Server 是否认为虚拟或物理内存不足。
sys.dm_exec_query_memory_grants ：捕获正在等待或已获得内存授予的查询的信息，包括每个查询请求、授予、所需、最大使用和理想内存的数量，还返回 SQL 句柄和计划句柄的信息，有助于识别消耗大量内存的查询并分析其执行计划。
sys.dm_os_buffer_descriptors ：显示每个数据库在内存中的数据页数量，在繁忙的多数据库系统中非常有用，可帮助确定哪些数据库占用了大量内存以及数据库之间是否存在内存竞争。可以使用以下查询来识别每个数据库缓存的页面数量：

SELECT count(*)AS cached_pages_count
    ,CASE database_id  
        WHEN 32767 THEN 'ResourceDb'  
        ELSE db_name(database_id)  
        END AS Database_name 
FROM sys.dm_os_buffer_descriptors 
GROUP BY db_name(database_id) ,database_id 
ORDER BY cached_pages_count DESC;

DBCC memorystatus ：该 DBCC 命令列出了缓冲管理器、内存 clerks 和其他可能消耗 SQL Server 内存的进程的内存使用情况。虽然其中一些信息可以在多个 DMV 中找到，但该命令提供了一站式的信息查询方式，并且与 SQL Server 2000 向后兼容。

此外，一些常见的 DMV 中存在跟踪查询内存使用情况的列，如 sys.dm_exec_sessions 中的 memory_usage 列或 sys.dm_exec_request 中的 granted_query_memory 列，可以按这些列对当前运行的查询进行排序，以识别使用最多内存的查询。

2. 磁盘 IO 监控

2.1 磁盘 IO 监控的重要性

除非整个数据库都能放入内存，否则 SQL Server 的性能在很大程度上取决于磁盘 IO。磁盘瓶颈会影响页面进出内存的速度，最终影响应用程序的性能。

2.2 磁盘 IO 相关计数器及推荐值

2.3 磁盘 IO 监控流程

graph LR
    A[开始监控磁盘 IO] --> B[选择合适的计数器]
    B --> C{是否需要考虑主轴数量}
    C -- 是 --> D[结合主轴数量评估结果]
    C -- 否 --> E[直接评估结果]
    D --> F{结果是否超出推荐值}
    E --> F
    F -- 是 --> G[调查原因并解决问题]
    F -- 否 --> H[继续监控]
    G --> H
    H --> I[结束监控]

2.4 磁盘 IO 监控的 Windows 计数器

Avg Disk Queue Length ：表示所选磁盘上排队的物理读写操作数量。当磁盘 IO 子系统不堪重负时，物理磁盘读写操作需要等待才能执行。每个主轴的持续值大于 2 时需要关注。
Avg Disk Reads/Sec or Writes/Sec ：捕获读写数据到磁盘的平均时间，该计数器测量磁盘延迟，与磁盘阵列中的主轴数量无关，以毫秒为单位。当计数器超过 20 ms 时，需要研究磁盘读写增加的原因。
Reads/Sec or Writes/Sec ：测量磁盘每秒的读写次数，这些数值应小于磁盘容量的 85%。因为超过 85% 的容量后，访问时间会呈指数级增加。
% Disk Time ：表示磁盘驱动器的繁忙程度，持续超过 55% 表示磁盘过载，可能存在磁盘 IO 问题，需要找出磁盘繁忙的原因以减少 IO 问题。
Current Disk Queue ：测量当前等待 IO 的进程数量，结合 % Disk Time 可以帮助理解磁盘的运行情况。如果当前磁盘队列高且 % Disk Time 高于基线，可能表示应用程序进程延迟，磁盘存在瓶颈。

2.5 磁盘 IO 监控的 SQL Server 计数器

Full Scans/Sec ：识别系统每秒发生的表和索引扫描次数。扫描操作是 IO 密集型的，应尽量减少。如果扫描计数器值较高，需要识别有大量扫描的表，并使用相关技巧来识别 IO 密集型查询和导致扫描的表。
Page Splits/Sec ：表示系统每秒发生的页面拆分数量。当页面上没有足够空间添加现有页面的数据时会发生页面拆分，拆分后会导致碎片化和 IO 密集型进程。应尽量将页面拆分保持在每 100 批处理少于 20 次，如果超过该数值，需要审查索引的填充因子并在适当的地方进行调整。

此外，一些常用的 DMV 中存储了 reads 和 logical_reads 信息，可以使用 sys_dm_exec_requests 按 reads 和/或 logical_reads 对当前运行的查询进行排序，以识别使用最多 IO 的查询。还可以使用 sys.dm_exec_stats DMV 中的 total_physical_reads 和 total_logical_reads 列来识别一段时间内使用最多 IO 的查询，并计算查询的平均 IO，确定需要审查的查询。

3. 其他监控指标

3.1 杂项计数器

两个通用计数器 Logins/Sec 和 User Connections 在监控 SQL Server 时可能会有用，它们应作为基线的一部分，以便随时间进行比较。这些计数器有助于了解硬件资源的逐渐变化，以及确定数据库服务器资源被占用的原因。例如，如果环境中的用户数量突然翻倍，这些信息可以节省大量的故障排除时间。

3.2 等待类型监控

SQL Server 会识别执行查询或系统进程的线程为完成任务而需要“等待”的原因。线程等待完成的时间越长，用户查询的响应时间就越长。如果能够识别并解决查询执行时间过长的原因，就可以显著提高查询的响应时间和应用程序的性能。

在监控等待类型时，有两个常见术语：Wait Type 和 Wait Time。Wait Type 表示 SQL Server 中线程需要等待完成的原因，Wait Time 表示特定 Wait Type 等待完成的时间。可以通过查询 sys.dm_os_wait_stats DMV 来查看 SQL Server 使用的等待类型列表及其简要描述，还可以审查查询结果以查看实例的累积等待统计信息并诊断性能问题。

另一个常用的 DMV 是 sys.dm_os_waiting_tasks，它提供了系统中当前正在等待的线程的信息，其中的 session_id 列可以排除系统等待类型，仅关注用户会话。当需要识别导致等待时间增加的查询文本和执行计划时，可以将该 DMV 与 sys.dm_exec_requests 连接以获取所需的其余数据。

当遇到长时间的等待问题时，可以使用 Books Online 和搜索引擎来解决。首先要弄清楚等待类型的含义，然后找出问题的原因并寻求解决方案。解决方案可能包括重写查询、更换网卡、升级磁盘等。同时，要对系统进行基线设置，以便了解主导等待类型何时偏离正常范围，从而确定系统中是否存在新的变量并及时解决问题。对于新手来说，可以使用 Activity Monitor 来帮助理解等待类型的监控，该工具可以识别系统的等待统计信息。

综上所述，通过全面监控内存、磁盘 IO 以及其他相关指标，可以及时发现 SQL Server 系统中的性能问题，并采取相应的措施进行优化和解决，从而确保系统的稳定运行和高效性能。

4. 综合监控策略

4.1 多维度监控的必要性

在监控 SQL Server 时，单一维度的监控往往无法全面反映系统的真实性能状况。例如，仅监控内存指标可能会忽略磁盘 IO 瓶颈对系统性能的影响；而只关注磁盘 IO 计数器，又可能错过内存不足导致的性能问题。因此，需要采用多维度的监控策略，将内存、磁盘 IO、杂项计数器以及等待类型等多个方面的监控结合起来，才能更准确地发现系统中的潜在问题。

4.2 监控流程整合

graph LR
    A[开始综合监控] --> B[监控内存指标]
    B --> C[监控磁盘 IO 指标]
    C --> D[监控杂项计数器]
    D --> E[监控等待类型]
    E --> F{是否发现异常指标}
    F -- 是 --> G[深入分析异常原因]
    F -- 否 --> H[继续监控]
    G --> I[制定解决方案]
    I --> J[实施解决方案]
    J --> H
    H --> K[结束监控]

4.3 异常处理流程

当监控过程中发现异常指标时，需要按照以下步骤进行处理：
1. 确认异常指标 ：首先要确定哪些指标超出了正常范围，例如内存指标中的硬页面错误百分比过高、磁盘 IO 指标中的平均磁盘队列长度过长等。
2. 关联相关指标 ：分析异常指标与其他指标之间的关联关系。例如，如果发现磁盘 IO 指标异常，需要查看内存指标是否也存在问题，因为内存不足可能会导致频繁的磁盘读写操作。
3. 定位问题根源 ：根据关联分析的结果，逐步缩小问题范围，定位问题的根源。可能的原因包括查询优化不当、硬件资源不足、配置参数不合理等。
4. 制定解决方案 ：根据问题根源，制定相应的解决方案。例如，如果是查询优化问题，可以对查询进行重写或添加索引；如果是硬件资源不足，可以考虑升级硬件或调整资源分配。
5. 实施解决方案 ：将制定好的解决方案付诸实施，并监控系统性能的变化。如果问题得到解决，继续进行正常的监控；如果问题仍然存在，需要重新分析并调整解决方案。

5. 监控工具与技巧

5.1 常用监控工具

Performance Monitor ：Windows 系统自带的性能监控工具，可以监控系统的各种性能指标，包括内存、磁盘 IO、CPU 使用率等。可以通过添加相应的计数器来监控 SQL Server 的性能。
SQL Server Management Studio (SSMS) ：SQL Server 提供的管理工具，可以方便地查看 SQL Server 的各种性能计数器和动态管理视图。通过 SSMS 可以执行查询来获取监控数据，并进行分析和可视化展示。
Activity Monitor ：SQL Server 中的一个工具，可以实时监控 SQL Server 的活动情况，包括当前运行的查询、等待类型、锁信息等。对于新手来说，Activity Monitor 是一个很好的监控入门工具。

5.2 监控数据的分析技巧

建立基线 ：在系统正常运行时，收集一段时间的监控数据，建立系统的性能基线。通过与基线进行比较，可以更容易地发现系统性能的变化和异常。
趋势分析 ：观察监控数据的变化趋势，例如内存使用率、磁盘 IO 速率等是否呈现上升或下降的趋势。趋势分析可以帮助预测系统性能的变化，提前采取措施进行优化。
关联分析 ：分析不同监控指标之间的关联关系，找出可能存在的问题根源。例如，当发现磁盘 IO 指标异常时，查看内存指标和查询执行情况，判断是否是由于内存不足或查询优化不当导致的。
可视化展示 ：将监控数据以图表、报表等形式进行可视化展示，使数据更加直观易懂。可视化展示可以帮助快速发现数据中的异常和趋势，提高分析效率。