📚 课程目录第1章大数据量下的SQL Server困境诊断

1. 大数据量下的SQL Server困境诊断

当单表数据量从百万级增长到千万级、亿级时，SQL Server 并不会在某一个瞬间突然"坏掉"，而是通过一系列可观测的症状逐步暴露问题。真正困难的不是发现"查询变慢了"，而是判断慢的根因究竟在表膨胀、索引碎片、统计信息过期，还是锁等待与阻塞。本节围绕这五类典型症状，说明如何用 DMV、性能计数器和执行计划把模糊的"慢"拆解成可定位、可验证的具体原因。

五类典型症状与它们的可观测信号

表膨胀指单表行数与占用空间远超业务实际需要，常见于只增不删的日志表、流水表。它的直接后果是全表扫描的代价随数据量线性上升，同时备份、DBCC 与索引重建的时间被同步拉长。判断表膨胀不能只看行数，还要看"行数 ÷ 页数"得到的平均行密度：如果一张宽表每页只放几行，说明行本身很宽或存在大量溢出，扫描代价会进一步放大。

索引碎片来自页分裂与不连续插入。当聚集索引键不是递增列（例如用 GUID 或业务随机编号做主键）时，新行会插入到已有页中间，触发页分裂，产生外部碎片与内部碎片。碎片本身不改变逻辑结果，但会让范围扫描读取更多页、让预读效率下降。需要注意的是，碎片率不是唯一判据：小表即使碎片率 90%，重建收益也有限；而大表碎片率超过 30% 时，扫描型查询的页读取量往往明显上升。

统计信息过期是三类症状中最容易被忽视、却最常导致执行计划劣化的一项。SQL Server 依赖统计信息的直方图估算行数，一旦数据分布发生大幅变化而统计信息未更新，优化器可能把实际返回 50 万行的查询估算成 500 行，从而选择嵌套循环 + 查找，而不是哈希连接 + 扫描。典型表现是：同一个查询昨天还很快，今天突然变慢，且执行计划中的"估计行数"与"实际行数"相差几个数量级。

查询超时通常是结果而非原因，它可能由上述任一问题引发，也可能由参数嗅探、隐式转换、缺失索引导致。因此看到超时不要直接去调命令超时时间，而应先拿到实际执行计划。

锁等待与阻塞则表现为并发场景下的连锁反应：一个长事务持有排他锁，后续请求排队，最终连接池耗尽、应用整体变慢。它与前四类的区别在于，阻塞是"等待别人"，而前三类是"自己太慢"。

用 DMV 把症状量化

诊断的第一步是把"感觉慢"变成数字。下面这组查询用于快速定位表膨胀与空间占用，按保留空间降序排列，能一眼看出哪些表是"大户"：

SELECT
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id)          AS table_name,
    SUM(p.rows)                       AS total_rows,
    SUM(p.used_page_count) * 8 / 1024 AS used_mb
FROM sys.dm_db_partition_stats AS p
WHERE p.index_id IN (0, 1)
GROUP BY p.object_id
ORDER BY used_mb DESC;

如果某张表 used_mb 达到几十 GB 而业务只关心最近三个月的数据，那么"分区 + 归档"就是比"加索引"更根本的方向。接下来检查索引碎片，注意必须指定 SAMPLED 或 LIMITED 模式，否则对大表执行 DETAILED 会长时间占用资源：

SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name                   AS index_name,
    s.avg_fragmentation_in_percent,
    s.page_count
FROM sys.dm_db_index_physical_stats(
        DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS s
JOIN sys.indexes AS i
  ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.page_count > 1000
ORDER BY s.avg_fragmentation_in_percent DESC;

统计信息是否过期，可以用最后更新时间和自上次更新以来的修改行数来判断。当修改行数超过表行数的 20%（或达到 500 行以上的动态阈值）而统计信息仍未刷新时，估算失真的风险显著上升：

SELECT
    OBJECT_NAME(sp.object_id) AS table_name,
    sp.name                   AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.modification_counter   AS rows_changed_since_update
FROM sys.dm_db_stats_properties(
        OBJECT_ID('dbo.OrderDetail'), NULL) AS sp;

执行计划：区分"估算错"与"结构差"

拿到实际执行计划后，重点看三处。第一，比较每个算子上的"估计行数"与"实际行数"。若某一步估计 800 行、实际 62 万行，说明统计信息或参数嗅探出了问题，优化器据此选错了连接方式。第二，看是否存在 Key Lookup（键查找）。当非聚集索引不覆盖查询所需列时，每一行都要回聚集索引取数据，几十万次随机 IO 足以让查询超时。第三，看是否有隐式转换警告。例如列是 VARCHAR 而参数是 NVARCHAR，会导致索引无法 Seek 只能 Scan，这是"索引明明存在却没用上"的常见原因。

配合 SET STATISTICS IO ON 观察逻辑读，可以进一步量化：一个只返回 100 行的查询如果逻辑读达到 50 万页，说明它在做全表扫描，问题不在网络也不在客户端。

锁等待与阻塞的定位

阻塞的诊断逻辑与前面几类不同，它关注的是"谁挡住了谁"。下面的查询列出当前被阻塞的会话及其等待类型：

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_seconds,
    r.command,
    t.text AS running_sql
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

若 wait_type 为 LCK_M_X、LCK_M_U 等锁等待类型，且 wait_seconds 持续增长，就应顺着 blocking_session_id 找到头阻塞会话，检查它是否开启了长事务、是否在事务中夹杂了应用层逻辑或用户交互。这类问题的根治手段通常是缩短事务边界、把批量更新拆小，而不是简单提高隔离级别或加 NOLOCK。

诊断顺序建议：先看阻塞（它影响面最大且最容易确认），再看执行计划中的估算偏差，最后才处理碎片与表膨胀。因为碎片重建耗时很长，如果根因其实是统计信息过期，重建索引只是白花时间。

性能计数器提供的全局视角

DMV 回答"哪条语句慢"，性能计数器回答"实例整体是否健康"。几个值得长期采集的计数器包括：SQL Server:Buffer Manager\Page life expectancy（页生命周期，持续低于 300 秒说明内存压力大）、SQL Server:SQL Statistics\Batch Requests/sec（吞吐基线）、SQL Server:Locks\Lock Waits/sec 与 Lock Wait Time (ms)（锁竞争强度）、SQL Server:Access Methods\Full Scans/sec（全表扫描频率）。把这些计数器与业务高峰时段对齐观察，就能判断问题是个别语句导致，还是整体资源不足导致。

需要强调的是，以上所有阈值都是经验参考值而非绝对标准。碎片率 25% 的大表可能因为查询以点查为主而毫无影响，而碎片率 10% 的表如果查询全是范围扫描，重建后逻辑读也可能明显下降。诊断的价值在于建立"症状—观测—判据"的对应关系，用数据确认根因，再决定是重建索引、更新统计信息、改写查询，还是引入分区与归档。只有根因判断准确，后续的分区表设计才有意义。