怎样在SQL存储过程中通过提示RECOMPILE解决多变参数-SQL-PHP中文网

WITH RECOMPILE 每次执行都强制重编译整个存储过程，适用于参数分布极不均匀场景，但会增加CPU开销；EXEC proc_name WITH RECOMPILE 仅影响当次调用，更灵活；OPTION (RECOMPILE) 作用于单条语句，必须显式加在SELECT/UPDATE/DELETE后；sp_recompile仅打标记，下次执行才重编译。

怎样在sql存储过程中通过提示recompile解决多变参数

WITH RECOMPILE 是每次执行都重编译，不是“加个提示就生效”

很多人以为在 CREATE PROCEDURE 里写上 WITH RECOMPILE 就能自动适配各种参数组合，其实它只做一件事：每次调用该存储过程时，强制丢弃缓存的执行计划，从头生成新的。这适合参数分布极不均匀的场景（比如某值查出 10 行，另一值查出 10 万行），但代价是每次都要消耗 CPU 去优化计划。

常见错误现象包括：

误以为加了 WITH RECOMPILE 就能解决所有慢查询——其实只是把“用错计划”的问题换成了“反复编译”的开销
在高并发 OLTP 场景下滥用，导致 CPU 突增、编译队列阻塞
和 OPTION (RECOMPILE) 混用，后者作用于单条语句，前者作用于整个过程，粒度不同但效果叠加时容易误判根源

EXEC proc_name WITH RECOMPILE 是更灵活的按需触发方式

比起在创建时硬编码 WITH RECOMPILE，运行时临时加更可控。它只影响当次调用，不影响其他会话或后续执行，适合调试或偶发性参数倾斜。

使用场景举例：

DBA 手动排查某次慢执行，怀疑是参数嗅探导致计划失优，直接 EXEC usp_GetOrders @CustomerID = 999999 WITH RECOMPILE
应用层逻辑判断到当前参数属于“稀有值”，主动带上 WITH RECOMPILE 避免复用旧计划
和 sp_recompile 配合：先用 sp_recompile 标记过程，再首次调用时自然触发重编译，比全量加 WITH RECOMPILE 更轻量

WHERE ... OR @param IS NULL 类型逻辑必须配合 OPTION(RECOMPILE)

这类写法天然破坏参数嗅探的稳定性，SQL Server 会基于第一次传入的参数值（比如 @param = NULL）生成一个全表扫描计划，后续即使传入具体值也沿用该计划，性能断崖式下跌。

正确做法是在对应 SQL 语句末尾显式加 OPTION (RECOMPILE)：

PubMedQA

PubMedQA是一个专为生物医学研究问题回答任务构建的数据集。

下载

SELECT * FROM Orders 
WHERE CustomerID = @CustomerID 
   OR @CustomerID IS NULL
OPTION (RECOMPILE);

注意点：

OPTION (RECOMPILE) 只作用于它所在的那条语句，不是整个存储过程
不能放在 IF 分支外层，必须嵌在实际执行的 SELECT/UPDATE/DELETE 语句后
如果过程里有多条动态拼接的语句，每条都需要单独加，否则只有第一条受益

sp_recompile 不是重编译命令，而是“打标记”操作

sp_recompile 不会立刻编译，只是给对象（如存储过程、表）打个“下次执行时请重来”的标记。真正编译发生在下一次调用时，这对生产环境更友好——避开高峰期，也避免游标遍历时锁表。

批量处理用户存储过程的典型写法：

DECLARE @proc_name NVARCHAR(256)
DECLARE proc_cursor CURSOR FOR 
  SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) 
  FROM sys.procedures 
  WHERE is_ms_shipped = 0
<p>OPEN proc_cursor
FETCH NEXT FROM proc_cursor INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_recompile @objname = @proc_name
FETCH NEXT FROM proc_cursor INTO @proc_name
END
CLOSE proc_cursor
DEALLOCATE proc_cursor</p>

容易踩的坑：