第十期：性能监控与调优方法论 —— 从等待统计到DMV分析
1. 性能优化的核心思维：基于证据，而非直觉
三个关键转变：
| 从 | 到 |
|—-|—-|
| “我觉得这里慢” | “等待统计显示这里花了X%时间” |
| “重启一下试试” | “定位具体瓶颈类型（CPU/IO/网络/锁）” |
| “加个索引吧” | “分析执行计划+等待类型后再决定” |
核心方法论：Waits and Queues（等待与队列）
-
SQL Server 的性能问题最终都会表现为等待（某个资源不可用）
-
找出TOP等待类型 → 定位瓶颈资源 → 针对性优化
2. 等待类型分类与解读
等待类型三大类（按可优化性排序）：
| 类别 | 定义 | 典型等待 | 处理方向 |
|——|——|———-|———-|
| 资源等待 | 等待CPU、内存、I/O等硬件资源 | WRITELOG, PAGEIOLATCH_*, SOS_SCHEDULER_YIELD | 升级硬件或优化使用方式 |
| 锁等待 | 等待锁释放 | LCK<em>M</em>* | 优化查询、索引、隔离级别 |
| 外部等待 | 等待外部应用或网络 | OLEDB, BROKER<em>*, PREEMPTIVE</em>* | 检查外部依赖 |
TOP 10 常见等待类型速查：
| 等待类型 | 含义 | 最常见原因 | 针对性排查 |
|———-|——|————|————|
| PAGEIOLATCH_SH | 等待从磁盘读页到内存 | 内存不足，或缺失索引导致大量物理读 | 检查缓冲池命中率，检查缺失索引 |
| WRITELOG | 等待日志写入磁盘 | 事务提交频繁，日志磁盘慢 | 批量提交，日志盘换SSD，检查磁盘队列 |
| LCK_M_X | 等待排他锁 | 长事务、锁升级、并发写冲突 | 查sys.dm_tran_locks，优化长事务 |
| SOS_SCHEDULER_YIELD | 自愿让出CPU（等待调度）| CPU压力大，查询未优化 | 检查CPU使用率，优化高代价查询 |
| ASYNC_NETWORK_IO | 等待客户端接收数据 | 客户端处理慢，网络慢 | 检查客户端应用，网络带宽 |
| PAGELATCH_EX | 等待页闩锁（内存结构）| tempdb竞争（PFS/GAM/SGAM页）| tempdb增加文件，使用分区表 |
| CXPACKET | 等待并行查询同步 | 并行查询的不均衡分布 | 调整MAXDOP，优化倾斜数据分布 |
| BACKUPTHREAD | 等待备份完成 | 备份任务占用资源 | 维护窗口做备份，调整备份压缩 |
| LOGBUFFER | 等待日志缓冲区空间 | 日志生成过快，磁盘跟不上 | 增大日志文件，优化大事务 |
| MEMORY_ALLOCATION_EXT | 等待内存分配 | 内存压力，大查询需要内存授予 | 调整max server memory，优化大排序/哈希 |
查询当前等待统计：
-- 查看自上次重启以来的等待累积
SELECT wait_type, waiting_tasks_count, wait_time_ms,
  max_wait_time_ms, signal_wait_time_ms,
  (wait_time_ms - signal_wait_time_ms) AS resource_wait_ms,
  CAST(wait_time_ms * 1.0 / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' -- 过滤无意义等待
  AND wait_type NOT LIKE '%BROKER%'
  AND wait_type NOT LIKE '%QUEUE%'
  AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;
现象解释：
-
为什么重启后性能短暂变好？
sys.dm_os_wait_stats被重置，但根本问题依然存在。 -
为什么
CXPACKET等待在并行度高的服务器上正常？少量CXPACKET正常，超过15%表示并行不平衡。
3. 系统化优化流程：5步法
步骤1：定位瓶颈（等待统计分析）
  ↓
步骤2：关联到具体资源（CPU/内存/IO/网络）
  ↓
步骤3：找到消耗资源最多的查询
  ↓
步骤4：分析查询执行计划
  ↓
步骤5：实施优化并验证效果
步骤1：定位瓶颈 —— 等待统计分析
-- 快速诊断当前瓶颈类型
SELECT
  CASE
  WHEN SUM(wait_time_ms) > 0 THEN
  (SELECT TOP 1 wait_type
  FROM sys.dm_os_wait_stats
  WHERE wait_type NOT LIKE '%SLEEP%'
  AND wait_type NOT LIKE '%BROKER%'
  ORDER BY wait_time_ms DESC)
  ELSE 'No significant waits'
  END AS top_wait_type,
  (SELECT SUM(wait_time_ms) FROM sys.dm_os_wait_stats
  WHERE wait_type NOT LIKE '%SLEEP%') AS total_wait_ms;
瓶颈分类决策树：
TOP等待类型分析
├─ PAGEIOLATCH_* 主导 (>40%)
│ └─ 内存不足 或 I/O子系统慢 → 检查PLE、磁盘延迟
├─ WRITELOG 主导 (>30%)
│ └─ 日志写入瓶颈 → 检查事务大小、日志盘IOPS
├─ SOS_SCHEDULER_YIELD 主导 (>20%)
│ └─ CPU压力 → 检查CPU使用率、高查询
├─ LCK_M_* 主导 (>20%)
│ └─ 锁阻塞 → 检查孤岛事务、死锁
├─ LATCH_* 主导 (>15%)
│ └─ 内部竞争 → 检查tempdb、数据库页争用
└─ 无明显单一主导（分散）
  └─ 混合负载正常，或整体压力大
步骤2：关联到具体资源指标
| 瓶颈类型 | 检查计数器 | 正常值 | 异常值 |
|———-|————|——–|——–|
| CPU | Processor Time, SQL Compilations/sec | 90% 持续 |
| 内存 | Page Life Expectancy, Buffer Cache Hit Ratio | >300秒, >95% | <60秒, <90% |
| 磁盘I/O | Avg Disk Sec/Read, Avg Disk Sec/Write | 50ms |
| 网络 | Network Interface Bytes/sec | 稳定 | 持续满载 |
| 锁阻塞 | Average Wait Time (ms) | 500ms |
-- 内存压力诊断
SELECT
  [name] AS counter_name,
  CASE [counter_name]
  WHEN 'Page life expectancy' THEN
  CASE WHEN cntr_value < 300 THEN '临界' WHEN cntr_value < 1000 THEN '警告' ELSE '正常' END
  WHEN 'Buffer cache hit ratio' THEN
  CASE WHEN cntr_value < 90 THEN '临界' WHEN cntr_value < 95 THEN '警告' ELSE '正常' END
  END AS status,
  cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
  AND counter_name IN ('Page life expectancy', 'Buffer cache hit ratio');
步骤3：找到消耗资源最多的查询
-- 当前正在运行的查询（按CPU/IO排序）
SELECT TOP 10
  r.session_id, r.cpu_time, r.total_elapsed_time,
  r.logical_reads, r.writes, s.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.session_id > 50 -- 排除系统会话
ORDER BY r.cpu_time DESC; -- 或 logical_reads, writes
-- 历史消耗统计（自上次重启）
SELECT TOP 10
  qs.total_worker_time / qs.execution_count AS avg_cpu,
  qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
  qs.execution_count, qs.total_elapsed_time,
  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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC; -- 或 logical_reads, elapsed_time
步骤4：分析执行计划
快速检查清单：
-
[ ] 是否有表扫描？预期行数大吗？
-
[ ] 估计行数 vs 实际行数差异 >10倍？
-
[ ] 是否有书签查找（Key Lookup）且占比高？
-
[ ] 是否有隐式转换（CONVERT_IMPLICIT）？
-
[ ] 连接方式合理吗（小表驱动大表）？
-
[ ] 是否有排序操作且无索引支持？
步骤5：实施优化并验证
优化类型优先级：
1. 查询重写（代价最低，效果可能最大）
  - SARGable改造
  - 消除隐式转换
  - 拆分复杂查询
2. 索引优化（性价比最高）
  - 添加覆盖索引
  - 调整索引列顺序
  - 删除无用索引
3. 配置调整（需谨慎）
  - MAXDOP, Cost Threshold for Parallelism
  - Max Server Memory
  - 隔离级别（RCSI）
4. 硬件升级（最后手段）
  - 增加内存
  - 换成SSD
  - 增加CPU核心
4. 关键性能计数器详解
CPU相关：
| 计数器 | 意义 | 告警阈值 |
|——–|——|———-|
| Processor Time | CPU总体使用率 | >80% |
| SQL Compilations/sec | 编译次数（每秒） | >100 |
| SQL Recompilations/sec | 重编译次数 | >10 |
| Batch Requests/sec | 批请求数（吞吐量） | 趋势监控，突然下降可能问题 |
内存相关：
| 计数器 | 意义 | 告警阈值 |
|——–|——|———-|
| Page Life Expectancy | 页在内存停留秒数 | <300秒 |
| Buffer Cache Hit Ratio | 缓冲池命中率 | <95% |
| Memory Grants Pending | 等待内存授予的查询数 | >0 |
| Target Server Memory vs Total Server Memory | 预期 vs 实际内存 | 实际 < 目标 持续 |
磁盘I/O相关：
| 计数器 | 意义 | 告警阈值 |
|——–|——|———-|
| Avg Disk sec/Read | 平均读延迟 | >20ms（数据），>5ms（日志） |
| Avg Disk sec/Write | 平均写延迟 | >20ms（数据），>5ms（日志） |
| Disk Reads/sec | 每秒读次数 | 与硬件能力对比 |
| Disk Writes/sec | 每秒写次数 | 与硬件能力对比 |
监控脚本（快速健康检查）：
-- 一键诊断（运行以上关键指标）
SELECT 'CPU Pressure' AS metric,
  CASE WHEN cntr_value > 80 THEN 'HIGH' ELSE 'NORMAL' END AS status,
  cntr_value AS value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Processor Time' AND instance_name = '_Total'
UNION ALL
SELECT 'Memory Pressure',
  CASE WHEN cntr_value < 300 THEN 'HIGH' ELSE 'NORMAL' END,
  cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
5. 常用DMV速查表
| 目的 | DMV/函数 |
|——|———-|
| 等待统计 | sys.dm_os_wait_stats |
| 当前请求 | sys.dm_exec_requests |
| 当前会话 | sys.dm_exec_sessions |
| 锁状态 | sys.dm_tran_locks |
| 查询统计（历史）| sys.dm_exec_query_stats |
| 查询计划 | sys.dm_exec_query_plan() |
| 索引使用 | sys.dm_db_index_usage_stats |
| 缺失索引 | sys.dm_db_missing_index_details |
| 计划缓存 | sys.dm_exec_cached_plans |
| 性能计数器 | sys.dm_os_performance_counters |
| 操作系统 | sys.dm_os_sys_info, sys.dm_os_windows_info |
6. 性能优化实战案例
案例1：突然变慢的报表查询
现象：每天早上8-10点报表慢，其他时间正常。
排查过程：
-
查等待统计：
PAGEIOLATCH_SH占70% -
定位查询：大表扫描 + 书签查找
-
原因：统计信息每天凌晨更新，但查询模式改变
-
解决方案：添加覆盖索引，消除书签查找
-
验证：执行时间从2分钟降到3秒
案例2：偶发死锁
现象：每月几次死锁错误（1205），重试后成功。
排查过程：
-
启用TF1222捕获死锁图
-
分析死锁图：两个事务交叉更新同一表的不同行
-
原因：非聚集索引书签查找导致锁顺序不一致
-
解决方案：统一更新顺序（ORDER BY），添加覆盖索引
-
验证：监控一个月，死锁消失
案例3：CPU持续99%
现象：CPU长时间跑满，响应变慢。
排查过程：
-
查等待统计：
SOS_SCHEDULER_YIELD占60% -
找高CPU查询：多个查询存在隐式转换
-
原因：WHERE VARCHAR列 = 数字，强制全表扫描
-
解决方案：统一参数类型，修改索引
-
验证：CPU降至40%，业务恢复正常
7. 性能优化的常见陷阱
| 陷阱 | 错误做法 | 正确做法 |
|——|———-|———-|
| 过早优化 | 随便加索引，不管写入影响 | 先分析瓶颈，确认收益>成本 |
| 只看平均 | 平均延迟正常就认为没问题 | 关注P95、P99峰值 |
| 忽视基线 | 不知道正常时什么样 | 建立性能基线，对比异常 |
| 一次改太多 | 同时改索引、查询、配置 | 逐一改动，验证效果 |
| 生产直调 | 直接在生产调参 | 仿真环境验证完整流程 |
| 只看SQL Server | 忽略OS、网络、存储 | 全链路排查 |
8. 建立性能基线
基线内容：
-
业务负载特征：每秒批请求数、并发会话数
-
等待类型分布：TOP5等待占比
-
资源使用：CPU、内存、磁盘I/O平均值和峰值
-
关键查询：TOP10 CPU/IO查询的平均执行时间
-
业务指标：核心事务响应时间的P50/P95/P99
工具推荐：
-
内置报表：SQL Server 标准报表（右键数据库 → 报表）
-
Performance Dashboard：SQL Server 官方DMV仪表盘
-
sp_Blitz：Brent Ozar 开源诊断工具
-
sp_WhoIsActive：实时监控当前活动（Adam Machanic）
-- 安装 sp_WhoIsActive（推荐）
-- 下载地址：http://whoisactive.com
EXEC sp_WhoIsActive @get_plans = 1, @get_locks = 1;
9. 优化前中后的检查清单
优化前：
-
[ ] 收集当前性能指标（等待统计、计数器、基线）
-
[ ] 确认问题范围（全局/特定库/特定查询）
-
[ ] 找到最可能的瓶颈类型
-
[ ] 设计优化方案（含回滚计划）
-
[ ] 评估优化风险（锁、停机时间）
优化中：
-
[ ] 在非生产环境验证
-
[ ] 监控优化过程中的关键指标
-
[ ] 记录改动前后对比
-
[ ] 准备回滚脚本
优化后：
-
[ ] 验证业务指标改善
-
[ ] 监控副作用（其他查询是否变慢）
-
[ ] 更新文档和基线
-
[ ] 通知团队优化结果
第十期小结
性能优化不是玄学，而是一套可复用的方法论：等待统计定位瓶颈 → 资源指标确认 → 找到消耗查询 → 分析执行计划 → 实施验证。关键工具是sys.dm_os_wait_stats、sys.dm_exec_query_stats和性能计数器。建立基线和遵循优化流程，能让你从“碰运气”变成“有据可依”。记住：性能优化的第一原则是度量，而不是猜测。
整个系列总结
这十期内容覆盖了SQL Server基础架构的核心知识：
| 期数 | 主题 | 核心要点 |
|——|——|———-|
| 第一期 | 整体架构 | 实例、数据库、文件组、执行引擎vs存储引擎 |
| 第二期 | 缓冲池与内存 | Page Life Expectancy、Checkpoint、LazyWriter |
| 第三期 | 事务日志与WAL | VLF、WAL、崩溃恢复（分析+重做+撤消） |
| 第四期 | 索引物理存储 | 堆、聚集/非聚集索引、覆盖索引、书签查找 |
| 第五期 | 并发控制（上） | 隔离级别、锁类型、锁升级、阻塞分析 |
| 第六期 | 并发控制（下） | 死锁检测、死锁分析、消除策略 |
| 第七期 | 查询优化器 | 执行计划、统计信息、参数嗅探 |
| 第八期 | 事务内部实现 | LSN、行版本控制、ARIES恢复 |
| 第九期 | 高可用与DR | 日志传送、复制、Always On AG |
| 第十期 | 性能调优方法论 | 等待统计、DMV分析、系统化流程 |
这十期构成了SQL Server基础架构的知识骨架，帮助你理解现象本质、定位问题根源、制定优化方案。后续可深入学习的方向包括：查询优化器高级话题、分区表与列存储、内存优化表、Azure SQL数据库架构等。
