entity-framework - Entity Framework 4.2 exec sp_executesql 不使用索引(参数嗅探)

Question

我遇到了一些主要的性能问题，由针对 SQL Server 2008 R2 运行的 Entity Framework (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...

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

这给我留下了几个问题:

有没有将 OPTION(RECOMPILE) 查询提示附加到 Entity Framework 生成的 SQL 中？

无论如何要阻止 Entity Framework 使用 exec sp_executesql，而只是运行原始 SQL？

还有其他人遇到这个问题吗？任何其他提示/提示？

附加信息:

我确实通过 SSMS 重新启动了数据库实例，但是，我将尝试从服务管理控制台重新启动该服务。

参数化设置为 SIMPLE (is_parameterization_forced: 0)

针对临时工作负载优化具有以下设置

值:0

最小值:0

最大值:1

value_in_use: 0

is_dynamic: 1

is_advanced: 1

我还应该提到，如果我在使用以下脚本启用跟踪标志 4136 后通过服务管理控制台重新启动 SQL Server 服务，似乎实际上清除了跟踪标志......也许我应该以不同的方式这样做......

DBCC TRACEON(4136,-1)

Answer 1

在这一点上，我会建议:

将优化临时工作负载设置设置为 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