万行存储过程的性能问题和维护风险主要源于结构失控,应按数据生命周期拆分子过程、分离强分支逻辑、用表值参数传参、主过程仅保留驱动逻辑,并持续监控执行计划重编译和逻辑读异常。

直接拆,别重写。万行存储过程的性能问题和维护风险,90% 来自结构失控,不是逻辑本身错——它已经跑通多年,改业务语句反而容易引入回归缺陷;真正该动的是调用链、临时对象生命周期和执行确定性边界。
为什么大过程会频繁重编译
SQL Server 不是“嫌你代码长”,而是当一个存储过程中引用对象超过 50+(表/视图/函数/临时表)、嵌套条件分支过深(IF + CASE + 动态拼接混用)、或反复 CREATE/DROP #temp 时,优化器会判定该执行计划“太脆弱”,主动放弃复用。这不是缓存被踢出,是每次执行都走完整优化流程。
- 查证方式:
sys.dm_exec_query_stats中该过程的plan_generation_num远大于execution_count(比如 127 vs 8) - 执行计划 XML 里反复出现
Reason="SchemaChanged",但实际表结构没变——本质是内部依赖链太长,让 SQL Server 误判为不稳定 - 过程里用
SELECT INTO #tmp后立刻CREATE INDEX,这种模式几乎必触发重编译,因为索引创建会改变临时对象元数据
按数据生命周期切分子过程
拆分不是 Ctrl+X/Ctrl+V,而是识别“哪段逻辑只负责准备中间结果”“哪段只做校验”“哪段纯输出”。关键原则:输入确定、输出明确、不跨事务边界、不共享未声明的本地临时表。
- 把多表 JOIN 汇总、窗口计算等“准备中间结果”的逻辑抽成独立子过程,用
@tvp或只读全局临时表(##global_tmp)传递,避免主过程反复建删#local_tmp - 把强分支逻辑分离,例如按
@mode = 'EXPORT'/'VALIDATE'分成usp_export_data和usp_validate_data,而不是在一个过程里堆满IF...ELSE IF... - 禁止子过程之间互相调用形成环路;主过程只调用子过程,子过程不反向调用主过程或其他兄弟过程
- 所有子过程开头必须加
SET NOCOUNT ON,否则结果集计数干扰缓存稳定性
临时表传参还是表值参数(TVP)
传中间结果时,别无脑选 #temp。本地临时表(#t)无法被子过程访问;全局临时表(##t)能跨过程但需手动清理、有并发风险;而表值参数(@tvp)是唯一安全、可预测、免清理的选择——前提是类型已预定义且字段对齐。
-
CREATE TYPE dbo.BatchUpdateTvp AS TABLE (id INT, status VARCHAR(20))必须提前建好,字段名、顺序、可空性(INT NULL)必须与 Go/.NET 中 struct 完全一致 - Go 调用时必须用
mssql.TVP包装切片,漏掉TypeName字段会报错The table type parameter '@tvp' must have a valid type name - 空切片允许,但子过程中需显式判断
IF NOT EXISTS (SELECT 1 FROM @tvp) RETURN,防止后续逻辑误操作
主过程只剩驱动逻辑,其余全下沉
重构后主过程应极薄:只做参数校验、调用子过程、错误聚合、统一事务控制(如有)。所有具体 SQL、JOIN、UPDATE、MERGE 都下沉到子过程中,确保每段逻辑可单独测试、可独立缓存、可灰度发布。
- 主过程开头加
SET XACT_ABORT ON,确保某子过程失败时整个事务回滚,不残留半截状态 - 禁用
sp_前缀(如sp_process_orders),改用usp_或业务前缀(ord_process_orders),避免 SQL Server 先去master库搜索 - 所有对象引用补全 Schema,例如
UPDATE dbo.Orders,不是UPDATE Orders;缺失 Schema 会导致每次执行都触发默认 Schema 查找和潜在重新编译 - 动态表名必须白名单校验:
CASE WHEN @table_name IN ('Orders','Customers') THEN @table_name ELSE THROW 50000, 'Invalid table name', 1
最易被忽略的一点:拆分后每个子过程仍要独立检查 sys.dm_exec_query_stats 的 total_logical_reads / execution_count 比值——如果某个子过程该比值异常高,说明它内部仍有隐式转换、缺失索引或 WHERE 子句用了函数,得继续深挖,不能以为“拆了就完事”。

















