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

本文介绍了Entity Framework 4.2 exec sp_executesql 不使用索引(参数嗅探)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I'm encountering some major performance problems with simple SQL queries generated by the Entity Framework (4.2) running against SQL Server 2008 R2. In some situations (but not all), EF uses the following syntax:

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

在其他情况下只是简单地执行原始 SQL，并将提供的参数烘焙到查询中.我遇到的问题是，使用 sp_executesql 执行的查询忽略了目标表上的所有索引，导致查询性能极差(通过检查 SSMS 中的执行计划确认).

In other situations is simply executes the raw SQL with the provided parameters baked into the query. The problem I'm encountering is that queries executed with the sp_executesql are ignoring all indexes on my target tables, resulting in an extremely poor performing query (confirmed by examining the execution plan in SSMS).

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

After a bit of research, it sounds like the issue might be caused by 'parameter sniffing'. If I append the OPTION(RECOMPILE) query hint like so:

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

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

The indexes on the target tables are used and the query executes extremely quickly. I've also tried toggling on the trace flag used to disable parameter sniffing (4136) on the database instance (http://support.microsoft.com/kb/980653), however this didn't appear to have any effect whatsoever.

这给我留下了几个问题:

This leaves me with a few questions:

是否可以将 OPTION(RECOMPILE) 查询提示附加到实体框架生成的 SQL 中?
无论如何要阻止实体框架使用 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 服务，似乎实际上清除了跟踪标志......也许我应该以不同的方式来做这件事...

I should also mention that if I restart the SQL Server Service via the service management console AFTER enabling trace flag 4136 with the below script, appears to actually clear the trace flag...perhaps I should be doing this a different way...

DBCC TRACEON(4136,-1)

推荐答案

此时我建议:

将针对临时工作负载的优化设置设置为 true.

Set the optimize for ad hoc workloads setting to 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

这篇关于Entity Framework 4.2 exec sp_executesql 不使用索引(参数嗅探)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

recompile