EF Core takes a lot of time, sometimes, to perform SELECT query

Question

I am using EF Core 6 vs a SQL Server DB. Sometimes, the execution of a SELECT query takes more than 30 seconds, and goes in timeout.

If I execute the very same SQL generated by EF Core (with the very same parameters, on the same DB, just a few seconds after the timeout), it takes less than a second.

During the overall period, the DB server stays with CPU < 30%.

Running the SQL query on SQL Server Management Studio, I can see that the execution plan is ideal (i.e. it uses the indexes, etc.).

So I am afraid there could be some locking that stops the DB from returning the result of the query.

Is there a way to specify to EF Core e.g. the ISOLATION LEVEL of the query, or even some concurrency / locking strategy?

In my specific scenario, it's ok to have dirty or not completely up-to-date reads, because we have procedures in place to retrieve the clean data in a subsequent round of queries.

Thanks

Answer 1

It is not new question when SQL Server may slowdown queries because of Parameter Sniffing. Problem can be solved by converting parameters to constants or by adding OPTION(RECOMPILE) to the end of the query. This answer adds DbCommandInterceptor to DbContextOptions and appends OPTION(RECOMPILE) hint to particular queries.

Configuring DbContext

builder.UseSqlServer(connectionString)
    .UseRecompileExtensions(); // registering interceptor 

How to use in queries:

var name = "SomeName";
var result = context.SomeItems
    .Where(x => x.Name == name)
    .WithRecompile() // it marks query as a query which needs RECOMPILE query hint
    .ToList();

Then to SQL Server will be sent the following SQL:

SELECT [s].[Id], [s].[Name]
FROM [SomeItems] AS [s]
WHERE [s].[Name] = @__name_0
OPTION(RECOMPILE)

And implementation of extensions:

I have put everything into one static class for simplifying answer. Tested on EF Core 6, but should work for lower versions also.

public static class RecompileExtensions
{
    private const string RecompileTag = "recompile_query_tag";
    private const string RecompileComment = "-- " + RecompileTag + "\r\n";

    public static DbContextOptionsBuilder UseRecompileExtensions(this DbContextOptionsBuilder builder)
    {
        return builder.AddInterceptors(RecompileInterceptor.Instance);
    }

    public static IQueryable<T> WithRecompile<T>(this IQueryable<T> query)
    {
        return query.TagWith(RecompileTag);
    }

    private class RecompileInterceptor : DbCommandInterceptor
    {
        public static RecompileInterceptor Instance = new();

        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
            CancellationToken cancellationToken = new CancellationToken())
        {
            CorrectCommand(command);

            return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
        }

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            CorrectCommand(command);

            return base.ReaderExecuting(command, eventData, result);
        }

        private static void CorrectCommand(DbCommand command)
        {
            var newQuery = command.CommandText.Replace(RecompileComment, "");

            // if query was changed, we have to append RECOMPILE option
            if (!ReferenceEquals(newQuery, command.CommandText))
            {
                // remove rest of the comment
                if (newQuery.StartsWith("\r\n"))
                    newQuery = newQuery.Substring(2);

                newQuery += "\r\nOPTION(RECOMPILE)";
                command.CommandText = newQuery;
            }
        }
    }
}

UPDATE

Starting from SQL Server 2016 it is possible to disable Parameter Sniffing via ALTER DATABASE SCOPED CONFIGURATION

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;