带日期参数的存储过程与查询运行缓慢问题排查咨询

兄弟，你这情况我碰到过好多次，核心问题大概率是参数嗅探（Parameter Sniffing），再加上可能的统计信息没跟上，导致存储过程和直接查询用了完全不同的执行计划，速度差了十万八千里。咱们一步步拆解解决：

一、先搞定参数嗅探的坑 #

你直接跑查询时，SQL Server会根据你写的具体日期（比如'2017-09-30'）生成最贴合这个数据分布的执行计划；但存储过程第一次执行后，会把当时的执行计划缓存起来，后面再传其他参数（比如2018的日期）时，就直接复用这个旧计划——如果两个日期范围的数据分布差很多，那旧计划肯定就不适用了，直接卡爆。

给你几个实用的解决办法：

• 给查询加OPTION (RECOMPILE)：在存储过程里的查询语句末尾加上这个选项，让SQL Server每次执行都重新生成适合当前参数的计划。比如：

  SELECT 你要的字段 FROM 你的表 
  WHERE 日期字段 BETWEEN @起始日期 AND @结束日期
  OPTION (RECOMPILE);
  

  这个方法对参数差异大的场景特别管用，唯一的小代价是每次都要编译计划，对于小查询来说完全可以忽略。
• 用局部变量“屏蔽”参数：在存储过程内部把传入的参数赋值给局部变量，再用局部变量查，这样SQL Server就不会嗅探原始参数了：

  CREATE PROCEDURE 你的存储过程名 (@起始日期 DATE, @结束日期 DATE)
  AS
  BEGIN
      DECLARE @本地起始 DATE = @起始日期;
      DECLARE @本地结束 DATE = @结束日期;
      SELECT 你要的字段 FROM 你的表 
      WHERE 日期字段 BETWEEN @本地起始 AND @本地结束;
  END
  

• 创建存储过程时加WITH RECOMPILE：如果这个存储过程每次传的参数差异都极大，干脆直接让它每次执行都重新编译：

  CREATE PROCEDURE 你的存储过程名 (@起始日期 DATE, @结束日期 DATE)
  WITH RECOMPILE
  AS
  BEGIN
      SELECT 你要的字段 FROM 你的表 
      WHERE 日期字段 BETWEEN @起始日期 AND @结束日期;
  END
  

二、检查统计信息是不是过时了 #

2018年数据更少反而更慢，这十有八九是表的统计信息没更新，SQL Server根本不知道2018年的数据量少，还在用旧的统计信息生成执行计划，比如误以为数据很多，选了效率极低的全表扫描或者哈希连接。

赶紧更一下统计信息：

• 更新整个数据库的统计信息：

  UPDATE STATISTICS 你的数据库名;
  

• 针对你的业务表更新，带上FULLSCAN确保统计信息准确：

  UPDATE STATISTICS 你的表名 WITH FULLSCAN;
  

三、看看索引是不是没发挥作用 #

不管是直接查还是调用存储过程，都得确认日期字段上有没有合适的索引，或者索引是不是碎了。

你可以在SSMS里打开执行计划（按Ctrl+M再跑查询），看看是不是走了索引扫描而不是索引查找。如果日期字段上没索引，赶紧建一个覆盖索引（包含你查询需要的其他字段，避免回表）：

CREATE NONCLUSTERED INDEX IX_你的表_日期字段 ON 你的表名(日期字段)
INCLUDE (字段1, 字段2, ...); -- 把你SELECT里的其他字段都加进来

另外检查一下索引碎片：

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('你的表名'), NULL, NULL, 'DETAILED');

如果碎片率超过30%，就重建索引；10%-30%的话 reorganize就行：

ALTER INDEX IX_你的表_日期字段 ON 你的表名 REBUILD;
-- 或者碎片少的话用这个
ALTER INDEX IX_你的表_日期字段 ON 你的表名 REORGANIZE;

四、额外要排查的小细节 #

• 看看存储过程里有没有多余的逻辑：比如有没有游标、循环，或者有没有未提交的事务，这些都可能拖慢执行速度。
• 对比执行计划：把直接查询的执行计划和存储过程的执行计划拉出来对比，看看哪里不一样——比如是不是用了不同的连接方式，或者有没有哪个步骤扫描了全表，一眼就能看出问题。

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