SQL Server 2014：sp_executesql执行查询远快于普通查询的异常问题

Hey folks, I ran into a super weird issue debugging someone else's SSRS 2014 report query and wanted to break down what was happening and how to tackle it:

问题场景 #

• 从SSRS直接执行查询时，返回13730行，约15秒完成
• 用SQL Profiler捕获到相同查询并在SSMS中执行时，却只返回11940行（同样约15秒完成）
• 更离谱的是，把sp_executesql调用改写成标准参数声明形式后，查询直接陷入无限等待（等待类型为CXPACKET），15分钟后只能手动终止——而且两种写法的执行计划差异极大

示例1：SSRS与SSMS执行结果不同，但均快速完成 #

exec sp_executesql N' CREATE TABLE #tmpTable ( field1 varchar(250), field2 int, field3 varchar(max) ) Insert into #tmpTable SELECT somestuff as field1, someotherstuff as field2, morestuff as field3 FROM RealTable WHERE somestuff = ''stuff that matters for the first query'' AND otherstuff = @param1 Insert into #tmpTable SELECT somestuff as field1, someotherstuff as field2, morestuff as field3 FROM RealTable2 WHERE somestuff = ''stuff that matters for the second query'' AND otherstuff = @param2 SELECT * FROM #tmpTable ',N'@param1 nvarchar(4000),@param2 nvarchar(4000)',@param1=N'value1',@param2=N'value2'

示例2：执行陷入无限等待，需手动终止 #

Declare @param1 nvarchar(4000), @param2 nvarchar(4000) 
SELECT @param1 = 'value1', @param2 = 'value2' 
CREATE TABLE #tmpTable ( field1 varchar(250), field2 int, field3 varchar(max) ) 
Insert into #tmpTable SELECT somestuff as field1, someotherstuff as field2, morestuff as field3 FROM RealTable WHERE somestuff = 'stuff that matters for the first query' AND otherstuff = @param1 
Insert into #tmpTable SELECT somestuff as field1, someotherstuff as field2, morestuff as field3 FROM RealTable2 WHERE somestuff = 'stuff that matters for the second query' AND otherstuff = @param2 
SELECT * FROM #tmpTable

问题根源分析 #

1. 为什么结果行数不一致？ #

这几乎肯定是参数嗅探+会话SET选项不匹配共同导致的：

• SSRS执行查询时会使用一套默认的SET选项（比如ANSI_NULLS ON、QUOTED_IDENTIFIER ON、ANSI_WARNINGS ON）。如果你的SSMS会话启用了不同的SET选项，SQL Server会生成不同的执行计划——哪怕是相同的查询和参数，这会导致过滤逻辑的执行效果出现差异（比如NULL值处理、字符串比较规则）。
• 务必确认SSMS中使用的参数类型和sp_executesql完全一致：示例里用的是nvarchar(4000)参数，别不小心在手动测试时用了varchar，隐式转换可能会打乱过滤结果。

2. 为什么普通参数声明会导致无限等待？ #

CXPACKET等待通常和并行执行相关，但这里的无限等待本质是参数嗅探方式差异导致的糟糕执行计划：

• 用DECLARE先定义参数再赋值时，SQL Server会在参数被赋值前就编译执行计划，只能用统计信息里的平均数据分布来猜测，这对数据分布极不均衡的表（比如某个参数值对应10行数据，另一个对应100万行）来说，很容易生成极差的执行计划。
• 而sp_executesql会带着实际传入的参数值编译计划，生成的计划更贴合当前数据分布。如果DECLARE方式生成的计划选了错误的执行策略（比如在超大数据集上用嵌套循环连接），并行执行的线程就会陷入协调死锁，导致无限等待。

解决建议 #

• 对齐SSRS与SSMS的会话设置：通过Profiler捕获SSRS执行的SET语句（比如SET ANSI_NULLS ON这类），先在SSMS中执行这些语句，再测试查询，这样能让执行计划匹配，解决行数不一致的问题。
• 修复慢查询的参数嗅探问题：
  • 在查询末尾添加OPTION (RECOMPILE)，强制SQL Server每次都用实际参数值重新生成计划（适合数据分布波动大的场景）。
  • 使用OPTION (OPTIMIZE FOR (@param1 = N'value1', @param2 = N'value2'))，指定SQL Server针对你的特定参数值优化执行计划。
  • 执行UPDATE STATISTICS RealTable WITH FULLSCAN;和UPDATE STATISTICS RealTable2 WITH FULLSCAN;更新表统计信息——过期的统计信息会导致SQL Server生成错误的执行计划。
• 排查CXPACKET等待：
  • 临时在查询末尾添加OPTION (MAXDOP 1)禁用并行执行，看看等待是否消失（这是快速测试方案，不是永久解决办法）。
  • 检查服务器的MAXDOP设置，如果设置过高（比如等于核心数），可能会导致并行线程竞争。

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