entity-frameworksql-server-2008-r2sp-executesqlparameter-sniffing

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

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

  • 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