如何避免ad hoc query（即席查询）出现recompile（重编译）问题

避免参数修改触发重编译的可行方案 #

你之前添加的hint无效的核心原因是：你每次修改@o的赋值字面量时，整个Ad-Hoc批处理的文本哈希就会发生变化，SQL Server会将其识别为全新的查询，自然不会复用之前的缓存计划，和参数嗅探没有关联，所以针对参数嗅探的hint不对症。无需封装为存储过程的可落地方案如下：

• 方案1（优先推荐）：使用sp_executesql显式参数化查询
  仅将参数值作为入参传入，查询结构固定不变，修改参数值不会改变批处理文本，即可复用缓存计划：

  set statistics time on;
  
  DECLARE @o bigint = 3374707; -- 仅需要修改此处的参数值即可
  EXEC sp_executesql 
      N'select * from ComplexTableValuedFunction(@o)',
      N'@o bigint',
      @o = @o;
  
  set statistics time off;
  

  该方案无需修改数据库全局配置，仅对当前查询生效，风险最低。
• 方案2：开启数据库级强制参数化
  开启后SQL Server会自动将Ad-Hoc查询中的字面量替换为参数，只要查询结构不变，参数值不同也会复用缓存计划：

  -- 替换为你的实际数据库名
  ALTER DATABASE [你的数据库名] SET PARAMETERIZATION FORCED WITH NO_WAIT;
  

  如果不想全局开启，也可以通过创建计划指南的方式，仅对当前查询强制参数化。
• 方案3：开启服务器级即席工作负载优化
  如果服务器存在大量类似的重复结构Ad-Hoc查询，开启该配置可以减少缓存占用，同结构查询第二次执行即可复用计划：

  sp_configure 'show advanced options',1;
  RECONFIGURE;
  sp_configure 'optimize for ad hoc workloads',1;
  RECONFIGURE;
  

  注意该方案第一次执行同结构查询仍会触发编译，第二次才会复用缓存。

附带问题解答 #

SET STATISTICS TIME 仅统计外层批处理的编译耗时，不会统计调用的表值函数内部的编译耗时，你遇到的7秒编译耗时实际是表值函数的编译开销，属于执行阶段触发的内部编译，所以不会被外层的STATISTICS TIME捕获。而SQL Sentry是从底层性能计数器抓取全链路的编译耗时，所以可以统计到完整的时长。

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