为何存储过程中推荐将参数赋值给变量？性能与参数嗅探解析

核心关联：破解参数嗅探问题 #

参数嗅探是SQL Server等数据库编译存储过程时的常见行为：它会用存储过程第一次执行时传入的参数值生成执行计划，后续所有调用都会复用这个计划。如果第一次的参数是极端场景（比如返回1行数据的用户ID，或是返回100万行的用户ID），后续用其他参数执行时，这个“量身定制”的计划就完全不匹配，直接导致查询耗时飙升。

把参数赋值给内部变量后，数据库无法直接获取变量的实际值来生成针对性计划，转而会基于表的统计信息平均值生成一个更通用的执行计划——这个计划不会偏向某一种极端场景，能适配大多数参数的查询需求，从根源上避免参数嗅探带来的性能波动。

举个直观的代码对比：

原始写法（易触发参数嗅探） #

CREATE PROCEDURE GetUserOrders @UserId INT
AS
BEGIN
  SELECT OrderId, OrderDate, TotalAmount 
  FROM Orders 
  WHERE UserId = @UserId;
END

改进写法（规避参数嗅探） #

CREATE PROCEDURE GetUserOrders @UserId INT
AS
BEGIN
  DECLARE @LocalUserId INT = @UserId;
  SELECT OrderId, OrderDate, TotalAmount 
  FROM Orders 
  WHERE UserId = @LocalUserId;
END

性能提升的底层原理 #

1. 避免执行计划不匹配：参数嗅探导致的计划复用，会出现比如用适合小数据集的嵌套循环去扫描百万行数据，或是用适合大数据集的哈希连接去查询几行数据——这两种情况都会极大浪费资源。用变量生成的通用计划，能在绝大多数场景下保持稳定的执行效率。
2. 动态评估数据分布：使用变量时，数据库在执行阶段会根据当前变量的实际值，结合最新的统计信息重新调整执行逻辑（比如选择合适的索引）；而直接用参数的话，编译阶段生成的计划就固定了，哪怕后续表的数据分布发生变化，也不会自动更新。
3. 缓解参数敏感型计划问题：如果你的存储过程处理的参数覆盖范围极大（比如有的参数返回10行，有的返回100万行），直接用参数很容易陷入“参数敏感”陷阱——变量法能让计划更通用，减少这种极端情况的影响。

这么做的重要性 #

• 稳定性能：旧存储过程时快时慢的核心原因，大概率是参数嗅探导致的计划波动。改用变量后，查询耗时会更一致，不会突然出现耗时翻倍的情况。
• 低改造成本：不需要重构存储过程的业务逻辑，只需要加几行变量声明和赋值的代码，适合遗留系统的快速优化。
• 减少维护风险：避免后续因为参数取值变化导致执行计划失效，降低DBA后续的调优和排查成本。

注意事项 #

这种方法不是万能的：如果你的存储过程参数的取值范围很窄，或者每次执行的参数对应的数据集大小基本一致，直接用参数反而可能生成更精准的执行计划。所以要结合实际的业务数据分布来判断是否适用。

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