如何在SQL Server 2022存储过程中利用PSP优化功能-SQL-PHP中文网

OPTIMIZE FOR UNKNOWN 会禁用 PSP，因其强制优化器忽略实际参数值、按平均基数估算，与 PSP “依参数值选择不同计划变体”的机制根本冲突；使用后同一存储过程仅复用单个 plan_handle，且查询存储中对应 query_id 仅有一个 plan_id。

如何在sql server 2022存储过程中利用psp优化功能

SQL Server 2022 的参数敏感计划（PSP）不是开关，也不能在存储过程中显式启用——它由优化器自动触发，前提是满足统计信息、谓词结构和兼容级别等硬性条件；强行加 OPTIMIZE FOR UNKNOWN 或 RECOMPILE 反而会关闭 PSP。

为什么存储过程里写 OPTIMIZE FOR UNKNOWN 会禁用 PSP？

OPTIMIZE FOR UNKNOWN

告诉优化器完全忽略运行时参数值，只按平均基数估算，这与 PSP “根据实际参数值选择不同计划变体”的设计目标直接冲突。一旦使用该提示，SQL Server 就不会为同一语句生成多个 plan_handle，PSP 失效。常见错误现象： - 同一存储过程在不同参数下始终复用同一个执行计划 - sys.dm_exec_procedure_stats 中只看到一个 plan_handle，即使参数差异极大（如 @mode = 'ALL' vs @mode = 'TOP10'） - 查询存储中对应语句的 query_id 下只存在一个 plan_id

建议直接删掉所有 OPTIMIZE FOR UNKNOWN，让优化器自由决策。

如何验证 PSP 是否已在你的存储过程中生效？

关键看是否出现“多计划共存”：同一 object_id 对应多个不同 plan_handle，且这些计划被实际复用。执行以下查询：

SELECT 
  p.plan_handle,
  p.last_execution_time,
  q.query_plan
FROM sys.dm_exec_procedure_stats p
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) q
WHERE p.object_id = OBJECT_ID('YourProc')
ORDER BY p.last_execution_time DESC;

若返回多行，且 query_plan 内容明显不同（例如一个含 Index Seek，另一个含 Table Scan），说明 PSP 已起作用。注意：必须确保数据库兼容级别为 160（SQL Server 2022 默认值），否则 PSP 不激活： ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 160

哪些写法会让 PSP 失效或无法触发？

PSP 仅对“参数化谓词”敏感，且有严格限制： - 谓词必须是单列、等值或范围比较（如 WHERE CustomerID = @cid），不支持函数包裹（如 WHERE UPPER(Name) = @name） - 同一语句最多只评估前三个符合条件的谓词，多余部分不参与 PSP 分支判断 - 含本地临时表（#t）或表变量（@t）的语句，因重编译频繁，PSP 变体可能被快速淘汰，观察窗口很短 - 使用 sp_executesql 动态拼接 SQL 时，若参数未正确传入（如字符串拼接而非参数化），PSP 完全不介入

最易被忽略的一点：统计信息过期。PSP 依赖直方图识别数据分布偏斜，若 UPDATE STATISTICS 长期未跑，优化器无法判断“哪些参数值会导致基数剧变”，自然不会生成变体计划。

PSP 的生效不是靠你“做什么”，而是靠你不做什么——不干扰参数嗅探、不锁死统计假设、不人为压制计划缓存。真正要花力气的地方，是清理旧提示、校准统计信息、检查谓词写法。