SQL Server 2019特定参数下查询运行时长突增问题咨询

问题背景 #

使用Microsoft SQL Server 2019，某存储过程针对特定参数的执行时长突然从几分钟飙升至4小时，近期未对该存储过程做任何修改，且其内部的SELECT查询单独运行也耗时极久。

排查过程 #

• 初步怀疑是参数嗅探问题，给最后一条SELECT语句添加OPTION(RECOMPILE)，前两次执行均在1分钟内完成，但第三次开始又恢复到4小时的耗时。
• 改用局部变量存储参数值，将WHERE条件改为使用变量后，查询稳定在1分钟内返回结果。

执行计划对比 #

• 低效计划：WHERE条件为WHERE PR.PRSId = 1，耗时4小时，执行计划中包含Key Lookup操作，这是主要性能瓶颈。
• 高效计划：WHERE条件为WHERE PR.PRSId = @PRSId，耗时1分钟，执行计划选择了更优的索引策略，避免了大量书签查找。

核心疑问 #

原本以为参数嗅探只发生在存储过程中，但本次是查询本身出现了类似问题，需要解释这一行为。

完整查询代码 #

DECLARE @SDVId INT = 3

IF OBJECT_ID('tempdb..#tmp_DSCF') IS NOT NULL
DROP TABLE #tmp_DSCF;

SELECT 
    SDSDDS.DataSourceId
,C.CategoryId
,F.[Code]
,PF.PFId
INTO
#tmp_DSCF
FROM 
dbo.tbl_SDV AS SDV WITH (NOLOCK) 
INNER JOIN dbo.tbl_SDSDDataSource AS SDSDDS WITH (NOLOCK) ON SDSDDS.SDVId = SDV.SDVId
INNER JOIN dbo.tbl_SDC AS SDC WITH (NOLOCK) ON SDC.SDVId = SDV.SDVId
INNER JOIN dbo.tbl_Category AS C WITH (NOLOCK) ON C.CategoryId = SDC.CategoryId
INNER JOIN [dbo].[fnt_SPAF](@SDVId) AS F ON (F.Category = C.Description OR F.Category = '*')
INNER JOIN dbo.tbl_PF AS PF WITH (NOLOCK) ON PF.Description = F.Filter AND PF.CategoryId = C.CategoryId
WHERE
SDV.SDVId = @SDVId;
        
CREATE CLUSTERED INDEX IX_tmp_DSCF ON #tmp_DSCF(PFId, CategoryId, DataSourceId);

 SELECT
   PR.PRId
  ,DSCF.Code AS AttributeName
  ,PFV.PFVId AS AttributeKey
  ,PFV.Value AS AttributeValue
 FROM 
  #tmp_DSCF AS DSCF
  INNER JOIN [dbo].[tbl_PR] AS PR WITH (NOLOCK) ON PR.DataSourceId = DSCF.DataSourceId AND PR.CategoryId = DSCF.CategoryId
  INNER JOIN [dbo].[fnt_PPAtDate](GETDATE()) AS F ON F.ProductId = PR.ProductId AND F.PFId = DSCF.PFId
  INNER JOIN [dbo].[tbl_PFV] AS PFV WITH (NOLOCK) ON PFV.PFId = F.PFId AND PFV.PFVId = F.PFVId
 WHERE 
  PR.PRSId = 1
  AND PR.ProductId IS NOT NULL

问题解释 #

参数嗅探并非存储过程专属，任何会被SQL Server缓存执行计划的查询场景都可能触发，包括独立批处理查询、动态SQL等，核心原因是SQL Server会基于首次执行的参数值生成执行计划并缓存，后续复用计划时若数据分布与首次差异较大，就会出现性能问题。

常量条件为何导致慢查询？ #

当查询中使用PR.PRSId = 1这类常量时，SQL Server会基于该常量对应的统计信息生成执行计划并缓存。如果PRSId=1对应的数据集分布极端（比如数据量远大于其他值，或者远小于），这个计划只适合该特定值的场景。后续即使是相同查询，只要实际处理的数据分布不同，复用这个缓存计划就会导致低效执行——比如你遇到的Key Lookup，就是计划选择了不适合当前数据量的索引策略，引发大量书签查找。

变量条件为何解决问题？ #

使用局部变量时，SQL Server在编译阶段无法获取变量的具体值，会采用统计信息的默认密度来估算行数，生成一个更通用的执行计划，不会绑定到某个特定参数值上，从而绕过了参数嗅探的影响，确保计划适配大多数数据分布场景。

OPTION(RECOMPILE)为何前两次有效？ #

OPTION(RECOMPILE)会强制每次执行都重新生成执行计划，但如果查询所在上下文存在其他干扰因素（比如存储过程的参数上下文、统计信息过时），或者SQL Server的缓存机制出现异常，都可能导致后续执行时重新生成的计划又选择了低效策略。另外，前两次执行时数据分布可能刚好适配编译出的计划，第三次时数据变化或统计信息偏差，导致计划失效。

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