实体框架4.2 exec sp_executesql不使用索引（参数嗅探）

问题描述

我遇到了由SQL Server 2008 R2运行的实体框架（4.2）生成的简单SQL查询的一些主要性能问题。在某些情况下（但不是全部），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）的跟踪标志（），但这并没有任何影响。

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.

这让我有几个问题：

1. 有没有在实体框架中生成的SQL附加OPTION（RECOMPILE）查询提示？


2. 有没有办法阻止Entity Framework使用exec sp_executesql，而是简单地运行原始SQL？


3. 其他人是否遇到这个问题？任何其他提示/提示？

附加信息

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


2. 参数化设置为SIMPLE （is_parameterization_forced：0）


3. 优化adhoc工作负载具有以下设置
   
   
   
   • value：0
   
   
   • minimum：0
   
   
   • maximum：1
   
   
   • value_in_use：0
   
   
   • is_dynamic：1
   
   
   • is_advanced：1
   
   

1. I did restart the database instance through SSMS, however, I will try restarting the service from the service management console.
2. Parameterization is set to SIMPLE (is_parameterization_forced: 0)
3. Optimize for adhoc workloads has the following settings
   • value: 0
   • minimum: 0
   • maximum: 1
   • value_in_use: 0
   • is_dynamic: 1
   • is_advanced: 1

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

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)

推荐答案

在这一点上，我会rec ommend：

At this point I would recommend:

将特设工作负载设置的优化设置为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配置管理器设置跟踪标志，而不是在查询窗口中使用全局标志。请参阅

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