MSSQL带变量查询性能异常问题求助

当你使用参数化查询时，SQL Server会在第一次执行时根据传入的参数值生成查询计划并缓存，后续执行相同语句时复用该计划。但如果首次传入的参数对应的数据分布和常用参数差异较大，或者优化器无法准确评估参数对应的行数量，就会生成非最优计划——比如你的场景中，带变量时生成的计划未走索引，而常量查询时优化器能直接判断走索引更高效。

添加OPTION (RECOMPILE)后，SQL Server会每次执行都重新生成针对当前参数值的最优计划，所以能快速完成，但代价是每次都要编译计划，高频查询可能产生额外开销。

• 强制指定索引：在查询的表名后添加索引提示，直接告诉优化器使用指定索引，示例：

  SELECT * FROM your_table WITH (INDEX(your_index_name)) WHERE your_column = ?
  

  注意：若后续索引被删除或失效，查询会报错；若数据分布变化导致索引不再最优，无法自动调整。
• 更新统计信息：执行UPDATE STATISTICS your_table让SQL Server获取最新数据分布，优化器能更准确判断是否走索引。也可开启自动更新统计信息，确保数据变化时统计信息同步更新。
• 局部变量中转参数：在查询内部将参数赋值给局部变量后再使用，示例：

  DECLARE @local_var INT = ?;
  SELECT * FROM your_table WHERE your_column = @local_var;
  

  这种方式会让SQL Server基于表统计信息的平均值生成计划，避免依赖首次传入的参数值生成缓存计划，适合数据分布相对均匀的场景。
• 使用OPTIMIZE FOR UNKNOWN：在查询末尾添加OPTION (OPTIMIZE FOR UNKNOWN)，让优化器基于数据通用分布生成计划，而非针对某个具体参数值，示例：

  SELECT * FROM your_table WHERE your_column = ? OPTION (OPTIMIZE FOR UNKNOWN)
  

  此方式开销远低于RECOMPILE，同时能避免参数嗅探问题。
• 检查索引有效性：确认索引是否最优——比如索引列是否是查询过滤的核心条件，是否为覆盖索引（包含查询所需所有字段，避免回表）；若索引选择性太低（大量重复值），优化器可能选择全表扫描而非索引。

会的。JPA/Hibernate默认使用参数化查询，同样会触发SQL Server的查询计划缓存，因此也可能遇到参数嗅探导致的执行计划不合理问题。

对应的解决思路和JdbcTemplate一致：

• 执行原生SQL时，可添加OPTION (OPTIMIZE FOR UNKNOWN)或索引提示；
• 使用HQL时，可通过@QueryHint调整查询计划缓存策略，比如禁用特定查询的计划缓存：

  @Query(value = "SELECT t FROM YourEntity t WHERE t.id = ?1",
         hints = @QueryHint(name = "org.hibernate.query.plan_cache_max_size", value = "0"))
  

• 同样可通过更新统计信息、优化索引等数据库层面的操作解决问题。

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