2. SQL Server生产环境监控指标体系

生产环境的SQL Server监控，最忌讳“什么都看、什么都报警”。一个可运维的监控体系，必须先把监控对象分层归类，再为每一类对象选定少量高信噪比的指标，最后明确每个指标的采集方式、正常基线与异常判据。本节围绕五类核心监控对象展开：关键性能计数器、等待统计、活动会话、阻塞与死锁、数据库文件与日志空间。它们分别回答“系统忙不忙”“时间花在哪”“谁在跑”“谁挡住了谁”“空间够不够”这五个运维最常追问的问题。

一、关键性能计数器：先建立资源基线

性能计数器反映实例级和资源级的整体负载，适合做趋势基线和容量判断。生产上建议优先采集以下对象，而不是把几百个计数器全部打开：

• CPU：Processor(_Total)\% Processor Time、SQLServer:SQL Statistics\Batch Requests/sec、SQLServer:SQL Statistics\SQL Compilations/sec 与 SQL Re-Compilations/sec。批请求量配合CPU使用率可判断单请求成本；编译与重编译突增往往指向计划缓存抖动或参数化问题。
• 内存：SQLServer:Buffer Manager\Page life expectancy（PLE）、Buffer cache hit ratio、SQLServer:Memory Manager\Total Server Memory (KB) 与 Target Server Memory (KB)。PLE持续低于300秒通常提示缓冲池被频繁冲刷，但需结合内存压力与查询模式判断，不能单指标下结论。
• I/O：SQLServer:Buffer Manager\Page reads/sec、Page writes/sec，以及 PhysicalDisk(_Total)\Avg. Disk sec/Read 与 Avg. Disk sec/Write。数据文件延迟经验阈值：小于10ms良好，10~20ms需关注，持续大于20ms应排查存储或查询。
• TempDB与并发：SQLServer:Transactions\Free Space in tempdb (KB)、SQLServer:General Statistics\User Connections、Processes blocked。

采集方式上，短周期（如每15秒）用 sys.dm_os_performance_counters 落库，长周期用性能监视器或扩展事件补充。关键是先记录一周基线，再设定阈值，例如“PLE低于300秒且持续5分钟”才告警，避免瞬时抖动造成误报。

二、等待统计：定位时间去向

等待统计回答“SQL Server在等什么”。核心视图是 sys.dm_os_wait_stats，它按等待类型累计等待时间与次数。生产上应关注累计等待时间占比最高的几类，并结合 wait_time_ms / waiting_tasks_count 计算平均等待时长。

常见等待类型与含义：CXPACKET 多与并行查询协调有关；PAGEIOLATCH_SH/EX 指向数据页读取I/O瓶颈；LCK_M_* 表示锁等待；PAGELATCH_* 常与TempDB或热页争用相关；RESOURCE_SEMAPHORE 表示内存授予等待；WRITELOG 指向日志写入延迟。判断时要注意：等待统计是自实例启动以来的累计值，必须用两次采样差值计算区间增量，否则历史数据会掩盖当前问题。

-- 示例：计算两次采样间的等待增量（需先建快照表）
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       wait_time_ms - signal_wait_time_ms AS resource_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP',
      'SQLTRACE_BUFFER_FLUSH','BROKER_TASK_STOP','CHECKPOINT_QUEUE');

示例判据：若某区间内 PAGEIOLATCH_SH 的 resource_wait_ms 占比超过总等待的40%，且平均等待超过20ms，应优先检查存储延迟与缺失索引，而不是直接加内存。

三、活动会话与阻塞、死锁

活动会话回答“当前谁在跑、跑什么、跑了多久”。核心视图是 sys.dm_exec_requests 关联 sys.dm_exec_sessions 与 sys.dm_exec_sql_text。重点字段包括 session_id、status、command、cpu_time、total_elapsed_time、wait_type、blocking_session_id 和 sql_text。

阻塞排查的关键是识别阻塞链：从 blocking_session_id 出发，逐级向上找到根阻塞会话。若根会话长时间处于 sleeping 且持有锁，通常是应用未提交事务或连接池泄漏。死锁则通过 system_health 扩展事件会话或 xml_deadlock_report 捕获，重点看两个事务各自持有的资源和请求的资源，以及是否因缺少索引导致扫描范围过大。

-- 示例：查看当前阻塞链
SELECT r.session_id, r.blocking_session_id, r.wait_type,
       r.wait_time, r.status, t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

四、数据库文件与日志空间

空间监控要区分数据文件与日志文件。数据文件关注 size、used_percent、自动增长设置与增长次数；日志文件关注 log_space_in_use、log_reuse_wait_desc 以及虚拟日志文件数量。核心视图是 sys.dm_db_file_space_usage、sys.dm_db_log_space_usage 和 sys.dm_db_log_info。

日志空间告警不能只看“用了多少”，更要看 log_reuse_wait_desc：若为 LOG_BACKUP，说明日志备份未及时执行；若为 ACTIVE_TRANSACTION，说明有长事务未提交；若为 REPLICATION 或 AVAILABILITY_REPLICA，则与复制或可用性组同步有关。数据文件则应设置固定增长量（如256MB）而非百分比增长，并监控剩余空间低于15%时告警。

五、指标采集与告警的落地要点

把上述五类对象落到生产，需要解决三个工程问题。第一是采集频率与开销：DMV查询本身消耗资源，短周期采集应限制返回行数并避免在高峰期执行全量扫描；性能计数器可用 sys.dm_os_performance_counters 直接读取，但部分计数器需要两次采样计算差值。第二是基线管理：同一指标在不同业务系统上的正常值差异很大，例如OLTP系统的PLE通常远低于报表系统，因此阈值必须按实例或按数据库分组配置。第三是告警收敛：把“CPU高”“等待高”“阻塞多”合并为一条根因线索，而不是分别触发三条告警。

综上，生产监控指标体系的建设顺序应是：先确定五类核心对象，再为每类对象选定3~5个高价值指标，然后建立基线、阈值和告警收敛规则，最后通过活动会话与阻塞链把“资源异常”追溯到“具体语句与事务”。这样，监控才不只是看板，而是可执行的排障入口。