SQL Server 编译与重编译机制：执行计划重用利弊及触发场景

编译含义与执行计划缓存

当 SQL Server 接收到任何指令（如查询、批处理、存储过程、触发器、预编译指令或动态 SQL）时，需经过语法与语句解释，随后进行“编译”以生成可运行的“执行计划”。编译过程中，SQL Server 会依据涉及的表结构（Schema）、统计信息及语句具体内容，估算多种可能的执行路径及其成本，最终选择预估成本最低的计划。

为提高效率，生成的执行计划通常缓存在内存中（称为 Plan Cache）。后续相同语句执行时，可直接重用该计划，避免重复编译，这称为“执行计划重用”。然而，即使语句完全一致，SQL Server 有时也会强制重新编译，即“重编译”。由于编译过程消耗 CPU 资源，如何在利用缓存速度与确保计划最优之间取得平衡，是数据库性能管理的核心。

执行计划重用的利弊分析

执行计划的优劣直接决定语句执行速度。使用最优计划可能比劣质计划快数百甚至数千倍。从理论上看，每条语句都重新编译能确保获取当前最优计划，但若每秒处理成百上千条指令，全量编译将造成巨大的资源浪费。因此，SQL Server 试图在有限的编译/重编译开销中获得最佳整体性能。

可通过以下命令查看当前缓存中的执行计划（注意：在生产服务器直接运行可能因缓存庞大而影响性能）：

SELECT * FROM sys.[syscacheobjects]

1. 执行计划重用的优势

• 降低 CPU 消耗：避免重复的解析与优化计算。
• 减少阻塞：加快指令执行速度，减少锁等待时间。

2. 执行计划重用的风险：参数嗅探

计划重用并非总是好事。最典型的负面效应是“参数嗅探”（Parameter Sniffing）。当存储过程第一次执行时，SQL Server 会根据传入的具体参数值构建执行计划并缓存。若后续调用传入的参数数据分布差异巨大，原先基于极端情况（如只有一行数据）生成的索引扫描计划，在处理百万行数据时可能导致性能急剧下降。

3. 重编译的价值与代价

重编译能为当前语句生成更准确的计划，适应数据变化。但对于高频、快速执行的语句，编译时间占总耗时比例过大，反而成为资源瓶颈。一般情况下，SQL Server 的自动平衡机制表现良好，但在特定场景下仍需人工干预监控。

重编译的典型发生场景

为确保返回正确结果或优化性能，SQL Server 会在以下场景中强制放弃缓存计划，进行现场重编译：

第1步：检查对象架构（Schema）变化

目的：确保执行计划与当前表结构匹配。

说明：如果指令涉及的表或视图发生以下变化，原有计划不再适用，必须重编译：

• 添加或删除字段/列。
• 添加或删除索引。
• 添加或删除约束条件。

第2步：手动触发 sp_recompile

目的：强制在下一次执行前重新优化。

• 针对存储过程/触发器：在该对象上运行 sp_recompile，下次运行时发生一次重编译。
• 针对表/视图：在此类对象上运行 sp_recompile，所有引用该对象的存储过程在下一次执行前均需重编译。

第3步：识别清除执行计划缓存的操作

某些操作会清空内存中的计划缓存，迫使所有相关语句重编译。需注意区分全局影响与实例级影响。

全局缓存清除（影响整个 SQL Server 实例）

1. 分离数据库：Detach 一个数据库。
2. 版本升级：对数据库进行升级并在新服务器上启动。
3. 手动清理：运行 DBCC freeproccache。
4. 配置重置：运行 reconfigure 语句。
5. 排序规则修改：运行 ALTER DATABASE ... COLLATE 修改字符集/排序规则。

实例（Database）级别缓存清除

以下操作仅清除特定数据库的计划缓存：

• DBCC FLUSHPROCINDB：清除指定 ID 数据库的存储过程缓存。

DECLARE @a INT;
SELECT @a=DB_ID('YourDatabaseName'); -- 替换为目标库名
DBCC FLUSHPROCINDB(@a);

• 数据库状态变更：ALTER DATABASE ... MODIFY NAME、SET ONLINE/OFFLINE/EMERGENCY、DROP DATABASE。
• 异常处理：数据库自动关闭或 DBCC CHECKDB 结束时。

第4步：监测 SET 开关值的变化

目的：不同的会话设置可能改变语句语义或结果，因此不兼容旧计划。

若以下连接选项发生变化，先前缓存的计划不可重用：

-- 常用 SET 选项示例
SET ANSI_NULL_DFLT_OFF, ANSI_NULL_DFLT_ON
SET ANSI_NULLS, ANSI_PADDING
SET ANSI_WARNINGS, ARITHABORT
SET CONCAT_NULL_YIELDS_NULL
SET DATEFIRST, DATEFORMAT
SET FORCEPLAN (弃用)
SET LANGUAGE
SET NO_BROWSE_TABLE
SET NUMERIC_ROUNDABORT
SET QUOTED_IDENTIFIER

这些设置会影响数据类型比较、空值处理及数学运算行为，故 SQL Server 会据此重新生成计划。

第5步：统计信息更新

目的：根据最新数据分布调整执行策略。

当表或视图上的统计信息被手动更新（如运行 UPDATE STATISTICS），或 SQL Server 触发了自动统计信息更新阈值时，涉及该对象的语句将被标记为重编译，以利用最新的行数、基数等信息优化路径选择。

总结

理解 SQL Server 的编译与重编译机制有助于识别性能瓶颈。虽然执行计划重用能显著降低开销，但需警惕参数嗅探问题；而重编译虽消耗资源，却是应对数据结构和配置变化的必要手段。通过合理管理 sp_recompile 及监控缓存状态，可在性能与安全间找到最佳平衡点。

以上就是 SQL Server 编译与重编译机制及执行计划重用利弊的详细内容，更多关于 SQL Server 性能调优的资料请关注本站其它相关文章！