How do I control parameter sniffing and/or query hints in entity framework?

Question

I have a problem where one of my Entity Framework (EF) queries is taking a very long time to execute in Sql Server, although when I copy-and-paste the generated TSQL into Sql Server Management Studio (SSMS) it runs extremely fast. After some investigation I found that I was experiencing a parameter sniffing issue, and the correct way to fix it is to insert one of many query hints (OPTIMIZE FOR, RECOMPILE, and so on). How do I insert these hints into my EF queries?

Answer 1

To apply a hint on a query generate by EF, you should use plan guides, more info here: One to one join Not fast enough in SQL Server

Answer 2

If you are executing stored procedures you could declare the parameters of the stored procedure internally.

I.e.

CREATE PROCEDURE sp_test
(
     @param1     NVARCHAR(10),
     @param2     INT
)

AS

DECLARE @internalParam1 NVARCHAR(10)
DECLARE @internalParam2 INT

SET @internalParam1 = @param1
SET @internalParam2 = @param2

-- REST OF YOUR QUERY

GO

This will stop SQL Server caching any parameters that are being passed to the SP.

Answer 3

The problem:

First, I want to point out that this question is not related to stored procedures question. A question about stored procedures will not have the same solution as this question. That is about queries that come from the EF core itself.

It is also not related to INDEX hint questions. If you do not have the correct index, the following solution will not speed up query.

(The same goes for NOLOCK hint)

The solution:

By default, when you write a code with EF, such as: e.Id == id

You get output in the query like this: [p].[Id] = @__id_0

This leads (sometimes) to non-optimal execution plan, and that leads to very slow query execution times, and this forces people to write extensions, command interceptors , etc. (Why this is bad - at the end of the question)

The actual solution to this problems comes with EF Core 9, where you have control over parameterization.

By replacing the code to: e.Id == EF.Constant(id))

The output of the query will be: [p].[Id] = 1

This changes the execution plan to seek that value. (To get idea how much faster this can get - my last query execution time went from 2.5 sec to 30 ms.)

Here is the documentation about the change: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-9.0/whatsnew#force-or-prevent-query-parameterization

Why pick this solution, instead of the old "solutions":

Unlike the new solution, the old do not really control anything. Those hints are not "single parameter" based. They are "whole query" based.

You do not want (let's say recompilation, as most common solution proposed) for the whole query. (Sometimes it is worse.)

Also the "write extension, add interceptor, add comments, check strings, modify the query" adds up as expense. Speaking both in terms to maintain the code and to execute it.