存储过程运行数周后执行过慢，无变更重编译后恢复正常求原因

这是典型的**参数嗅探（Parameter Sniffing）**导致的执行计划失效问题，是SQL Server存储过程运行一段时间后性能骤降的常见原因。

具体原因： #

• 存储过程首次执行时，SQL Server会根据传入的第一个@userId参数值生成最优执行计划并缓存。
• 如果第一次传入的是'0'（返回全量客户端数据），生成的执行计划是针对全表扫描的；后续传入普通userId（仅返回该代表的少量数据）时，SQL Server仍会复用这个全表扫描的计划，导致查询速度骤降。反之，如果首次传入的是普通userId，后续传'0'时会用小数据量的执行计划，同样会导致全量查询变慢。
• 执行无变更的ALTER PROCEDURE操作，会强制清空该存储过程的缓存执行计划，再次执行时SQL Server会根据当前传入的参数重新生成适配的最优计划，因此性能恢复正常。

针对性解决方案： #

• 添加RECOMPILE选项：在两个SELECT语句末尾加上OPTION (RECOMPILE)，让SQL Server每次执行都根据当前参数生成最优计划，适合数据量差异较大的场景。示例：

  SELECT 
       q.AccountNumber,
       ...(15 other fields)
  FROM 
      SRV_CustomCaptions cc
  INNER JOIN 
      MyTable q ON cc.Capt_Code = q.CategorieClient
  LEFT JOIN 
      TurnoverByType tt ON q.CompteClient = tt.Turn_AccountNumber 
  WHERE
      cc.Capt_Family = 'pers_type'
  OPTION (RECOMPILE)
  

• 拆分存储过程：将两种分支逻辑拆成两个独立的存储过程，分别处理@userId='0'和普通用户的查询，从根源避免执行计划冲突。
• 使用局部变量：在存储过程内部将外部参数@userId赋值给局部变量，后续查询用局部变量代替原参数，避免SQL Server直接嗅探外部参数生成固定计划。示例：

  BEGIN
      DECLARE @localUserId varchar(50) = @userId;
      
      IF(@localUserId = '0')
          SELECT ... WHERE cc.Capt_Family = 'pers_type'
      ELSE
          SELECT ... WHERE cc.Capt_Family = 'pers_type' AND q.CodeRepresentant=@localUserId
  END
  

• 手动清除特定缓存：如果不想修改存储过程，可执行DBCC FREEPROCCACHE (存储过程对象ID)清除该存储过程的缓存计划，但不建议频繁操作，避免影响其他缓存的执行计划。

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