外观
SQLServer 核心监控指标清单
在SQLServer数据库运维中，监控是确保数据库稳定运行的重要环节。通过监控关键指标，可以及时发现并解决性能问题，预防故障发生。本文将详细介绍SQLServer的核心监控指标，帮助DBA建立完善的监控体系。
监控指标分类
SQLServer的监控指标可以分为以下几类：
- 资源利用率指标：CPU、内存、磁盘I/O、网络等
- 数据库性能指标：查询响应时间、吞吐量、并发连接数等
- 数据库状态指标：数据库可用性、备份状态、索引碎片等
- 错误和告警指标：错误日志、异常事件、告警信息等
资源利用率指标
1. CPU利用率
监控指标：
- 总体CPU利用率：服务器总体CPU使用率
- SQLServer进程CPU利用率：SQLServer进程占用的CPU百分比
- CPU就绪队列长度：CPU等待执行的线程数
- 上下文切换率：每秒上下文切换次数
监控方法：
- Windows性能监视器：Processor(_Total)% Processor Time
- SQLServer动态管理视图：sys.dm_os_ring_buffers, sys.dm_os_performance_counters
- T-SQL查询示例：sql
-- 查询SQLServer进程CPU使用率 SELECT (total_worker_time - start_time_total_worker_time) / 1000000.0 AS cpu_usage_seconds FROM (SELECT SUM(ws.total_worker_time) AS total_worker_time, MIN(ws.start_time_total_worker_time) AS start_time_total_worker_time FROM (SELECT SUM(worker_time) OVER () AS total_worker_time, MIN(start_time) OVER () AS start_time_total_worker_time FROM sys.dm_exec_requests WHERE session_id > 50) AS ws) AS t;
阈值建议：
- 总体CPU利用率：持续超过80%需要关注
- SQLServer进程CPU利用率：持续超过70%需要优化
- CPU就绪队列长度：超过CPU核心数的2倍需要关注
2. 内存利用率
监控指标：
- SQLServer内存使用量：SQLServer进程占用的内存
- Buffer Pool命中率：数据页在内存中的命中率
- 页寿命(Page Life Expectancy)：数据页在Buffer Pool中停留的平均时间
- 内存授予等待(Memory Grant Pending)：等待内存授予的请求数
- 免费内存：服务器剩余可用内存
监控方法：
- Windows性能监视器：Process(sqlservr)\Working Set
- SQLServer动态管理视图：sys.dm_os_performance_counters, sys.dm_os_memory_clerks
- T-SQL查询示例：sql
-- 查询Buffer Pool命中率和页寿命 SELECT (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio') * 1.0 / (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base') * 100 AS buffer_cache_hit_ratio_percent, (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy') AS page_life_expectancy;
阈值建议：
- SQLServer内存使用量：根据服务器配置和工作负载调整
- Buffer Pool命中率：应高于95%
- 页寿命：应高于300秒
- 内存授予等待：持续大于0需要关注
3. 磁盘I/O指标
监控指标：
- 磁盘读写次数(IOPS)：每秒读写操作数
- 磁盘吞吐量(MBps)：每秒读写数据量
- 平均磁盘读写延迟：磁盘I/O操作的平均响应时间
- 磁盘队列长度：等待磁盘I/O的请求数
- SQLServer等待类型：PAGEIOLATCH_*, WRITELOG等I/O相关等待
监控方法：
- Windows性能监视器：PhysicalDisk()\Avg. Disk sec/Read, PhysicalDisk()\Avg. Disk sec/Write
- SQLServer动态管理视图：sys.dm_io_virtual_file_stats, sys.dm_os_wait_stats
- T-SQL查询示例：sql
-- 查询数据库文件I/O延迟 SELECT DB_NAME(database_id) AS database_name, file_id, CASE WHEN file_id = 2 THEN 'Log' ELSE 'Data' END AS file_type, 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) WHERE num_of_reads > 0 OR num_of_writes > 0;
阈值建议：
- 平均磁盘读延迟：应低于10ms
- 平均磁盘写延迟：应低于20ms
- 磁盘队列长度：超过磁盘 spindles 数的2倍需要关注
- I/O相关等待：持续占总等待时间的30%以上需要优化
4. 网络指标
监控指标：
- 网络吞吐量：每秒发送和接收的数据量
- 网络等待时间：网络I/O等待时间
- 连接数：当前活跃连接数
- 连接错误数：每秒连接错误数
监控方法：
- Windows性能监视器：Network Interface(*)\Bytes Total/sec
- SQLServer动态管理视图：sys.dm_exec_connections, sys.dm_os_performance_counters
- T-SQL查询示例：sql
-- 查询当前活跃连接数 SELECT COUNT(*) AS active_connections FROM sys.dm_exec_sessions WHERE status = 'running' AND session_id > 50;
阈值建议：
- 网络吞吐量：根据网络带宽和业务需求调整
- 连接数：不超过SQLServer最大连接数的80%
- 连接错误数：持续大于0需要关注
数据库性能指标
1. 查询性能指标
监控指标：
- 每秒查询数(QPS)：每秒执行的查询次数
- 查询响应时间：查询的平均执行时间
- 慢查询数量：执行时间超过阈值的查询数
- 查询计划重用率：查询计划的重用比例
- 编译/重编译次数：每秒查询编译和重编译次数
监控方法：
- SQLServer动态管理视图：sys.dm_exec_query_stats, sys.dm_exec_procedure_stats
- Query Store：监控查询性能和执行计划
- T-SQL查询示例：sql
-- 查询前10个耗时最长的查询 SELECT TOP 10 query_hash, SUM(total_worker_time) / SUM(execution_count) AS avg_cpu_time, SUM(total_elapsed_time) / SUM(execution_count) AS avg_duration, SUM(execution_count) AS execution_count, MIN(statement_text) AS sample_statement FROM (SELECT qs.query_hash, qs.total_worker_time, qs.total_elapsed_time, qs.execution_count, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st) AS t GROUP BY query_hash ORDER BY avg_duration DESC;
阈值建议：
- 查询响应时间：根据业务需求调整，通常关键查询应低于1秒
- 慢查询数量：持续增加需要优化
- 查询计划重用率：应高于90%
- 编译/重编译次数：占总查询数的比例应低于5%
2. 事务指标
监控指标：
- 每秒事务数(TPS)：每秒执行的事务数量
- 事务响应时间：事务的平均执行时间
- 长事务数量：执行时间超过阈值的事务数
- 死锁数量：每秒死锁数
- 锁等待时间：锁等待的平均时间
监控方法：
- SQLServer动态管理视图：sys.dm_tran_active_transactions, sys.dm_os_performance_counters
- T-SQL查询示例：sql
-- 查询当前活跃事务 SELECT dt.transaction_id, DB_NAME(dt.database_id) AS database_name, dt.transaction_begin_time, DATEDIFF(second, dt.transaction_begin_time, GETDATE()) AS transaction_duration_seconds, dt.transaction_type, dt.transaction_state, des.session_id, des.login_name, des.host_name, des.program_name, dst.text AS transaction_text FROM sys.dm_tran_active_transactions AS dt JOIN sys.dm_tran_session_transactions AS dst ON dt.transaction_id = dst.transaction_id JOIN sys.dm_exec_sessions AS des ON dst.session_id = des.session_id LEFT JOIN sys.dm_exec_connections AS dec ON des.session_id = dec.session_id OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) AS dst;
阈值建议：
- 每秒事务数：根据业务需求调整
- 长事务数量：持续大于0需要关注
- 死锁数量：持续大于0需要解决
- 锁等待时间：平均超过100ms需要优化
3. 索引指标
监控指标：
- 索引使用率：索引被使用的频率
- 索引碎片率：索引碎片的百分比
- 缺失索引数量：SQLServer建议创建的索引数
- 索引扫描/查找比例：索引扫描和索引查找的比例
监控方法：
- SQLServer动态管理视图：sys.dm_db_index_usage_stats, sys.dm_db_index_physical_stats
- T-SQL查询示例：sql
-- 查询索引碎片率超过30%的索引 SELECT DB_NAME(database_id) AS database_name, OBJECT_NAME(object_id, database_id) AS table_name, i.name AS index_name, ips.avg_fragmentation_in_percent, ips.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id WHERE ips.avg_fragmentation_in_percent > 30 AND ips.page_count > 1000 ORDER BY ips.avg_fragmentation_in_percent DESC;
阈值建议：
- 索引碎片率：超过30%需要重建，10%-30%需要重组
- 缺失索引数量：持续增加需要评估是否创建
- 索引扫描/查找比例：索引扫描占比应低于20%
数据库状态指标
1. 数据库可用性
监控指标：
- 数据库状态：数据库是否在线、可疑、恢复中、离线等
- 可用性组状态：Always On可用性组的同步状态
- 复制状态：复制代理的运行状态
- 镜像状态：数据库镜像的同步状态
监控方法：
- SQLServer动态管理视图：sys.databases, sys.dm_hadr_availability_group_states
- T-SQL查询示例：sql
-- 查询数据库状态 SELECT name AS database_name, state_desc, recovery_model_desc, compatibility_level, collation_name FROM sys.databases;
阈值建议：
- 数据库状态：应为ONLINE
- 可用性组状态：同步副本应为SYNCHRONIZED
2. 备份状态
监控指标：
- 最后一次备份时间：全量、差异、事务日志备份的最后执行时间
- 备份成功率：备份作业的成功比例
- 备份大小：备份文件的大小
- 备份持续时间：备份作业的执行时间
监控方法：
- SQLServer动态管理视图：sys.dm_db_backup_history
- SQL Server代理：监控备份作业状态
- T-SQL查询示例：sql
-- 查询数据库最后一次备份时间 SELECT d.name AS database_name, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full_backup, MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff_backup, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log_backup FROM sys.databases AS d LEFT JOIN msdb.dbo.backupset AS b ON d.name = b.database_name GROUP BY d.name ORDER BY d.name;
阈值建议：
- 全量备份：根据业务需求，通常每天至少一次
- 差异备份：根据业务需求，通常每6-12小时一次
- 事务日志备份：根据业务需求，通常每15-60分钟一次
3. 事务日志指标
监控指标：
- 事务日志大小：日志文件的当前大小
- 事务日志使用率：日志文件的使用比例
- 日志增长次数：日志文件自动增长次数
- 日志截断状态：事务日志是否正常截断
- 日志等待时间：WRITELOG等待时间
监控方法：
- SQLServer动态管理视图：sys.dm_db_log_space_usage, sys.dm_os_performance_counters
- T-SQL查询示例：sql
-- 查询事务日志使用率 SELECT DB_NAME(database_id) AS database_name, (total_log_size_in_bytes - available_log_space_in_bytes) * 100.0 / total_log_size_in_bytes AS log_usage_percent, available_log_space_in_bytes / 1024 / 1024 AS available_log_space_mb, total_log_size_in_bytes / 1024 / 1024 AS total_log_size_mb FROM sys.dm_db_log_space_usage;
阈值建议：
- 事务日志使用率：持续超过80%需要关注
- 日志增长次数：持续大于0需要调整日志文件大小
- 日志截断状态：VLF数量不超过100个
错误和告警指标
1. 错误日志指标
监控指标：
- 错误数量：每秒错误数
- 严重错误数：严重级别>=16的错误数
- 致命错误数：严重级别>=20的错误数
- 警告数量：警告信息数量
监控方法：
- SQLServer错误日志：查看错误和警告信息
- sys.dm_os_ring_buffers：查看系统健康状态
- T-SQL查询示例：sql
-- 查询最近的错误日志条目 EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, 'DESC';
阈值建议：
- 严重错误数：持续大于0需要立即处理
- 致命错误数：任何时候出现都需要立即处理
2. 等待统计指标
监控指标：
- 总等待时间：所有等待类型的总等待时间
- 主要等待类型：占总等待时间比例最高的等待类型
- 等待队列长度：每种等待类型的队列长度
监控方法：
- SQLServer动态管理视图：sys.dm_os_wait_stats
- T-SQL查询示例：sql
-- 查询主要等待类型 SELECT TOP 10 wait_type, wait_time_ms, (wait_time_ms * 100.0) / SUM(wait_time_ms) OVER () AS wait_time_percent, waiting_tasks_count FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN') ORDER BY wait_time_ms DESC;
阈值建议：
- 主要等待类型：单个等待类型占比超过30%需要优化
- 等待队列长度：持续大于0需要关注
监控工具推荐
1. 原生监控工具
- SQL Server Management Studio (SSMS)：提供图形化监控界面
- SQL Server Profiler：捕获和分析SQLServer事件
- Database Engine Tuning Advisor：分析查询性能并提供优化建议
- Query Store：监控查询性能和执行计划
- Extended Events：轻量级事件监控系统
- Performance Monitor：Windows自带的性能监控工具
2. 第三方监控工具
- SolarWinds Database Performance Analyzer：全面的数据库性能监控工具
- PRTG Network Monitor：网络和系统监控工具，支持SQLServer监控
- Datadog：云原生监控平台，支持SQLServer监控
- New Relic：应用性能监控工具，支持SQLServer监控
- AppDynamics：应用性能监控工具，支持SQLServer监控
- Idera SQL diagnostic manager：专门针对SQLServer的监控工具
版本差异
| SQLServer版本 | 监控指标支持特性 |
|---|---|
| 2005-2008R2 | 支持基本的性能计数器和DMV，缺少Query Store和Extended Events |
| 2012-2014 | 新增Columnstore索引监控，增强了DMV功能 |
| 2016+ | 新增Query Store，增强了Extended Events，支持JSON格式输出 |
| 2019+ | 新增Intelligent Query Processing监控，增强了内存优化表监控 |
| Azure SQL DB | 内置监控和性能建议，支持自动优化 |
| Azure SQL 托管实例 | 支持本地监控工具和Azure监控服务 |
常见问题(FAQ)
Q1: 如何选择合适的监控指标？
A: 应根据业务需求、数据库规模和性能目标选择监控指标。建议从核心指标开始，逐步扩展到更详细的指标。重点监控与业务直接相关的指标，如查询响应时间、事务数和可用性。
Q2: 监控频率应该如何设置？
A: 监控频率应根据指标的变化频率和业务需求确定：
- 资源利用率指标：每1-5分钟采集一次
- 数据库性能指标：每5-15分钟采集一次
- 数据库状态指标：每15-30分钟采集一次
- 错误和告警指标：实时监控
Q3: 如何处理监控数据？
A: 监控数据应：
- 存储在专门的监控数据库中
- 设置合理的数据保留策略
- 定期分析趋势和异常
- 生成可视化报告
- 建立告警机制
Q4: 如何建立有效的告警机制？
A: 建立告警机制时应：
- 为关键指标设置合理的阈值
- 分级告警（警告、严重、紧急）
- 多种告警方式（电子邮件、短信、微信等）
- 明确告警负责人和处理流程
- 定期审查和调整告警规则
Q5: 监控会对数据库性能产生影响吗？
A: 监控会对数据库性能产生一定影响，但影响程度取决于：
- 监控工具的类型和配置
- 监控指标的数量和采集频率
- 数据库的规模和性能
合理配置监控工具和指标可以将影响降到最低。
Q6: 如何优化监控性能？
A: 可以通过以下方法优化监控性能：
- 只监控必要的指标
- 调整监控频率，避免过于频繁采集
- 使用轻量级监控工具（如Extended Events）
- 避免在生产环境使用性能影响较大的监控工具（如SQL Server Profiler）
- 将监控数据存储在单独的服务器上
总结
SQLServer核心监控指标是数据库运维的重要组成部分，通过监控这些指标，可以及时发现并解决性能问题，预防故障发生。本文介绍了资源利用率、数据库性能、数据库状态和错误告警四个方面的核心监控指标，包括监控方法、阈值建议和版本差异。
建立完善的监控体系需要：
- 选择合适的监控指标和工具
- 设置合理的监控频率和阈值
- 建立有效的告警机制
- 定期分析监控数据和趋势
- 根据业务需求和系统变化调整监控策略
通过持续监控和优化，可以确保SQLServer数据库的稳定运行，提高业务系统的可用性和性能。