印刻数据库

外观

SQLServer 常用监控指标详解 ​

监控指标概述 ​

SQL Server 监控是数据库运维的重要组成部分，通过监控可以及时发现性能瓶颈、预防潜在问题、优化系统配置，并确保系统的稳定性和可用性。有效的监控策略需要结合多种监控工具和指标，建立合理的基准值和告警机制。

监控的重要性 ​

  • 故障预警：提前发现潜在问题，避免故障发生
  • 性能优化：识别性能瓶颈，针对性优化
  • 容量规划：基于历史数据预测资源需求
  • 合规要求：满足审计和合规性需求
  • 故障排查：发生故障时快速定位问题根源

监控指标分类 ​

分类主要指标监控工具
CPU 监控CPU 使用率、批处理请求数、并行度性能监视器、DMVs、Extended Events
内存监控内存使用率、缓冲池命中率、页面寿命预期性能监视器、sys.dm_os_memory_clerks
磁盘 I/O 监控读写延迟、吞吐量、队列长度性能监视器、sys.dm_io_virtual_file_stats
网络监控网络吞吐量、连接数性能监视器、sys.dm_exec_sessions
查询性能监控慢查询、执行计划、等待统计Query Store、sys.dm_exec_query_stats
事务和锁监控锁等待、死锁、事务日志增长sys.dm_tran_locks、sys.dm_db_log_space_usage
系统可用性监控服务状态、数据库状态故障转移群集管理器、sys.databases

CPU 监控指标 ​

Processor: % Processor Time ​

指标说明：CPU 使用率，反映 CPU 繁忙程度，是最基本的 CPU 监控指标。

正常范围：

  • 持续 < 70%：正常
  • 70-90%：警告，需要关注
  • 持续 > 90%：严重，需要立即处理

监控工具：

  • 性能监视器：Processor 计数器组
  • DMV：sys.dm_os_ring_buffers、sys.dm_os_performance_counters

版本差异：

  • SQLServer 2008 及以前：主要依赖性能监视器和 Profiler
  • SQLServer 2012 及以后：新增 sys.dm_os_ring_buffers 增强版视图
  • SQLServer 2016 及以后：Query Store 可用于分析查询对 CPU 的影响

生产场景优化建议：

  1. 识别高 CPU 查询：使用以下 DMV 查询找出消耗 CPU 最多的查询
    sql
    SELECT TOP 10
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.total_worker_time/1000000 AS total_cpu_seconds,
    qs.execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;
  2. 调整 MAXDOP：对于 OLTP 系统，建议将 MAXDOP 设置为 1 或 CPU 核心数的 1/4
  3. 检查外部进程：使用任务管理器或 sys.dm_os_process_memory 检查是否有外部进程消耗 CPU
  4. 考虑 CPU 升级：在确认软件优化空间有限后，考虑硬件升级

SQL Server: SQL Statistics: Batch Requests/sec ​

指标说明：每秒处理的 SQL 批处理请求数，反映系统的工作负载强度。

正常范围：根据系统配置和工作负载不同而变化，需要建立基准值。例如：

  • 小型系统：500-1000 批/秒
  • 中型系统：1000-5000 批/秒
  • 大型系统：5000+ 批/秒

监控工具：

  • 性能监视器：SQL Server: SQL Statistics 计数器组
  • DMV：sys.dm_os_performance_counters

版本差异：

  • 所有版本均支持此指标
  • SQLServer 2016 及以后：可通过 Query Store 关联批处理请求与具体查询

生产场景优化建议：

  1. 结合 CPU 使用率分析：高批处理请求数 + 高 CPU 使用率可能表示系统过载
  2. 优化高频查询：针对 TOP N 高频执行的查询进行优化
  3. 使用查询缓存：对于重复执行的查询，确保使用参数化查询以提高缓存命中率

sys.dm_os_schedulers: runnable_tasks_count ​

指标说明：可运行任务数，反映 CPU 压力。当 CPU 资源不足时，此指标会持续升高。

正常范围：持续 < 10 为正常，超过 20 表示 CPU 压力较大。

监控工具：

  • DMV：sys.dm_os_schedulers

版本差异：

  • SQLServer 2005 及以后支持
  • SQLServer 2012 及以后：增强了视图的统计信息

生产场景优化建议：

  1. 查询可运行任务：
    sql
    SELECT scheduler_id, runnable_tasks_count, current_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;
  2. 识别瓶颈查询：使用 sys.dm_exec_requests 查找长时间运行的查询
  3. 调整并行度：适当降低 MAXDOP 设置，减少并行查询对 CPU 的压力

内存监控指标 ​

Memory: Available MBytes ​

指标说明：服务器可用物理内存（MB），反映系统整体内存压力。

正常范围：

  • 持续 > 500 MB：正常
  • 500-100 MB：警告，内存压力增大
  • 持续 < 100 MB：严重，系统内存不足

监控工具：

  • 性能监视器：Memory 计数器组

版本差异：

  • 所有版本均支持

生产场景优化建议：

  1. 调整 max_server_memory：根据服务器总内存合理分配 SQL Server 可用内存
    sql
    -- 设置 SQL Server 最大内存为 64GB
EXEC sp_configure 'max server memory (MB)', 65536;
RECONFIGURE;
  2. 关闭不必要的服务：释放服务器内存资源
  3. 优化内存密集型查询：减少查询的内存使用

SQL Server: Buffer Manager: Buffer Cache Hit Ratio ​

指标说明：缓冲池命中率，反映内存中数据页的命中率，越高表示内存利用效率越好。

正常范围：

  • 持续 > 99%：优秀
  • 95-99%：良好
  • < 95%：需要关注，可能存在内存不足

监控工具：

  • 性能监视器：SQL Server: Buffer Manager 计数器组
  • DMV：sys.dm_os_performance_counters

版本差异：

  • 所有版本均支持
  • SQLServer 2016 及以后：可通过 Query Store 分析导致低命中率的查询

生产场景优化建议：

  1. 增加内存：如果命中率持续低于 95%，考虑增加 SQL Server 可用内存
  2. 优化查询：减少逻辑读取，例如添加合适的索引
  3. 使用内存优化表：对于频繁访问的小表，考虑使用内存优化表

SQL Server: Buffer Manager: Page Life Expectancy ​

指标说明：页面在缓冲池中停留的平均时间（秒），反映内存压力。值越高表示内存充足，页面不需要频繁换入换出。

正常范围：

  • 持续 > 300 秒：正常
  • 300-100 秒：警告，内存压力增大
  • 持续 < 100 秒：严重，内存不足

监控工具：

  • 性能监视器：SQL Server: Buffer Manager 计数器组
  • DMV：sys.dm_os_performance_counters

版本差异：

  • 所有版本均支持
  • SQLServer 2012 及以后：引入了 sys.dm_os_memory_nodes，支持 NUMA 架构的细粒度监控

生产场景优化建议：

  1. NUMA 架构下的监控：对于 NUMA 服务器，需要监控每个 NUMA 节点的 PLE
    sql
    SELECT node_id, pages_kb, page_life_expectancy
FROM sys.dm_os_memory_nodes
WHERE node_id <> 64;
  2. 增加内存：如果 PLE 持续低于 300 秒，考虑增加内存
  3. 优化 I/O：使用更快的存储设备，减少页面读取延迟

磁盘 I/O 监控指标 ​

PhysicalDisk: Avg. Disk sec/Read ​

指标说明：平均读操作延迟（秒），反映磁盘读性能，是评估存储性能的关键指标。

正常范围：

  • HDD：< 10 ms 为优秀，10-20 ms 为良好，20-50 ms 为一般，> 50 ms 为差
  • SSD：< 1 ms 为优秀，1-5 ms 为良好，> 5 ms 为一般

监控工具：

  • 性能监视器：PhysicalDisk 计数器组
  • DMV：sys.dm_io_virtual_file_stats

版本差异：

  • 所有版本均支持
  • SQLServer 2012 及以后：sys.dm_io_virtual_file_stats 增加了更多统计信息

生产场景优化建议：

  1. 监控数据库文件 I/O：
    sql
    SELECT 
    db_name(database_id) AS database_name,
    file_id,
    io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency_ms,
    io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY avg_read_latency_ms DESC;
  2. 存储分层：将频繁访问的数据放在 SSD 上，不常用数据放在 HDD 上
  3. 优化查询：减少物理读取，例如添加索引或优化查询计划

PhysicalDisk: Avg. Disk sec/Write ​

指标说明：平均写操作延迟（秒），反映磁盘写性能，对事务日志和数据文件的写入性能影响较大。

正常范围：

  • HDD：< 10 ms 为优秀，10-20 ms 为良好，20-50 ms 为一般，> 50 ms 为差
  • SSD：< 1 ms 为优秀，1-5 ms 为良好，> 5 ms 为一般

监控工具：

  • 性能监视器：PhysicalDisk 计数器组
  • DMV：sys.dm_io_virtual_file_stats

版本差异：

  • 所有版本均支持

生产场景优化建议：

  1. 分离事务日志：将事务日志放在单独的高速磁盘上
  2. 优化日志写入：减少长事务，调整日志文件大小和增长设置
  3. 批量操作优化：对于大量数据写入，考虑使用批量日志恢复模式

SQL Server: Databases: Log Flush Waits/sec ​

指标说明：每秒日志刷新等待次数，反映事务日志写入压力，高值表示日志写入瓶颈。

正常范围：< 10 为正常，超过 50 表示日志写入压力较大。

监控工具：

  • 性能监视器：SQL Server: Databases 计数器组

版本差异：

  • SQLServer 2005 及以后支持

生产场景优化建议：

  1. 高速存储日志：将事务日志放在 SSD 上
  2. 调整日志文件：设置合适的初始大小和增长步长，避免频繁自动增长
    sql
    -- 修改日志文件大小为 10GB，增长步长为 1GB
ALTER DATABASE [YourDatabase] 
MODIFY FILE (NAME = N'YourDatabase_Log', SIZE = 10GB, FILEGROWTH = 1GB);
  3. 减少长事务：拆分长事务，避免长时间占用日志写入资源

网络监控指标 ​

Network Interface: Bytes Total/sec ​

指标说明：网络接口的总吞吐量（字节/秒），反映网络带宽使用情况。

正常范围：根据网络带宽和工作负载而定，不应持续超过带宽的 80%。

监控工具：

  • 性能监视器：Network Interface 计数器组

版本差异：

  • 所有版本均支持

生产场景优化建议：

  1. 优化查询结果集：只返回必要的列，减少数据传输量
  2. 使用数据压缩：启用数据库压缩或网络压缩
  3. 升级网络带宽：如果持续超过带宽的 80%，考虑升级网络

SQL Server: General Statistics: User Connections ​

指标说明：当前连接到 SQL Server 的用户连接数，反映系统的并发访问情况。

正常范围：根据系统配置和许可证而定，不应超过最大连接数（默认 32767）。

监控工具：

  • 性能监视器：SQL Server: General Statistics 计数器组
  • DMV：sys.dm_exec_sessions

版本差异：

  • 所有版本均支持
  • SQLServer 2016 及以后：可通过 Query Store 分析连接数与查询性能的关系

生产场景优化建议：

  1. 监控连接状态：
    sql
    SELECT 
    status,
    COUNT(*) AS connection_count
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY status;
  2. 使用连接池：应用程序中使用连接池管理连接，避免频繁创建和关闭连接
  3. 识别异常连接：查找长时间运行的连接或空闲连接

查询性能监控指标 ​

SQL Server: Query Store: Query Store CPU usage ​

指标说明：Query Store 功能消耗的 CPU 资源，过高的 CPU 使用率可能影响系统性能。

正常范围：< 5% 为正常，超过 10% 需要调整 Query Store 配置。

监控工具：

  • 性能监视器：SQL Server: Query Store 计数器组

版本差异：

  • SQLServer 2016 及以后支持

生产场景优化建议：

  1. 调整 Query Store 配置：
    sql
    ALTER DATABASE [YourDatabase] 
SET QUERY_STORE (OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 3600);
  2. 定期清理 Query Store 数据：
    sql
    ALTER DATABASE [YourDatabase] 
SET QUERY_STORE CLEAR ALL;
  3. 选择性启用 Query Store：只在需要监控的数据库上启用

sys.dm_exec_query_stats: total_worker_time ​

指标说明：查询消耗的总 CPU 时间（微秒），用于识别 CPU 密集型查询。

监控工具：

  • DMV：sys.dm_exec_query_stats 结合 sys.dm_exec_sql_text

版本差异：

  • SQLServer 2005 及以后支持
  • SQLServer 2016 及以后：可与 Query Store 结合使用，提供更完整的查询性能分析

生产场景优化建议：

  1. 查找高 CPU 查询：
    sql
    SELECT TOP 10
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.total_worker_time/1000000 AS total_cpu_seconds,
    qs.execution_count,
    qs.total_worker_time/qs.execution_count/1000 AS avg_cpu_ms_per_execution
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;
  2. 优化查询计划：使用索引提示或强制计划优化查询
  3. 分解复杂查询：将复杂查询拆分为多个简单查询

事务和锁监控指标 ​

SQL Server: Locks: Lock Waits/sec ​

指标说明：每秒锁等待次数，反映锁争用程度，高值表示系统存在严重的锁争用。

正常范围：< 10 为正常，超过 50 表示锁争用严重。

监控工具：

  • 性能监视器：SQL Server: Locks 计数器组
  • DMV：sys.dm_tran_locks、sys.dm_os_wait_stats

版本差异：

  • 所有版本均支持
  • SQLServer 2008 及以后：增加了更多锁相关的 DMVs

生产场景优化建议：

  1. 监控锁等待类型：
    sql
    SELECT TOP 10
    wait_type,
    wait_time_ms / 1000 AS total_wait_time_seconds,
    waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'LCK%'
ORDER BY wait_time_ms DESC;
  2. 优化锁持有时间：减少事务持有锁的时间，使用更细粒度的锁
  3. 使用行版本控制：启用 READ_COMMITTED_SNAPSHOT 隔离级别，减少读锁争用

SQL Server: Locks: Average Wait Time (ms) ​

指标说明：锁等待的平均时间（毫秒），反映锁争用的严重程度。

正常范围：< 50 ms 为正常，超过 200 ms 表示锁争用严重。

监控工具：

  • 性能监视器：SQL Server: Locks 计数器组

版本差异：

  • 所有版本均支持

生产场景优化建议：

  1. 识别锁争用对象：
    sql
    SELECT 
    OBJECT_NAME(p.object_id) AS object_name,
    request_mode,
    COUNT(*) AS lock_count
FROM sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
WHERE resource_type = 'OBJECT'
GROUP BY p.object_id, request_mode
ORDER BY lock_count DESC;
  2. 优化索引设计：使用合适的索引减少锁的范围
  3. 调整事务隔离级别：根据业务需求选择合适的隔离级别

监控工具和最佳实践 ​

常用监控工具 ​

  1. 性能监视器 (PerfMon)：Windows 内置的系统监控工具，可监控各种性能计数器，支持实时监控和历史数据收集
  2. SQL Server Management Studio (SSMS)：
    • Activity Monitor：实时监控 SQL Server 活动，包括进程、等待、锁和 I/O
    • Query Store：监控和分析查询性能，支持查看执行计划变化
    • Extended Events：轻量级事件捕获机制，性能影响小，适合生产环境
  3. Dynamic Management Views (DMVs)：使用 T-SQL 查询监控系统状态，灵活且实时
  4. 第三方监控工具：如 SolarWinds Database Performance Monitor、Redgate SQL Monitor、Datadog 等，提供更全面的监控和告警功能

监控最佳实践 ​

  1. 建立基准值：在系统正常运行时建立各项指标的基准值，便于识别异常
  2. 持续监控：实现 24/7 持续监控，及时发现问题
  3. 设置合理的告警阈值：根据基准值设置告警，避免过多误告警
  4. 综合分析：结合多个指标进行综合分析，避免单一指标误判
  5. 自动化响应：对于常见问题，实现自动化响应和修复
  6. 历史数据保留：保留足够的历史监控数据，便于趋势分析和容量规划
  7. 定期性能报告：生成定期性能报告，跟踪系统性能变化

版本差异注意事项 ​

  • SQLServer 2005-2008 R2：主要依赖性能监视器和早期 DMVs，缺少 Query Store 和 Extended Events 等高级功能
  • SQLServer 2012-2014：引入了更多 DMVs 和 Extended Events，增强了监控能力
  • SQLServer 2016 及以后：引入了 Query Store、自适应查询处理等功能，监控和优化能力显著提升
  • SQLServer 2019 及以后：引入了智能查询处理、内存优化表增强等功能，进一步提升了监控和优化能力

常见问题 (FAQ) ​

如何建立有效的监控基准值？ ​

答：建立监控基准值的步骤：

  1. 选择正常时间段：在业务稳定的时间段（如连续 2-4 周）收集数据
  2. 收集关键指标：重点收集 CPU、内存、磁盘 I/O、网络等核心指标
  3. 计算统计值：计算各项指标的平均值、最大值、最小值和标准差
  4. 确定正常范围：基于统计值和业务需求，确定各项指标的正常范围
  5. 定期更新：每季度或业务发生重大变化时更新基准值

如何避免监控工具对系统性能的影响？ ​

答：减少监控对系统性能影响的方法：

  1. 选择轻量级工具：优先使用 DMVs、Extended Events 等轻量级工具，避免在生产环境使用 SQL Server Profiler
  2. 合理设置采样间隔：根据监控需求调整采样频率，避免过于频繁的采样
  3. 仅监控必要指标：只监控与业务相关的关键指标，避免监控过多无关指标
  4. 使用专用监控服务器：将监控数据存储在专用服务器上，减少对生产服务器的影响
  5. 定期清理监控数据：及时清理过期的监控数据，避免占用过多存储空间

如何快速定位性能问题？ ​

答：快速定位性能问题的步骤：

  1. 检查系统资源：先检查 CPU、内存、磁盘 I/O 和网络等基础资源
  2. 分析等待统计：使用 sys.dm_os_wait_stats 找出主要的等待类型
  3. 识别瓶颈查询：使用 DMVs 或 Query Store 找出消耗资源最多的查询
  4. 查看执行计划：分析瓶颈查询的执行计划，找出优化点
  5. 检查配置：确认 SQL Server 和操作系统配置是否合理

如何设置合理的告警阈值？ ​

答：设置合理告警阈值的建议：

  1. 基于基准值：通常设置为平均值 + 2-3 倍标准差
  2. 考虑业务影响：关键业务系统可设置更严格的阈值
  3. 分层告警：设置警告和严重两个级别的告警
  4. 避免告警风暴：设置合理的告警间隔，避免同一问题频繁告警
  5. 定期调整：根据系统变化和业务需求定期调整告警阈值

云环境中如何监控 SQL Server？ ​

答：云环境中 SQL Server 监控方法：

  1. Azure SQL Database：使用 Azure Monitor、Azure SQL Analytics、SQL Insights
  2. AWS RDS for SQL Server：使用 Amazon CloudWatch、Enhanced Monitoring
  3. Google Cloud SQL for SQL Server：使用 Cloud Monitoring、Cloud Logging
  4. 混合监控：结合传统监控工具和云平台提供的监控服务
  5. 云原生监控：使用云平台提供的专用监控解决方案，如 Azure Monitor for SQL

如何监控 Always On 可用性组？ ​

答：Always On 可用性组监控要点：

  1. 可用性组状态：使用 sys.dm_hadr_availability_group_states 监控可用性组状态
  2. 副本同步状态：使用 sys.dm_hadr_database_replica_states 监控数据库同步状态
  3. 性能指标：监控主副本和辅助副本的 CPU、内存、I/O 等指标
  4. 故障转移事件：使用 Extended Events 或日志监控故障转移事件
  5. 网络延迟：监控副本之间的网络延迟

总结 ​

SQL Server 监控是数据库运维的核心工作之一，通过合理的监控策略和工具，可以及时发现和解决问题，优化系统性能，确保系统的稳定性和可用性。在监控过程中，需要结合多种监控工具和指标，建立基准值，设置合理的告警阈值，并定期分析监控数据。

不同版本的 SQL Server 提供了不同的监控功能，DBA 需要根据实际版本选择合适的监控方法。同时，监控是一个持续的过程，需要定期更新基准值和优化监控策略，适应业务变化和系统演进。

通过有效的监控，可以将被动的故障处理转变为主动的故障预防，提高数据库系统的可靠性和性能，为业务提供稳定的支持。