8.2 常见性能瓶颈分析：三大瓶颈的指纹

8.2 常见性能瓶颈分析：三大瓶颈的指纹

本节摘要：CPU、内存、I/O 三大瓶颈各有指标指纹，也各有伪装术——CPU 高的根因可能是缺索引导致的过度编译，内存压力的元凶常是大扫描，I/O 慢背后可能是缓冲池被冲刷。本节给出三大瓶颈的识别指标与判读阈值，讲清它们之间的传导链，并用一桩"CPU 不忙却全慢"的误诊案收尾。目标是对着指标能读出病灶，而不是背阈值表。

CPU 不忙，系统却瘫痪：一桩误诊案

先上一桩反面教材。某系统午高峰全员卡顿，值班一看监控：CPU 使用率不到四成，内存充足——"资源没问题，可能是网络抖动"，工单结了。第二天同样时段再来，第三天升级成事故。复诊从等待统计切入：SOS_SCHEDULER_YIELD 累计等待排在第一。这个等待的含义是"任务在调度器上让出后重新排队"——它指向 CPU 争用，但特征是单核排队而非整机满载：几十个调度器里少数几个被并行扫描打爆，其余空闲，整机使用率自然不高。顺藤摸到计划缓存，元凶是一段带标量函数的查询——每行调用一次的自定义函数让一个五万行查询变成几百万次函数执行，全部压在少数调度器上。改写为内联表值函数后，午高峰延迟从十一秒降到零点四秒。这案子的教训入册为第一诊断戒律：整机使用率是体感指标，等待统计是病灶指标——两者矛盾时，信等待统计。

三大瓶颈的指纹卡

CPU 瓶颈的指纹：SOS_SCHEDULER_YIELD 与 CXPACKET（并行同步等待）居前；可运行任务数持续大于零；编译占比高（计划缓存里有海量单次执行条目）。常见根因排序：缺索引导致的大规模扫描（扫描也是 CPU 活）、标量函数与复杂表达式、过度并行、统计过期引发的编译风暴。判读要分清"忙得对"与"忙得错"——同样的 CPU 消耗，支撑三倍吞吐的忙是好忙。

内存瓶颈的指纹：页面预期寿命（PLE）骤降、缓冲池命中率趋势下滑、RESOURCE_SEMAPHORE（等内存授予）攀升、RESOURCE_SEMAPHORE_QUERY_COMPILE（编译内存紧张）。第 2 章讲过体征，这里补因果：内存压力的直接表现通常是 I/O 指标恶化（页被换出又被读回），所以看到 I/O 异常先排除内存冲刷，否则会在磁盘上白花预算。

I/O 瓶颈的指纹：PAGEIOLATCH 系列等待（读写数据页）或 WRITELOG（等日志落盘）居前；物理读延迟（计数器里的 Avg. Disk sec/Read）超过十毫秒即堪忧；日志盘延迟对同步高可用尤其致命（提交延迟直接放大）。判读三问：是磁盘真的慢（延迟高且队列长），还是缓冲池失效制造了假性 I/O 压力（PLE 同步恶化），还是日志盘与数据盘混布互相踩踏。

图 8-2 三大瓶颈的传导链：表象在末梢，起点在源头

传导链：瓶颈从来不是孤立的

三大瓶颈像连通器，压力会传导。一条典型链：统计过期让优化器选了全表扫描（第 4 章的估错链）→ 扫描把缓冲池热页冲出（内存压力，PLE 跌）→ 后续查询缓存未命中转物理读（I/O 压力，PAGEIOLATCH 升）→ I/O 等待占用工作线程，调度器排队（CPU 等待升）→ 全部表现为"系统慢"。表象在 I/O，起点在统计信息——这就是分诊层为什么必须看等待统计而不看表面资源图：等待统计会把第一因顶到前面，资源仪表盘只会呈现传导后的末梢。反过来做容量规划也一样：给传导链末梢扩容（换更快磁盘）往往只换来几周的平静，治理第一因（统计维护、索引设计）才是结构性解。

-- 三大瓶颈的一条龙快照（接 8.1 的差分采样法使用）
SELECT (SELECT cntr_value FROM sys.dm_os_performance_counters
         WHERE counter_name = 'Page life expectancy'
           AND object_name LIKE '%Buffer Node%')              AS PLE秒,
       (SELECT SUM(wait_time_ms) FROM sys.dm_os_wait_stats
         WHERE wait_type LIKE 'SOS_SCHEDULER_YIELD%')         AS CPU等待累计ms,
       (SELECT SUM(wait_time_ms) FROM sys.dm_os_wait_stats
         WHERE wait_type LIKE 'PAGEIOLATCH%')                 AS IO等待累计ms,
       (SELECT SUM(wait_time_ms) FROM sys.dm_os_wait_stats
         WHERE wait_type LIKE 'LCK%')                         AS 锁等待累计ms;
​

并行等待的正确解读

并行同步等待（CXPACKET 一族）是被误诊最多的指标——它常被当成"并行度太高"而直接调低最大并行度，结果大查询从并行退化为串行，报表时段全线变慢。正确的解读分三步：先看它伴随谁出现——与页闩锁或磁盘等待伴生时，它是受害者不是元凶（并行线程在等慢 I/O 汇合）；再看并行的输入是否均匀（执行计划里并行分支的行数分布，倾斜的分支决定木桶短板）；最后才考虑动并行度。调整顺序同样有讲究：先调并行的成本阈值，让小查询不进并行（多数系统的默认阈值五已严重过低，调到二十五到五十是常见起点），再考虑最大并行度——大多数在线交易库设为八以内，数据仓库按节点数对齐。动参数前把这三步走完，并行等待的锅才能甩对对象。

本节要点回顾

• 第一诊断戒律：整机使用率与等待统计矛盾时信后者——单核排队不体现为整机满载；
• CPU 指纹：调度器让出与并行同步等待居前，根因常是缺索引扫描与标量函数；
• 内存指纹：PLE 骤降加授予等待攀升，内存压力会伪装成 I/O 问题；
• I/O 指纹：页闩锁与写日志等待居前，先排除缓冲池失效再谈换盘；
• 瓶颈会传导：统计过期到扫描到内存到 I/O 到 CPU 是一条经典链，治理第一因而不是给末梢扩容；
• 指纹卡入册：三行表贴进值班手册，对着等待类型能直接翻到根因清单。

诊断学齐了。下一节把救火升级为制度：标准调优三步、自动化巡检与配置基线——让系统在没有事故的日子也持续变好。