EF 6 Parameter Sniffing

Question

I have a dynamic query that is just too large to put here. Safe to say that in it's current form it utilizes a CLR procedure to dynamically build joins based upon the number of search parameters passed then takes that result and joins it to more detailed tables to bring back attributes important to the end-user. I have converted the entire query into LINQ to Entities and what I have found is that the SQL that it produces is efficient enough to do the job, however running via EF 6, the query timesout. Taking the resulting SQL and running it in SSMS runs in 3 or less seconds. I can only imagine that my problem is parameter sniffing. I have tried updating statistics on every table in the database and this has not solved the problem.

My Question is:

Can I somehow embed options like an "OPTION RECOMPILE" via EF?

Answer 1

It's possible to use the interception feature of EF6 to manipulate its internal SQL commands before executing them on DB, for instance adding option(recompile) at the end of the command:

public class OptionRecompileHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        addQueryHint(command);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        addQueryHint(command);
    }

    private static void addQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

To use it, add the following line at the beginning of the application:

DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());

Answer 2

I like VahidN's solution, do up vote him, but I want more control of when it happens. It turns out that DB Interceptors are very global, and I only wanted this to happen on specific contexts in specific scenarios.

Here we are setting the ground work to also support adding other query hints, that could be turned on and off as desired.

Since I often expose the method for passing a connection string, I also included support for that.

Below would give your context a flag to enable/disable the hint programatically, by extending the partial class EF generates. We also threw the small piece of reused code in the Interceptor into its own method.

Small Interface

public interface IQueryHintable
{
    bool HintWithRecompile { get; set; }
}

DB Command Interceptor

public class OptionHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    private static void AddHints<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext)
    {
        var context = interceptionContext.DbContexts.FirstOrDefault();
        if (context is IQueryHintable)
        {
            var hints = (IQueryHintable)context;

            if (hints.HintWithRecompile)
            {
                addRecompileQueryHint(command);
            }
        }
    }

    private static void addRecompileQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

Extending Entity Context to Add IQueryHintable

public partial class SomeEntities : DbContext, IQueryHintable
{
    public bool HintWithRecompile { get; set; }

    public SomeEntities (string connectionString, bool hintWithRecompile) : base(connectionString)
    {
        HintWithRecompile = hintWithRecompile;
    }

    public SomeEntities (bool hintWithRecompile) : base()
    {
        HintWithRecompile = hintWithRecompile;
    }

    public SomeEntities (string connectionString) : base(connectionString)
    {
    }

}

Register DB Command Interceptor (global.asax)

    DbInterception.Add(new OptionHintDbCommandInterceptor());

Enable context wide

    using(var db = new SomeEntities(hintWithRecompile: true) )
    {
    }

Turn On or Off

    db.HintWithRecompile = true;
    // Do Something
    db.HintWithRecompile = false;

I called this HintWithRecompile, because you might also want to implement a HintOptimizeForUnknown , or other query hints.

Answer 3

Same for me as for @Greg, enabling this system wide was not an option, so I wrote this small utility class that can temporary add option(recompile) to queries executed within an OptionRecompileScope.

Example usage

using (new OptionRecompileScope(dbContext))
{
    return dbContext.YourEntities.Where(<YourExpression>).ToList();
}

Implementation

public class OptionRecompileScope : IDisposable
{
    private readonly OptionRecompileDbCommandInterceptor interceptor;

    public OptionRecompileScope(DbContext context)
    {
        interceptor = new OptionRecompileDbCommandInterceptor(context);
        DbInterception.Add(interceptor);
    }

    public void Dispose()
    {
        DbInterception.Remove(interceptor);
    }

    private class OptionRecompileDbCommandInterceptor : IDbCommandInterceptor
    {
        private readonly DbContext dbContext;

        internal OptionRecompileDbCommandInterceptor(DbContext dbContext)
        {
            this.dbContext = dbContext;
        }

        public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            if (ShouldIntercept(command, interceptionContext))
            {
                AddOptionRecompile(command);
            }
        }

        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        }

        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            if (ShouldIntercept(command, interceptionContext))
            {
                AddOptionRecompile(command);
            }
        }

        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        private static void AddOptionRecompile(IDbCommand command)
        {
            command.CommandText += " option(recompile)";
        }

        private bool ShouldIntercept(IDbCommand command, DbCommandInterceptionContext interceptionContext)
        {
            return 
                command.CommandType == CommandType.Text &&
                command is SqlCommand &&
                interceptionContext.DbContexts.Any(interceptionDbContext => ReferenceEquals(interceptionDbContext, dbContext));
        }
    }
}

Answer 4

I had a similar problem. In the end, I removed the cached query plan with this command:

dbcc freeproccache([your plan handle here])

In order to get your plan handle, you can use the following query:

SELECT qs.plan_handle, a.attrlist, est.dbid, text
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
      FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
      WHERE  epa.is_cache_key = 1
      ORDER  BY epa.attribute
      FOR    XML PATH('')) AS a(attrlist)
 WHERE  est.text LIKE '%standardHourRate%' and est.text like '%q__7%'and est.text like '%Unit Overhead%'
 AND  est.text NOT LIKE '%sys.dm_exec_plan_attributes%'

replacing the content of the 'like' clauses with appropriate pieces of your query.

You can see my whole issue at:

SQL Query Using Entity Framework Runs Slower, uses bad query plan

Answer 5

Had a similar case in EF Core 2, but it only differs in the Interceptor implementation. Since this thread helped me the most, i want to share my implementation with you, even if the OP asked for EF 6. Furthemore i improved @Oskar Sjöberg and @Greg Solution a bit, to single out the queries that should be extended with the recompile option.

In EF Core 2 the Interceptor is a little tricky and a bit different.

It can be implemented via Package Microsoft.Extensions.DiagnosticAdapter and the following code

var contextDblistener = this.contextDb.GetService<DiagnosticSource>();
(contextDblistener as DiagnosticListener).SubscribeWithAdapter(new SqlCommandListener());

The Interceptor itself then needs its Methods marked with the corresponding DiagnosticName Annotation.

The tweak i gave to the Interceptor was, that it looks for specific tags (sql comments) inside the command to single out the queries that should be extended with the desired option.

To mark a Query to use the recompile option, you simply have to add a .TagWith(Constants.SQL_TAG_QUERYHINT_RECOMPILE) to the query without bothering around with setting a bool to true and back to false.

This way you also don't have a problem with parallel Queries being intercepted and all being extended with a recompile option because of a single bool HintWithRecompile.

The constant tag strings are designed so that they can only be inside of an sql comment and not part of the query itself. I could't find a solution to analyze only the tag part (implementation detail of EF), so the whole sql command is analyzed and you don't want to add a recompile because some text inside of the query matches your flag.

The "Optimize for Unknown" Part can be improved further by using the command parameter property, but i'll leave that up to you.

public class SqlCommandListener
{
    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuting")]
    public void OnCommandExecuting(DbCommand command, DbCommandMethod executeMethod, Guid commandId, Guid connectionId, bool async, DateTimeOffset startTime)
    {
        AddQueryHintsBasedOnTags(command);
    }

    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuted")]
    public void OnCommandExecuted(object result, bool async)
    {
    }

    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandError")]
    public void OnCommandError(Exception exception, bool async)
    {
    }

    private static void AddQueryHintsBasedOnTags(DbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
        {
            return;
        }

        if (command.CommandText.Contains(Constants.SQL_TAG_QUERYHINT_RECOMPILE) && !command.CommandText.Contains("OPTION (RECOMPILE)", StringComparison.InvariantCultureIgnoreCase))
        {
            command.CommandText = command.CommandText + "\nOPTION (RECOMPILE)";
        }
        else if (command.CommandText.Contains(Constants.SQL_TAG_QUERYHINT_OPTIMIZE_UNKNOWN_USER) && !command.CommandText.Contains("OPTION (OPTIMIZE FOR (@__SomeUserParam_0 UNKNOWN))", StringComparison.InvariantCultureIgnoreCase))
        {
            command.CommandText = command.CommandText + "\nOPTION (OPTIMIZE FOR (@__SomeUserParam_0 UNKNOWN))";
        }
    }
}

Edit: Take care if you subscribe to the DiagnosticSource, since it is not a subscription to the context object. The DiagnosticSource has another lifetime (and can be the source for many contexts). So if you subscribe with every scoped context you create, you will eventually create more and more subscriptions. See my answer here here for a solution to create only a single subscription.