大表新增bit列后存储过程性能下降，执行SP_Recompile恢复的原理问询

1. ALTER TABLE新增列的后台执行逻辑 #

针对你场景中的大表新增无默认值bit类的操作，后台变化分为三类：

• 元数据即时更新：新增允许为NULL、无非易失性默认值的列属于轻量级元数据操作，仅会修改系统表中存储的表结构元数据（包括列定义、列偏移量、空值属性等），不会遍历全表修改现有数据页，2000万行级的大表也可以在毫秒级完成，不会产生大量IO开销。
• 统计信息标记失效：该操作会自动将该表关联的所有统计信息标记为过期状态，后续查询需要使用统计信息时才会触发自动更新。
• 执行计划标记待重编译：所有引用该表的已缓存执行计划都会被标记为「需要重新编译」，但不会立刻从计划缓存中清除。

2. 新增列导致存储过程性能劣化的原因 #

存储过程的执行效率依赖预编译生成的最优执行计划，而执行计划的准确性完全依赖表的结构元数据和统计信息，性能劣化通常由以下两种情况导致：

• 旧执行计划复用错误：虽然表结构变更后执行计划被标记为待重编译，但如果存储过程执行时传入的参数没有达到自动重编译的触发阈值，数据库会直接复用旧执行计划。旧执行计划的索引选择、列偏移计算、行数预估逻辑都是基于变更前的表结构生成，会出现索引命中失败、JOIN算法选择错误、行数预估偏差过大等问题，最终导致执行效率骤降。
• 过期统计信息生成错误计划：即使部分存储过程触发了自动重编译，由于表的统计信息已经过期，和当前表结构、实际数据分布不匹配，数据库基于错误统计信息生成的执行计划也会存在严重的逻辑缺陷，无法选择最优的执行路径。

3. SP_Recompile 'tableName'恢复性能的原理 #

SP_Recompile指定表名执行时，会强制清除所有引用该表的已缓存执行计划，同时触发该表关联的所有过期统计信息的全量更新。后续存储过程首次执行时，会基于最新的表结构和准确的统计信息重新生成最优执行计划，性能即可恢复正常。

内容的提问来源于stack exchange，提问作者joemac12