sql-server-performance

SQL Server 性能

适用场景

• 曾经运行很快的查询现在变慢了
• 阅读执行计划以查找瓶颈
• 在索引策略（聚集、覆盖、筛选）之间做选择
• 诊断锁定、阻塞或死锁
• 编写不会导致事务日志膨胀的批量 INSERT/UPDATE/DELETE
• 修复存储过程中的参数嗅探问题
• 决定使用临时表还是表变量
• 分析等待统计信息以识别资源瓶颈

不适用场景： 应用程序架构设计（表设计、命名约定、访问控制）、备份/还原策略、高可用配置或复制设置。

诊断流程图

查询变慢时，请按以下顺序操作——每一步都会缩小原因范围，再进入下一步。

步骤 1 — 获取实际执行计划

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- 然后在 SSMS 中按 Ctrl+M（包括实际执行计划）运行查询

请留意：

• 键查找 — 先进行非聚集索引查找，再对每一行执行聚集索引查找。达到 1,000 行以上时，这会成为主要耗时。修复方法：将缺少的 SELECT 列添加到 NCI INCLUDE 列表中。
• 运算符之间的粗箭头（粗连接线）——估算行数 ≪ 实际行数。这是基数估算失败。修复方法：更新统计信息，并检查是否存在参数嗅探。
• Hash Match 上的黄色警告三角形——内存授予过小；哈希已溢出到 tempdb。修复方法：更新统计信息，使内存授予大小合适。
• 大型表上的表扫描——没有聚集索引，或非 SARGable 谓词迫使 SQL Server 执行扫描。修复方法：添加聚集索引或重写谓词。
• 索引假脱机 — SQL Server 即时构建了临时索引。修复方法：创建永久索引。

步骤 2 — 检查等待统计信息

-- 步骤 1：在问题时间段开始前创建快照
SELECT wait_type, wait_time_ms INTO #w FROM sys.dm_os_wait_stats;
-- ...等待 60 秒，或等待缓慢时段结束...
-- 步骤 2：差值显示该时间段内消耗等待时间的项目
SELECT c.wait_type, c.wait_time_ms - b.wait_time_ms AS delta_ms
FROM sys.dm_os_wait_stats c JOIN #w b ON b.wait_type = c.wait_type
WHERE c.wait_time_ms > b.wait_time_ms
  AND c.wait_type NOT IN ('SLEEP_TASK','LAZYWRITER_SLEEP','WAITFOR','CXCONSUMER')
ORDER BY delta_ms DESC;
DROP TABLE #w;

主要等待	可能原因
LCK_M_*	阻塞/锁定 — 请参阅 locking-blocking.md
PAGEIOLATCH_SH	缺少索引或缓冲池未预热
CXPACKET	并行度不均衡或 MAXDOP 过高
RESOURCE_SEMAPHORE	内存授予不足 — 哈希/排序溢出
WRITELOG	事务日志 I/O 瓶颈
SOS_SCHEDULER_YIELD	CPU 饱和

完整方法和 DMV 查询请参阅 wait-stats.md。

步骤 3 — 评估索引

-- 缺失索引建议排名前 10（重启后会重置——请尽快处理）
SELECT TOP 10
    d.statement                 AS [Table],
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    ROUND(s.avg_total_user_cost * s.avg_user_impact
          * (s.user_seeks + s.user_scans), 0) AS estimated_improvement
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats s
    ON g.index_group_handle = s.group_handle
JOIN sys.dm_db_missing_index_details d
    ON g.index_handle = d.index_handle
WHERE d.database_id = DB_ID()
ORDER BY estimated_improvement DESC;

有关聚集键选择、覆盖索引和索引过多的权衡，请参阅 index-strategy.md。

步骤 4 — 检查统计信息是否为最新

-- 将直方图最大值与实际最大值比较（递增键问题）
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_OrderDate') WITH HISTOGRAM;
SELECT MAX(OrderDate) AS actual_max FROM dbo.Orders;
-- 如果 actual_max > last RANGE_HI_KEY，说明统计信息未涵盖最近的行
-- 检查修改计数器
SELECT s.name, sp.last_updated, sp.modification_counter,
       CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(5,2))
           AS pct_modified
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.Orders');

有关直方图解读和更新策略，请参阅 statistics-tuning.md。

步骤 5 — 诊断参数嗅探

-- 同一存储过程存在多个执行计划 = 嗅探不稳定
SELECT qsq.query_id, COUNT(DISTINCT qsp.plan_id) AS plan_count,
       qsqt.query_sql_text
FROM sys.query_store_query       qsq
JOIN sys.query_store_plan        qsp  ON qsp.query_id      = qsq.query_id
JOIN sys.query_store_query_text  qsqt ON qsqt.query_text_id = qsq.query_text_id
GROUP BY qsq.query_id, qsqt.query_sql_text
HAVING COUNT(DISTINCT qsp.plan_id) > 3
ORDER BY plan_count DESC;

请参阅下文的“参数嗅探”部分和 execution-plans.md。

索引策略

表应有一个经过合理选择的聚集索引，并针对辅助访问模式建立有目标性的覆盖非聚集索引。每个索引都会带来写入开销——OLTP 表的非聚集索引总数应保持在 3–5 个。

聚集索引选择——选择满足以下条件的键：

• 窄——聚集键会复制到每个非聚集索引叶级行中。与 UNIQUEIDENTIFIER（16 字节）相比，INT（4 字节）会在所有索引和数百万行中显著减少开销。
• 持续递增——随机插入会导致 50/50 页拆分和严重碎片。使用 INT IDENTITY、BIGINT IDENTITY 或 NEWSEQUENTIALID()。
• 唯一——SQL Server 会静默地为重复值追加一个 4 字节的唯一化值，导致索引膨胀。
• 静态——更新聚集键会在物理上移动行，并连带更新每个非聚集索引。

非聚集索引的列顺序：

1. 首先放置等值谓词（col = @val）
2. 接着放置范围谓词（col BETWEEN、col > @val）
3. 最后放置 ORDER BY 列（如果它们在索引中跟在等值列之后，排序无需额外开销）

覆盖索引可消除键查找——将仅用于 SELECT 的列添加到 INCLUDE 中：

-- 之前：每一行都会触发一次回到聚集索引的键查找
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
    ON dbo.Orders (CustomerID);

-- 之后：覆盖索引——无需键查找
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Covering
    ON dbo.Orders (CustomerID)
    INCLUDE (OrderDate, Status, TotalAmt);

筛选索引覆盖部分行——维护成本更低，选择性更高：

-- 仅为活动订单建立索引——比完整表的 NCI 小得多
CREATE NONCLUSTERED INDEX IX_Orders_Active
    ON dbo.Orders (CustomerID, OrderDate)
    INCLUDE (Status)
    WHERE Status = 1;

-- 对可空外部键建立唯一约束（排除 NULL）
CREATE UNIQUE NONCLUSTERED INDEX UX_Orders_ExternalRef
    ON dbo.Orders (ExternalRef)
    WHERE ExternalRef IS NOT NULL;

有关碎片阈值、填充因子指南以及缺失索引 DMV 的用法，请参阅 index-strategy.md。

SARGability

当优化器可以使用索引查找而不是扫描每一行时，谓词就是 SARGable。规则：筛选列绝不能套用函数或表达式。

非 SARGable（会强制扫描）	SARGable 替代写法
WHERE YEAR(OrderDate) = 2025	WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
WHERE ISNULL(col, '') = @Val	确保列为 NOT NULL；使用 WHERE col = @Val
WHERE col LIKE '%search'	使用尾部通配符：WHERE col LIKE 'search%'
WHERE DATEDIFF(day, col, GETDATE()) < 30	WHERE col > DATEADD(day, -30, GETDATE())
WHERE CAST(col AS VARCHAR) = '42'	修正参数类型，使其与列类型匹配
WHERE LEFT(LastName, 3) = 'Smi'	WHERE LastName LIKE 'Smi%'
WHERE col + 0 = @Val	WHERE col = @Val（绝不要对列进行算术运算）

隐式类型转换的效果与函数相同——它会迫使优化器转换每一行。参数类型必须与列类型完全一致。在执行计划 XML 中查找 PlanAffectingConvert 警告。

参数嗅探

SQL Server 会在存储过程首次执行时，使用当时传入的实际参数值编译执行计划。该计划会被缓存并重复使用——即使后续调用传入了不同的参数。90% 的情况下，这样做是有益的；但当数据分布倾斜且被嗅探的值不具代表性时，就会产生问题。

策略排名（按顺序使用）：

1. OPTIMIZE FOR UNKNOWN — 使用平均分布统计信息生成稳定的计划。当可能存在许多不同的参数值，且没有某个值占主导时，优先使用：

CREATE PROCEDURE dbo.GetOrders_ut
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, TotalAmt
    FROM   dbo.Orders
    WHERE  CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));
END;

2. OPTION (RECOMPILE) — 每次执行都强制生成新计划，并将实际参数值嵌入计划。优化器每次都会找到最优计划，但每次调用都要付出 CPU 编译开销。适用于执行频率低但参数变化很大的存储过程（夜间报表、管理工具）：

SELECT OrderID, OrderDate, TotalAmt
FROM   dbo.Orders
WHERE  CustomerID = @CustomerID
OPTION (RECOMPILE);   -- 语句级别，不是在存储过程上使用 WITH RECOMPILE

3. 复制到局部变量 — 避免使用。 将 @CustomerID 赋给局部变量会使嗅探失效，同时也会失去常见情况下嗅探带来的好处。OPTIMIZE FOR UNKNOWN 可以实现相同的统计行为，而且意图明确并有文档记录。

-- BAD: 完全禁用嗅探，即使嗅探本可以发挥作用
DECLARE @CID INT = @CustomerID;
SELECT ... WHERE CustomerID = @CID;

在启用了 Query Store 且兼容性级别为 160 的 SQL Server 2022 上，参数敏感计划优化（PSPO）可以针对倾斜分布自动维护多个计划变体。对于 SQL Server 2019 及更早版本，它不能替代上述策略。

临时表与表变量

维度	临时表（#t）	表变量（@t）	CTE
统计信息	有 — 自动创建	无（2019 之前为 1 行；2019+ 延迟编译）	无
索引支持	所有类型	仅 PRIMARY KEY、UNIQUE	无
并行处理	有	有	有
重新编译触发条件	架构或统计信息变更时	很少	无
ROLLBACK 后仍存在	否 — 会被删除	是 — 内容保留	否
作用域	会话及被调用的存储过程	仅当前批次	当前语句
TempDB 缓存	有 — 在存储过程中	同左	无

选择规则：

• 当行数超过几百行、需要为中间结果创建索引，或需要为后续联接提供准确的基数估算时，使用临时表。
• 当数据集较小且范围明确（1–100 行）、希望避免触发重新编译，或需要让数据在 ROLLBACK 后继续保留时，使用表变量（例如，在工作开始前插入审计日志）。
• 当结果只被引用一次时，使用 CTE — CTE 不会被具体化；每次引用都会重新执行子查询。引用两次的 CTE 会扫描源数据两次。

SQL Server 2019 表变量延迟编译（兼容级别 150+）：优化器会将使用表变量的语句延迟到表变量填充后再编译，并使用实际行数。这在很大程度上解决了 2019+ 环境中的“估算为 1 行”问题。对于列级统计信息以及非键列上的索引，临时表仍然更有优势。

-- 验证延迟编译是否已启用（兼容级别 150+）
SELECT name, value FROM sys.database_scoped_configurations
WHERE name = 'DEFERRED_COMPILATION_TV';

常见错误

错误	修正方法
WHERE 子句不可 SARG	切勿对筛选列使用函数 — 应对参数应用函数
高行数查询出现 Key Lookup	将所需的 SELECT 列添加到 NCI 的 INCLUDE 列表中
为了“提升读取性能”而使用 NOLOCK	改为启用 RCSI — 读取一致，且无共享锁开销
用局部变量副本解决参数嗅探	使用 OPTIMIZE FOR UNKNOWN 或语句级 OPTION (RECOMPILE)
忽略随机键表的填充因子	将填充因子设为 70–80%；按计划重建
盲目创建缺失索引建议	评估写入开销及其与现有索引的重叠情况
不考虑碎片程度而重建所有索引	跳过小于 1,000 页的表；跳过碎片率低于 5% 的索引
大表的自动更新统计信息始终未触发	升级到兼容级别 130+，以使用动态阈值：MIN(500 + 0.20×n, SQRT(1000×n)) — 在大表上触发频率约提高 20 倍
表变量的基数固定为 1 行	升级到兼容级别 150，以启用延迟编译
哈希/排序操作溢出到 tempdb	修正统计信息，使内存授予大小正确；行模式 MGF（2019+）会自动调整
将 NOLOCK 当作死锁修复方案	解决死锁需要锁定顺序或 RCSI — NOLOCK 无法阻止死锁
单次大批量 DELETE 导致日志膨胀	在 WHILE 循环中使用 DELETE TOP (5000) 分块删除

参考文件

• execution-plans.md — 计划读取、关键运算符、基数估算、CE 版本映射、智能查询处理（IQP）、Query Store 配置和回归检测
• index-strategy.md — 聚集键选择、覆盖索引、筛选索引、碎片、缺失索引 DMV
• wait-stats.md — 主要等待类型、DMV 查询、基线方法、资源关联、TempDB 配置、最大服务器内存
• locking-blocking.md — 隔离级别、RCSI、锁升级、死锁、NOLOCK 的风险
• batch-operations.md — 分块 DML、最小日志记录、分区切换、日志管理
• statistics-tuning.md — 直方图解读、自动更新阈值、升序键问题、增量统计信息、筛选统计信息