运行多年的SQL存储过程突发超时 局部变量修复问题咨询

有一套持续迭代运行20年、作为Web应用后端支撑的SQL Server数据库，近期某功能模块突发调用超时故障，其余功能运行正常：

• 故障定位为某存储过程调用超时，数据库服务器层面未发现锁、阻塞、大量运行任务的异常
• 本地通过SQL Server Management Studio直接调用该存储过程可正常执行
• 修改存储过程逻辑：将传入参数赋值给局部变量，用局部变量替换原代码中所有入参引用位置后，故障完全恢复

故障前存储过程示例代码 #

ALTER PROCEDURE [dbo].[usp_sprocThatHasBeenFineForYears]
    -- Add the parameters for the stored procedure here
    @TheId int,
    @StartDate datetime

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT * FROM aTable WHERE TheId = @TheId AND StartDate = @StartDate
END

修改后存储过程示例代码 #

ALTER PROCEDURE [dbo].[usp_sprocThatHasBeenFineForYears]
    -- Add the parameters for the stored procedure here
    @TheId int,
    @StartDate datetime

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @LOCAL_TheId int = @TheId
    DECLARE @LOCAL_StartDate datetime = @StartDate

    SELECT * FROM aTable WHERE TheId = @LOCAL_TheId AND StartDate = @LOCAL_StartDate
END

1. 稳定运行十余年的存储过程为何无征兆突发超时 #

这个故障的核心原因是参数嗅探导致的缓存执行计划失配，之前十几年稳定运行、突发异常完全符合这类问题的典型特征：

• SQL Server默认会缓存存储过程第一次编译（或重编译）时生成的执行计划，后续所有调用都会复用这个缓存计划。编译时优化器会“嗅探”当时传入的参数值，结合字段统计信息生成对应最高效的计划。
• 之前十几年稳定运行，是因为缓存的执行计划是基于业务高频传入的正常参数生成的，适配绝大多数调用场景。触发突发异常的常见诱因包括：表数据量增长到统计信息自动更新阈值（默认超过20%行变动时自动触发）、数据库实例重启/服务重启清空了计划缓存、某次业务请求传入了一个数据分布极特殊的参数（比如某个@TheId对应表中99%的行，其余id仅对应几行）触发了存储过程重编译，这次重编译刚好基于极端参数生成了执行计划，这个计划对极端参数是高效的，但对业务侧99%的正常参数来说效率极差，就会出现大面积超时。
• 本地SSMS调用正常，是因为SSMS的连接默认SET配置（比如ARITHABORT默认值为ON，和普通应用连接的默认配置不一致）不会复用应用连接池缓存的坏执行计划，会单独编译生成适配当前传入参数的执行计划，所以本地跑速度正常，同时服务器层面也查不到锁、阻塞问题——本质是执行计划选择错误导致的语句本身执行效率极低，不是资源抢占或锁问题。
• 用局部变量替换入参就能修复，是因为SQL Server编译时不会嗅探局部变量的具体值，只会基于字段统计信息的平均分布生成通用性更强的执行计划，绕开了针对极端参数生成的坏计划的影响。

2. 如何排查其他存在同类超时隐患的存储过程 #

可以按以下步骤批量排查，无需等用户反馈故障再处理：

• 从系统动态管理视图拉取历史执行统计：查询sys.dm_exec_procedure_stats关联sys.dm_exec_query_stats，筛选出近1-3个月内单次执行时长、逻辑读、CPU消耗波动方差超过10倍的存储过程，这类对象是计划失配的高发对象。
• 校验字段数据分布：对筛选出的存储过程，逐个确认其WHERE条件中用到的过滤字段是否存在数据倾斜（比如某个字段值占全表数据的80%以上，其余值占比极低），存在倾斜字段的存储过程优先级最高。
• 多参数场景验证执行计划：对高风险存储过程，分别传入高频业务参数、极端低频参数测试执行，对比实际执行计划中预估行数和实际行数的偏差，如果某组参数下预估行数和实际行数差10倍以上，甚至出现大量Key Lookup、错误的索引选择，就说明存在参数嗅探隐患。
• 检查现有缓存计划：遍历计划缓存中存储过程的执行计划，找不同入参下执行效率差异巨大、计划本身对参数敏感度极高的对象，提前标记处理。
• 重点筛查老系统中从未做过计划优化、对应表数据量比上线时增长10倍以上的存储过程，这类对象的统计信息和初始部署时差异极大，最容易出现计划突变。

3. 开发、运维层面如何防范此类问题复发 #

开发侧防控 #

• 对过滤字段存在明显数据倾斜的存储过程，统一采用局部变量中转入参的写法，或者根据业务场景明确使用查询提示：如果存储过程本身执行快、数据量不大可以加OPTION(RECOMPILE)让每次执行都编译生成适配当前参数的计划；如果明确知道绝大多数请求都对应某类参数，可以加OPTION(OPTIMIZE FOR (@参数= 典型高频值))固定生成适配高频场景的计划。
• 存储过程编写时禁止在WHERE条件中对入参做函数运算、隐式类型转换，这类写法会进一步放大参数嗅探带来的计划偏差。
• 存储过程上线前的测试不能只测单一场景参数，必须覆盖高频值、极端低频值两类参数，确认执行计划稳定、执行效率符合预期再上线。

运维侧防控 #

• 建立核心存储过程的执行基线：记录每个核心存储过程正常情况下的平均执行时长、逻辑读、CPU消耗，配置阈值告警，一旦指标突增第一时间介入，不用等用户反馈。
• 优化统计信息更新策略：不要完全依赖SQL Server默认的自动统计信息更新，对千万级以上的核心大表配置定期手动更新统计信息的任务，采用足够的采样率保证统计信息和实际数据分布一致，避免优化器基于过时的统计信息生成错误计划。
• 定期巡检计划缓存：每周扫描一次缓存中的执行计划，清理那些预估行数和实际行数偏差极大、资源消耗异常的坏计划，避免单个坏计划影响全量业务请求。
• 对P0级核心业务的存储过程，可以通过计划指南固定适配通用场景的执行计划，避免统计信息更新、实例重启、异常参数传入导致的计划突变。

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