为何日期作为存储过程输入参数会让大表INNER JOIN查询耗时激增？

实测故障场景还原 #

• 测试基础：开发环境部署了包含MyTable、MyTable2两张单表行数500万以上的INNER JOIN关联逻辑的存储过程
• 对照测试1：存储过程将@from、@to设为外部输入参数接收时，关联查询耗时超过3分钟
• 对照测试2：注释掉外部输入参数定义，在存储过程内部直接声明同取值的日期变量运行相同逻辑，查询可在1秒内稳定完成
• 问题范围排除：移除存储过程中的INNER JOIN逻辑后，两种写法的运行速度无明显差异，确认问题出在INNER JOIN与外部输入参数的组合场景
• sp_whoisactive采集的性能量化对比：
  • 带外部输入参数执行：总耗时2分41.82秒，CPU累计占用161821，逻辑读达10713290次
  • 内部声明变量执行：总耗时0.026毫秒，CPU累计占用97，逻辑读仅53次
• 临时修复效果：为MyTable2补充包含DATETIME、ID列的非聚集索引后，两种写法的运行性能达到一致，但两类参数形式的本质差异疑问仍未消除

两类参数形式的核心本质差异 #

1. 执行计划编译阶段的参数值可感知性不同
   • 存储过程的外部输入参数，在执行计划编译/缓存阶段，SQL Server优化器会直接嗅探（提取）当次传入的实际参数值，结合表的统计信息生成适配该参数取值的执行计划；该计划一旦进入缓存，后续执行相同存储过程时会优先复用，不会重新编译。
   • 存储过程内部直接声明的局部变量，在执行计划编译阶段，优化器无法提前获取变量的实际赋值，只会根据列的数据类型、整体数据密度做通用估算，生成适配大多数场景的中性执行计划，不会被特定参数值绑定。
2. INNER JOIN场景下差异会被指数级放大
   • 单表查询场景下，即使执行计划选择有偏差（比如选了全表扫描而非索引查找），针对500万行规模的表，扫描耗时通常在数秒内，两类写法的感知差异很小。
   • 双表INNER JOIN场景下，优化器需要同时决策驱动表选择、连接算法（嵌套循环/哈希匹配/合并连接）、索引访问路径三个核心维度：如果第一次执行存储过程时传入的日期范围很小，优化器会生成嵌套循环+逐行索引查找的计划；当后续传入覆盖大时间范围的参数时，复用的计划会强制走逐行查找路径，累计产生千万级的逻辑读、百万级的CPU消耗，直接出现数量级的性能下降。
   • 为MyTable2补充对应列的非聚集索引后，不管优化器选择哪种执行路径，都有足够的索引入口可以减少数据扫描范围，因此两类写法的性能会趋于一致，相当于给执行计划的选择加上了安全兜底。
3. 观测到的内部声明变量版本逻辑读仅53次的现象，本质是因为优化器没有被特定小参数值的计划绑架，自动选择了匹配数据规模的最优连接路径，避免了逐行扫描的性能陷阱。

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