第十期：性能监控与调优方法论 —— 从等待统计到DMV分析

### 第十期：性能监控与调优方法论 —— 从等待统计到DMV分析

#### 1. 性能优化的核心思维：基于证据，而非直觉

**三个关键转变**：

| 从 | 到 |

|----|----|

| “我觉得这里慢” | “等待统计显示这里花了X%时间” |

| “重启一下试试” | “定位具体瓶颈类型（CPU/IO/网络/锁）” |

| “加个索引吧” | “分析执行计划+等待类型后再决定” |

**核心方法论**：**Waits and Queues**（等待与队列）

- SQL Server 的性能问题最终都会表现为**等待**（某个资源不可用）

- 找出TOP等待类型 → 定位瓶颈资源 → 针对性优化

#### 2. 等待类型分类与解读

**等待类型三大类**（按可优化性排序）：

| 类别 | 定义 | 典型等待 | 处理方向 |

|------|------|----------|----------|

| **资源等待** | 等待CPU、内存、I/O等硬件资源 | WRITELOG, PAGEIOLATCH\_\*, SOS\_SCHEDULER\_YIELD | 升级硬件或优化使用方式 |

| **锁等待** | 等待锁释放 | LCK\_M\_\* | 优化查询、索引、隔离级别 |

| **外部等待** | 等待外部应用或网络 | OLEDB, BROKER\_\*, PREEMPTIVE\_\* | 检查外部依赖 |

**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 | <80% | >90% 持续 |

| 内存 | Page Life Expectancy, Buffer Cache Hit Ratio | >300秒, >95% | <60秒, <90% |

| 磁盘I/O | Avg Disk Sec/Read, Avg Disk Sec/Write | <10ms | >50ms |

| 网络 | Network Interface Bytes/sec | 稳定 | 持续满载 |

| 锁阻塞 | Average Wait Time (ms) | <100ms | >500ms |

\-- 内存压力诊断

SELECT 

    \[name] AS counter\_name,

    CASE \[counter\_name]

        WHEN 'Page life expectancy' THEN 

&#x20;           CASE WHEN cntr\_value < 300 THEN '临界' WHEN cntr\_value < 1000 THEN '警告' ELSE '正常' END

&#x20;       WHEN 'Buffer cache hit ratio' THEN 

&#x20;           CASE WHEN cntr\_value < 90 THEN '临界' WHEN cntr\_value < 95 THEN '警告' ELSE '正常' END

&#x20;   END AS status,

&#x20;   cntr\_value

FROM sys.dm\_os\_performance\_counters

WHERE \[object\_name] LIKE '%Buffer Manager%'

&#x20; 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',

&#x20;   CASE WHEN cntr\_value < 300 THEN 'HIGH' ELSE 'NORMAL' END,

&#x20;   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点报表慢，其他时间正常。

排查过程：

1. 查等待统计：PAGEIOLATCH\_SH 占70%

2. 定位查询：大表扫描 + 书签查找

3. 原因：统计信息每天凌晨更新，但查询模式改变

4. 解决方案：添加覆盖索引，消除书签查找

5. 验证：执行时间从2分钟降到3秒

**案例2：偶发死锁**

现象：每月几次死锁错误（1205），重试后成功。

排查过程：

1. 启用TF1222捕获死锁图

2. 分析死锁图：两个事务交叉更新同一表的不同行

3. 原因：非聚集索引书签查找导致锁顺序不一致

4. 解决方案：统一更新顺序（ORDER BY），添加覆盖索引

5. 验证：监控一个月，死锁消失

**案例3：CPU持续99%**

现象：CPU长时间跑满，响应变慢。

排查过程：

1. 查等待统计：SOS\_SCHEDULER\_YIELD 占60%

2. 找高CPU查询：多个查询存在隐式转换

3. 原因：WHERE VARCHAR列 = 数字，强制全表扫描

4. 解决方案：统一参数类型，修改索引

5. 验证：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数据库架构等。