SQL Server sp_ExecuteSQL和执行计划

Question

我有一个查询，在SQL Server管理工具中运行非常快，但在使用sp_ExecuteSQL运行时非常慢。

这是与执行计划缓存没有在使用sp_ExecuteSQL运行时发生有关吗？

- AJM

---

我想知道“sp_executesql不缓存计划”的神话何时才会消失——请阅读《动态SQL的诅咒和祝福》（http://www.sommarskog.se/dynamic_sql.html）。 - OMG Ponies

@OMG Ponies - sp_ExecuteSQL 是否存在参数嗅探问题？请确认。 - JNK

@JNK：自从遇到这种情况后，我已经默认加入了反参数嗅探功能。 - OMG Ponies

@JNK - 它缓存计划并重复使用，所以是的。 - Martin Smith

Answer 1

不可以。

您可以使用以下查询来查看两个执行计划并进行比较。

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%Some unique string in your query%' 
                                          and attribute='set_options'

sp_executesql 版本将具有 "prepared" 的 objtype

- Martin Smith

---

为什么执行计划会有如此不同？例如，我直接从Sql Management Studio查看我的查询执行计划（需要3秒），而通过sp_executeSql获取的执行计划需要5分钟以上。从sp_executeSql获取的执行计划完全忽略了一些主要索引，而直接调用却找到了这些索引。可以有人解释一下为什么从管理工具调用可以找到键，但是通过sp_ExecuteSql调用却不能找到吗？ - Nathan Tregillus

@NathanTregillus - 可能是参数嗅探，您可以查看缓存的计划XML以查看实际编译计划使用的参数值。 - Martin Smith

感谢 @MartinSmith 的回复。实际上这是由于我们在视图中使用 contextInfo 作为过滤器的方式，而它并没有被考虑进执行计划中。 - Nathan Tregillus

Answer 2

我通过更新统计数据解决了在SSMS中使用即席TSQL和sp_executesql之间的查询计划差异。这是一个简单的查询，涉及两次访问相同的表格。 UPDATE STATISTICS sometable

- Eric Petro

---

统计数据过时也是这里的罪魁祸首。当然，重新编译选项可以解决问题，以及删除传递给sp_executesql的查询中的一个简单空格，但在我们的情况下，统计数据已经超过1年了，这才是真正的问题所在。 - mprost

Answer 3

我也经历过同样的情况（设置选项相等）：正常查询会生成并行计划，而使用sp_executesql则会生成串行计划。

declare @xyzParam1 datetime,@xyzParam2 datetime
select @xyzParam1='Sep  1 2014 12:00:00:000AM',@xyzParam2='Sep 26 2014 11:59:59:000PM'
SELECT  *  FROM Theview WHERE  departuretime BETWEEN @xyzParam1 AND  @xyzParam2
;

vs

 exec sp_executesql N'SELECT  *  FROM Theview WHERE  departuretime BETWEEN @xyzParam1 AND  @xyzParam2',N'@xyzParam1 datetime,@xyzParam2 datetime',@xyzParam1='Sep  1 2014 12:00:00:000AM',@xyzParam2='Sep 26 2014 11:59:59:000PM'

我修改了使用的视图并获得了最佳结果，因为它包含了对预期数据的左连接，现在已转换为内部连接。现在常规查询选取与使用sp_executesql获得的计划相同的方案，性能得到了显著提高。

- ALZDBA