SQL Server 性能排查

SQL Server 性能排查要结合执行计划、等待类型、索引、统计信息、锁阻塞、TempDB 和事务日志。

学习目标

学完本页，你要能做到：

目标	能力
能定位慢点	从接口慢定位到 SQL、会话、实际执行计划、等待类型和阻塞链
能读计划	看懂估算行数、实际行数、逻辑读、Key Lookup、Sort、Hash Match、Spill
能解释原理	知道统计信息、参数嗅探、计划缓存、聚集/非聚集索引为什么影响性能
能排并发问题	能区分阻塞、死锁、锁升级、RCSI 行版本和 TempDB 压力
能排写入问题	能解释事务日志、Full 恢复模式、日志备份、大事务和 Always On 延迟的关系
能落地商业场景	能处理资产列表、审批抢单、批量导入、报表、支付状态更新、日志暴涨

排查流程

mermaid

flowchart TD
    A["系统慢"] --> B["定位慢 SQL"]
    B --> C["看实际执行计划"]
    C --> D{"是否 Scan 或 Key Lookup 过多"}
    D -- "是" --> E["索引 / INCLUDE / SQL 改写"]
    D -- "否" --> F{"是否等待或阻塞"}
    F -- "是" --> G["查锁 / wait stats / blocking"]
    F -- "否" --> H["查 TempDB / 日志 / CPU / IO"]

常见问题

问题	表现
Key Lookup 过多	非聚集索引后大量回表
参数嗅探	同一 SQL 不同参数执行计划差异大
统计信息过期	估算行数不准
锁阻塞	会话互相等待
TempDB 压力	大排序、临时表、版本存储
日志写瓶颈	大事务或磁盘慢

DMV

常用 DMV：

DMV	用途
`sys.dm_exec_requests`	当前请求
`sys.dm_exec_sessions`	会话
`sys.dm_os_wait_stats`	等待统计
`sys.dm_tran_locks`	锁
`sys.dm_db_index_usage_stats`	索引使用情况

Key Lookup 优化

如果执行计划显示大量 Key Lookup，可以考虑：

sql

create index IX_Asset_AssetNo
on dbo.Asset(AssetNo)
include (OwnerId, CreatedAt);

但不要为了覆盖所有查询疯狂 include 字段，索引越大，写入维护和缓存压力越大。

零基础先理解：SQL Server 慢在哪里

SQL Server 查询从客户端发来后，大体会经历解析、优化、执行、读取数据页、返回结果。慢问题通常不是一个点，而是下面几类机制叠加：

类型	典型表现	本质原因
计划不合适	同一 SQL 有时快有时慢	参数嗅探、统计信息、索引选择、Join 顺序
读太多页	CPU、IO、逻辑读高	扫描范围大、索引不覆盖、Key Lookup 多
等锁或阻塞	单独执行快，并发执行慢	长事务、更新顺序不一致、隔离级别、锁升级
TempDB 压力	排序、Hash、临时表慢	大中间结果、行版本存储、TempDB 配置不足
日志瓶颈	写入、提交慢	大事务、频繁提交、日志磁盘慢

所以排查时不要只问“加哪个索引”，而要按“计划 -> 读页 -> 等待 -> 锁 -> TempDB -> 日志”走完整链路。

商业排查闭环

mermaid

flowchart TD
    A["接口或报表变慢"] --> B["定位 SQL 和会话"]
    B --> C["查看实际执行计划"]
    C --> D{"估算行数和实际行数差距大吗"}
    D -- "大" --> E["查统计信息、参数嗅探、数据倾斜"]
    D -- "不大" --> F{"逻辑读是否过高"}
    F -- "高" --> G["查索引、Key Lookup、扫描范围"]
    F -- "不高" --> H{"是否等待"}
    H -- "阻塞" --> I["查 blocking session 和事务"]
    H -- "TempDB" --> J["查排序、Hash、版本存储"]
    H -- "日志" --> K["查大事务和日志写入"]
    E --> L["改索引、SQL、统计信息或参数策略"]
    G --> L
    I --> L
    J --> L
    K --> L
    L --> M["压测验证并观察回归"]

看实际执行计划要看什么

SQL Server 的执行计划不是只看图标。要重点看估算和实际是否一致。

计划信息	怎么理解
Estimated Number of Rows	优化器估算行数
Actual Number of Rows	实际行数
Logical Reads	读了多少数据页，性能排查非常关键
Index Seek	按索引定位范围，不代表一定快
Index Scan / Table Scan	扫描索引或表，返回大范围时可能合理
Key Lookup	非聚集索引找到行定位器后再回聚集索引取列
Sort	排序，可能吃内存或溢写 TempDB
Hash Match	Hash Join 或聚合，大数据量时可能吃内存

一个常见误区是看到 Index Seek 就觉得没问题。若 Seek 后返回 50 万行，再做 50 万次 Key Lookup，仍然会很慢。

第一步：拿到 SQL、参数和调用量

生产慢 SQL 排查一定要拿真实参数。同一条 SQL，OwnerId=1001 可能返回 20 行，OwnerId=9999 可能返回 200 万行，执行计划完全不同。

常用方向：

sql

select top (20)
    qs.total_elapsed_time / 1000 as total_ms,
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_worker_time / 1000 as cpu_ms,
    st.text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
order by qs.total_elapsed_time desc;

这段 DMV 不是万能的，但能先找出“总耗时高、调用多、逻辑读高、CPU 高”的 SQL。排查时要区分：

类型	例子	优化重点
单次很慢	报表一次 60 秒	执行计划、索引、TempDB、分区、异步化
单次不慢但调用巨大	接口 20ms 但每天 500 万次	缓存、索引、减少字段、接口聚合
CPU 高	函数计算、Hash、排序、编译多	SQL 改写、计划复用、索引
逻辑读高	读了大量页	索引、覆盖、过滤条件、分页

如果应用侧有 traceId、接口名、SQL 参数日志，要把数据库 DMV 和应用日志对起来。否则只看到数据库里有慢 SQL，却不知道哪个业务入口触发，会很难改。

第二步：实际执行计划和逻辑读

在测试或影子环境可以打开：

sql

set statistics io on;
set statistics time on;

然后执行目标 SQL，观察类似输出：

text

Table 'Asset'. Scan count 1, logical reads 985432, physical reads 120
SQL Server Execution Times:
   CPU time = 3520 ms, elapsed time = 4860 ms.

解释：

指标	含义	为什么重要
`logical reads`	从 Buffer Pool 读取了多少 8KB 页	比单次耗时更稳定，能衡量访问数据量
`physical reads`	需要从磁盘读的页	受缓存影响大，但高时说明 IO 压力
`CPU time`	CPU 计算耗时	高说明计算、Join、排序、编译或扫描重
`elapsed time`	墙钟时间	包含等待、IO、阻塞、并行等

如果 logical reads 很高，哪怕当前因为缓存命中所以 elapsed time 不高，在线上高并发下仍然可能压垮 Buffer Pool 和 CPU。

第三步：估算行数为什么错

SQL Server 优化器要先估算“这一步大概返回多少行”，再决定走 Seek、Scan、Nested Loops、Hash Match、Merge Join、是否并行、内存授予多少。

mermaid

flowchart TD
    A["SQL和参数"] --> B["读取统计信息"]
    B --> C["估算过滤后行数"]
    C --> D["估算 Join 和排序成本"]
    D --> E["选择执行计划"]
    E --> F["缓存计划"]

估算错的常见原因：

原因	表现
统计信息过期	大批量导入或删除后，计划仍按旧数据估算
数据倾斜	大客户和小客户数据量差异很大
参数嗅探	第一次参数生成的计划不适合后续参数
多列相关	`TenantId` 和 `HospitalCode` 组合选择性被估错
临时表统计不足	中间结果估算偏差，后续 Join 计划错

查看统计信息更新时间：

sql

select
    object_name(s.object_id) as table_name,
    s.name as stats_name,
    sp.last_updated,
    sp.rows,
    sp.modification_counter
from sys.stats s
cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
where object_name(s.object_id) = 'Asset';

更新统计信息：

sql

update statistics dbo.Asset IX_Asset_Owner_CreatedAt with fullscan;

fullscan 更准但成本更高，大表生产环境要评估窗口。不是所有统计信息都要全量扫，关键 SQL、关键列、数据倾斜表才需要重点处理。

Key Lookup 为什么会拖慢

SQL Server 非聚集索引叶子层不一定包含查询需要的所有列。假设索引只有 OwnerId, CreatedAt，查询还要 AssetName, DeptName，执行器会先从非聚集索引找到行，再根据行定位器回到聚集索引取剩余列。

mermaid

flowchart TD
    A["非聚集索引按 OwnerId 定位"] --> B["得到聚集索引键 Id"]
    B --> C["回到聚集索引查整行"]
    C --> D{"返回行数多吗"}
    D -- "少" --> E["Key Lookup 可接受"]
    D -- "多" --> F["大量随机读，性能下降"]

优化方式：

sql

create index IX_Asset_Owner_CreatedAt
on dbo.Asset(OwnerId, CreatedAt desc)
include (AssetNo, DeptId, Status);

但 INCLUDE 不是越多越好。索引越宽，占用页越多，缓存命中下降，写入、更新、删除都要维护更多索引。

参数嗅探为什么常见

SQL Server 编译存储过程或参数化 SQL 时，会根据第一次看到的参数值生成执行计划并缓存。若数据分布不均，同一个计划不一定适合所有参数。

示例：一个大客户有 100 万条资产，小客户只有 100 条资产。

sql

create procedure dbo.QueryAsset
  @OwnerId bigint
as
begin
  select Id, AssetNo, CreatedAt
  from dbo.Asset
  where OwnerId = @OwnerId
  order by CreatedAt desc;
end;

如果首次参数是小客户，优化器可能选择 Nested Loops + Lookup；后续大客户复用这个计划就会爆炸式回表。解决方向包括：

更新统计信息，确保估算更接近真实。
对极端参数拆分 SQL。
使用 OPTION (RECOMPILE) 让每次按当前参数编译，适合低频重查询。
使用 OPTIMIZE FOR 或 Query Store 固定更合适计划。
从业务上隔离大客户数据或分区。

不要一上来全局清计划缓存，会影响整库并造成编译风暴。

统计信息为什么重要

SQL Server 优化器根据统计信息估算行数、成本和 Join 方式。统计信息不准时，计划就容易错。

sql

update statistics dbo.Asset IX_Asset_Owner_CreatedAt;

需要关注：

大表批量导入后统计信息是否更新。
数据分布是否倾斜。
估算行数和实际行数是否差距巨大。
自动更新统计信息是否及时。
过滤索引是否能更准确表达热点条件。

阻塞和死锁怎么排

阻塞是一个会话等另一个会话释放锁。死锁是多个会话互相等待，SQL Server 会选择一个牺牲者回滚。

查当前请求：

sql

select
  session_id,
  blocking_session_id,
  wait_type,
  wait_time,
  status,
  command
from sys.dm_exec_requests
where blocking_session_id <> 0;

查锁：

sql

select
  request_session_id,
  resource_type,
  request_mode,
  request_status
from sys.dm_tran_locks;

商业系统常见阻塞来源：

场景	原因	处理
批量更新资产状态	单事务更新太多行	分批提交，缩小条件
审批任务抢单	多线程抢同一批待办	状态条件更新，合理索引
报表长查询挡写入	隔离级别和锁策略不合适	评估 RCSI、读写分离
忘记提交事务	应用异常或手工 SQL 未提交	查会话、回滚或提交

RCSI 和行版本

SQL Server 默认读提交可能读写互相阻塞。开启 Read Committed Snapshot Isolation 后，读提交使用行版本读取已提交快照，能减少读写阻塞。

sql

alter database YourDb
set read_committed_snapshot on;

但 RCSI 不是免费午餐：

行版本存储在 TempDB，会增加 TempDB 压力。
读到的是语句开始时的已提交版本，不是最新未提交数据。
写写冲突仍然需要锁。
长查询会让版本保留更久。

所以开启前要评估 TempDB、报表查询、写入压力和一致性语义。

TempDB 为什么会成为瓶颈

TempDB 用于临时表、排序、Hash、版本存储、游标等。它慢了，很多看似无关的业务都会慢。

典型信号：

大量 Sort 或 Hash Match 溢写。
开启 RCSI 后版本存储增长。
临时表大量创建删除。
等待类型集中在 TempDB 相关资源。

优化方向：

减少不必要的大排序。
给 Join 和过滤条件设计合适索引。
临时表数据量大时加索引并更新统计信息。
合理配置 TempDB 数据文件数量和磁盘。

Sort、Hash 溢写到 TempDB 是什么

执行计划里如果看到 Sort、Hash Match 带有 warning，或者实际计划显示 spill，说明内存授予不够，中间数据被写到了 TempDB。

mermaid

flowchart TD
    A["查询需要排序或Hash"] --> B["优化器估算需要多少内存"]
    B --> C["申请 Memory Grant"]
    C --> D{"实际数据是否超过内存"}
    D -- "否" --> E["内存中完成"]
    D -- "是" --> F["溢写到 TempDB"]
    F --> G["读写临时文件，查询变慢"]

为什么会溢写？

原因	解释
估算行数偏小	优化器以为只有 100 行，实际 100 万行
行宽估算偏小	选择了很多宽字段，排序中间结果很大
统计信息过期	内存授予按旧数据量计算
并发查询多	内存授予竞争，单个查询拿不到足够内存
SQL 形态不合理	先排序大结果，再过滤或分页

处理方向：

先修估算行数：统计信息、参数嗅探、SQL 改写。
减少中间结果：先过滤再 Join/排序。
用索引提供有序数据，减少显式 Sort。
报表查询迁到报表库或异步导出。
不要一上来盲目加服务器内存，估算错时加内存也可能继续浪费。

事务日志写入瓶颈

SQL Server 写入事务时，日志是提交可靠性的核心。提交成功前，相关日志记录必须持久化到 LDF。

mermaid

flowchart TD
    A["执行 insert/update/delete"] --> B["生成日志记录"]
    B --> C["修改 Buffer Pool 数据页"]
    C --> D["commit"]
    D --> E["等待日志写入磁盘"]
    E --> F["返回提交成功"]
    F --> G["数据页后续刷入 MDF/NDF"]

日志瓶颈常见表现：

现象	可能原因
写接口慢	日志磁盘延迟高或提交太频繁
日志文件暴涨	Full 模式没做日志备份、大事务、复制延迟
批量导入拖垮系统	单事务太大或索引太多导致日志量巨大
Always On 延迟	副本接收/重做日志慢，主库日志不能及时截断

查看数据库日志空间：

sql

dbcc sqlperf(logspace);

查看无法截断原因：

sql

select name,
       recovery_model_desc,
       log_reuse_wait_desc
from sys.databases;

log_reuse_wait_desc 常见含义：

值	说明
`LOG_BACKUP`	Full/Bulk-logged 模式需要日志备份
`ACTIVE_TRANSACTION`	有长事务未结束
`AVAILABILITY_REPLICA`	Always On 副本相关原因
`REPLICATION`	复制相关消费滞后
`NOTHING`	当前没有阻塞日志复用的明显原因

注意：不要直接删除 LDF 文件。日志是恢复链的一部分，正确做法是修复日志备份、长事务、复制延迟或批处理设计，然后规划合理日志大小。

Query Store 怎么帮助排查

Query Store 会记录查询文本、计划、运行时统计，可以用来分析“昨天快今天慢”“发布后计划变了”“参数嗅探导致计划抖动”。

适合用它看：

问题	Query Store 价值
计划回退	同一 query 有多个 plan，某个 plan 明显慢
发布影响	发布前后耗时和逻辑读变化
参数嗅探	不同时间段复用不同计划
固定计划	对核心 SQL 临时强制使用稳定计划

思路不是“看到慢就 force plan”。强制计划可以救急，但如果数据分布持续变化、索引变更、SQL 语义变化，强制旧计划可能变成新风险。根因仍然要回到统计信息、索引、参数策略和业务数据分布。

商业场景：审批任务抢单阻塞

待办任务表：

sql

create table dbo.ApprovalTask (
  Id bigint identity(1,1) not null primary key,
  TenantId bigint not null,
  Status varchar(20) not null,
  Priority int not null,
  CreatedAt datetime2 not null default sysdatetime(),
  HandlerId bigint null
);

多个审批服务并发领取任务，如果先 select 再 update，容易多个线程选到同一批数据，或者发生阻塞。

更稳的思路是单条 update 完成“筛选 + 加锁 + 改状态 + 返回结果”：

sql

;with picked as (
    select top (10) Id
    from dbo.ApprovalTask with (updlock, readpast, rowlock)
    where TenantId = @TenantId
      and Status = 'WAITING'
    order by Priority desc, CreatedAt
)
update t
set Status = 'RUNNING',
    HandlerId = @HandlerId
output inserted.Id
from dbo.ApprovalTask t
join picked p on t.Id = p.Id;

关键词理解：

写法	作用
`updlock`	读取时就拿更新锁，避免多个会话同时准备更新同一行
`readpast`	跳过已被锁住的行，减少抢任务等待
`rowlock`	倾向行锁，但不是绝对保证
`output inserted.Id`	返回本次真正抢到的任务

配套索引：

sql

create index IX_ApprovalTask_Pick
on dbo.ApprovalTask(TenantId, Status, Priority desc, CreatedAt)
include (HandlerId);

注意：锁提示不是到处乱加。它适合明确的队列/抢单模型，普通查询滥用锁提示会降低并发并掩盖索引问题。

商业场景：批量删除导致日志暴涨和锁升级

错误做法：

sql

delete from dbo.AssetLog
where CreatedAt < dateadd(month, -6, sysdatetime());

如果一次删除几千万行，可能导致：

单个大事务产生巨大日志。
锁数量很多，可能锁升级。
阻塞在线查询或写入。
回滚时间非常长。
Full 模式下日志备份压力增大。

更合理的分批：

sql

while 1 = 1
begin
    delete top (5000)
    from dbo.AssetLog
    where CreatedAt < dateadd(month, -6, sysdatetime());

    if @@rowcount = 0
        break;

    waitfor delay '00:00:01';
end

如果日志表天然按时间归档，更好的方案可能是分区表，按月切换分区后归档或清理，而不是每天大范围 delete。

商业场景：支付回调更新慢

支付回调常见更新：

sql

update dbo.PaymentOrder
set Status = 'SUCCESS',
    PaidAt = sysdatetime()
where PayNo = @PayNo
  and Status = 'PAYING';

这里必须带状态条件。原因：

防重复回调：已成功的订单不应再次更新。
防并发覆盖：旧请求不能覆盖新状态。
支持幂等：影响行数为 0 时，再查询当前状态判断是否已处理。

如果偶发慢，排查顺序：

排查点	说明
是否走唯一索引	`PayNo` 应该有唯一索引
是否被阻塞	查 `blocking_session_id`
是否事务太长	上游是否把远程通知放在事务里
日志写是否慢	写高峰是否出现日志等待
是否触发器太重	更新是否带出复杂触发器或审计

等待类型怎么读

等待类型告诉你请求在等什么。不要看到慢就只改 SQL。

等待类型方向	可能含义
`LCK_M_*`	锁等待，查阻塞链
`PAGEIOLATCH_*`	数据页物理 IO 等待
`WRITELOG`	事务日志写等待
`PAGELATCH_*`	内存页 latch 竞争，常见于 TempDB 或热点页
`CXPACKET` / `CXCONSUMER`	并行相关等待，需要结合计划看
`ASYNC_NETWORK_IO`	客户端消费结果慢或网络慢

查当前等待：

sql

select
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.session_id <> @@spid;

等待类型只是方向，不是结论。例如 ASYNC_NETWORK_IO 可能是网络慢，也可能是应用一次拉 100 万行但处理很慢。

商业场景：资产列表偶发 20 秒

接口：

sql

select top (20) Id, AssetNo, OwnerId, Status, CreatedAt
from dbo.Asset
where OwnerId = @OwnerId
order by CreatedAt desc;

排查：

看实际计划，发现有时走 IX_Asset_Owner_CreatedAt，有时选择扫描。
对大客户参数，实际行数远超估算。
Key Lookup 次数很多，因为列表需要 Status、AssetNo。
统计信息过期，且大客户数据倾斜。
新增覆盖索引或 INCLUDE，更新统计信息。
对大客户查询单独分页策略，避免一次扫描过多历史数据。

sql

create index IX_Asset_Owner_CreatedAt_List
on dbo.Asset(OwnerId, CreatedAt desc)
include (AssetNo, Status);

上线前要验证写入影响，因为资产采集入库会维护这个索引。

常见误区

误区	为什么错
Index Seek 一定快	Seek 后可能返回大量行并 Key Lookup
加 INCLUDE 没成本	索引变宽会增加存储、缓存和写入维护成本
清计划缓存能治参数嗅探	可能短暂有效，后续还会复发，并影响全库
NOLOCK 能解决慢	可能读脏数据、重复数据、漏数据，不适合核心业务
开 RCSI 就没有锁问题	写写冲突仍然有锁，TempDB 压力会上升

面试标准回答

text

SQL Server 性能排查要先定位慢 SQL 和实际执行计划，关注 Index Seek、Index Scan、Table Scan、Key Lookup、Hash Match、Sort 等算子。Key Lookup 过多说明非聚集索引不能覆盖查询，可能需要 INCLUDE 或改写 SQL。统计信息过期会导致估算错误，参数嗅探可能导致同一 SQL 在不同参数下计划不合适。还要结合等待统计、锁阻塞、TempDB、事务日志和 IO 情况综合判断。

JDK 8/11/17/21 LTS演进

线程池

SQL Server 性能排查 ​

学习目标 ​

排查流程 ​

常见问题 ​

DMV ​

Key Lookup 优化 ​

零基础先理解：SQL Server 慢在哪里 ​

商业排查闭环 ​

看实际执行计划要看什么 ​

第一步：拿到 SQL、参数和调用量 ​

第二步：实际执行计划和逻辑读 ​

第三步：估算行数为什么错 ​

Key Lookup 为什么会拖慢 ​

参数嗅探为什么常见 ​

统计信息为什么重要 ​

阻塞和死锁怎么排 ​

RCSI 和行版本 ​

TempDB 为什么会成为瓶颈 ​

Sort、Hash 溢写到 TempDB 是什么 ​

事务日志写入瓶颈 ​

Query Store 怎么帮助排查 ​

商业场景：审批任务抢单阻塞 ​

商业场景：批量删除导致日志暴涨和锁升级 ​

商业场景：支付回调更新慢 ​

等待类型怎么读 ​

商业场景：资产列表偶发 20 秒 ​

常见误区 ​

面试标准回答 ​

SQL Server 性能排查

学习目标

排查流程

常见问题

DMV

Key Lookup 优化

零基础先理解：SQL Server 慢在哪里

商业排查闭环

看实际执行计划要看什么

第一步：拿到 SQL、参数和调用量

第二步：实际执行计划和逻辑读

第三步：估算行数为什么错

Key Lookup 为什么会拖慢

参数嗅探为什么常见

统计信息为什么重要

阻塞和死锁怎么排

RCSI 和行版本

TempDB 为什么会成为瓶颈

Sort、Hash 溢写到 TempDB 是什么

事务日志写入瓶颈

Query Store 怎么帮助排查

商业场景：审批任务抢单阻塞

商业场景：批量删除导致日志暴涨和锁升级

商业场景：支付回调更新慢

等待类型怎么读

商业场景：资产列表偶发 20 秒

常见误区

面试标准回答