Entity Framework 4.2 exec sp_executesql 不使用索引（参数嗅探）

Question

我遇到了针对 SQL Server 2008 R2 运行的实体框架 (4.2) 生成的简单 SQL 查询的一些主要性能问题。在某些情况下（但不是全部），EF 使用以下语法：

exec sp_executesql 'DYNAMIC-SQL-QUERY-HERE', @param1...

在其他情况下，只是使用嵌入到查询中的提供的参数来执行原始 SQL。我遇到的问题是，使用 sp_executesql 执行的查询忽略了目标表上的所有索引，导致查询性能极差（通过检查 SSMS 中的执行计划确认）。

经过一番研究，听起来这个问题可能是由“参数嗅探”引起的。如果我像这样附加 OPTION(RECOMPILE) 查询提示：

exec sp_executesql 'DYNAMIC-SQL-QUERY-HERE OPTION(RECOMPILE)', @param1...

将使用目标表上的索引，并且查询执行得非常快。我还尝试在数据库实例上切换用于禁用参数嗅探（4136）的跟踪标志（http://support.microsoft .com/kb/980653），但这似乎没有任何效果。

这给我留下了几个问题：

1. 是否有办法将 OPTION(RECOMPILE) 查询提示附加到实体框架生成的 SQL 中？
2. 是否有办法阻止实体框架使用 exec sp_executesql，而只是运行原始 SQL？
3. 还有其他人遇到这个问题吗？还有其他提示/技巧吗？

其他信息：

1. 我确实通过 SSMS 重新启动了数据库实例，但是，我将尝试从服务管理控制台重新启动该服务。
2. 参数化设置为 SIMPLE (is_parameterization_forced: 0)
3. 针对临时工作负载的优化具有以下设置
   • 值：0
   • 最小值：0
   • 最大值：1
   • 使用价值：0
   • is_dynamic：1
   • is_advanced：1

我还应该提到，如果我在使用以下脚本启用跟踪标志 4136 后通过服务管理控制台重新启动 SQL Server 服务，则会出现实际清除跟踪标志...也许我应该以不同的方式执行此操作...

DBCC TRACEON(4136,-1)

Answer 1

tl;dr

更新统计信息

---

我们有一个带有一个参数（主键）的 delete 查询，通过 EF 调用时大约需要 7 秒才能完成和sp_executesql。手动运行查询，并将参数嵌入到 sp_executesql 的第一个参数中，可以使查询快速运行（约 0.2 秒）。添加选项（重新编译）也有效。当然，由于我们使用的是 EF，所以这两种解决方法对我们来说不可用。

可能由于级联外键约束，长时间运行的查询的执行计划非常庞大。当我查看 SSMS 中的执行计划时，我注意到在某些情况下不同步骤之间的箭头比其他步骤更宽，这可能表明 SQL Server 无法做出正确的决策。这让我开始思考统计学。我查看了执行计划中的步骤，看看可疑步骤涉及哪些表。然后我为该表运行了更新统计表。然后我重新运行了错误的查询。我又重新运行了一遍。再次确认。它起作用了。我们的表现恢复正常。 （仍然比非 sp_executesql 性能差一些，但是嘿！）

事实证明，这只是我们开发环境中的一个问题。 （这是一个大问题，因为它使我们的集成测试花费了很长时间。）在我们的生产环境中，我们运行了一项定期更新所有统计数据的作业。

Answer 2

此时，我建议：

---

将临时工作负载优化设置设置为 true。

EXEC sp_configure 'show advanced', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'optimize for ad hoc', 1;
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

---

如果一段时间后此设置似乎没有帮助，只有那时我才会尝试跟踪标志的额外支持。这些通常被保留作为最后的手段。通过 SQL Server 配置管理器使用命令行设置跟踪标志，而不是在查询窗口中使用全局标志。请参阅http://msdn.microsoft.com/en-us/library/ms187329.aspx