Entity Framework 4.2 exec sp_executesql does not use indexes (parameter sniffing)

Question

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...

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).

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...

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:

1. Is there anyway to append the OPTION(RECOMPILE) query hint to the SQL generated by Entity Framework?
2. Is there anyway to prevent Entity Framework from using exec sp_executesql, and instead simply run the raw SQL?
3. Is anyone else running into this problem? Any other hints/tips?

Additional Information:

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

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)

Venation

I can't answer to anything about EF (sorry, have never touched it), but I have other questions that you should answer in your question: Did you restart the service after setting the trace flag? What is your setting at the database level for parameterization (simple or forced - see sys.databases.is_parameterization_forced)? What is the sp_configure setting for "optimize for ad hoc workloads"? Unfortunately, if I remember right, out of the box EF will generate two different plans for parameters like N'foo' and N'blat' - because they're different lengths. – Lockman

My apologies for the delayed response, and thank you for you quick comment! I've added the additional information to the original post. – Venation

So I would try your tests again with (a) parameterization set to forced (b) optimize for ad hoc workloads set to 1 and (c) both. It is quite likely that (b) will help this scenario but (a) might help as well. I'll confess I haven't tested much with (a) but (b) has helped every ad hoc workload I've ever tried it against. – Lockman

In response to your question #3, yes I am having the same problem. No suggestions yet, however. – Seamanlike

We have the same problem – Hallway

Same problem here. And the accepted answer below doesn't work for us... – Leyba

Answer 1

At this point I would recommend:

---

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

---

If after some time this setting doesn't seem to have helped, only then would I try the additional support of the trace flag. These are usually reserved as a last resort. Set the trace flag using the command line via SQL Server Configuration Manager, as opposed to in a query window and using the global flag. See http://msdn.microsoft.com/en-us/library/ms187329.aspx

Lockman

Answer 2

tl;dr

update statistics

---

We had a delete query with one parameter (the primary key) that took ~7 seconds to complete when called through EF and sp_executesql. Running the query manually, with the parameter embedded in the first argument to sp_executesql made the query run quickly (~0.2 seconds). Adding option (recompile) also worked. Of course, those two workarounds aren't available to us since were using EF.

Probably due to cascading foreign key constraints, the execution plan for the long running query was, uhmm..., huge. When I looked at the execution plan in SSMS I noticed that the arrows between the different steps in some cases were wider than others, possibly indicating that SQL Server had trouble making the right decisions. That led me to thinking about statistics. I looked at the steps in the execution plan to see what table was involved in the suspect steps. Then I ran update statistics Table for that table. Then I re-ran the bad query. And I re-ran it again. And again just to make sure. It worked. Our perf was back to normal. (Still somewhat worse than non-sp_executesql performance, but hey!)

It turned out that this was only a problem in our development environment. (And it was a big problem because it made our integration tests take forever.) In our production environment, we had a job running that updated all statistics on a regular basis.

Leyba

This works when it works, but doesn't always work - still leaves "no good way" to get the hints over to SQL Server from EF. – Fenske