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

Question

Update: I've created a suggestion to implement hint control in a future version of EF. Go here to vote for it.

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?

Related questions coming at this from different perspectives are here, here, and here.

Foulness

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

Wheeled

Does anybody have an actual EF example of this? – Mud

Can you explain your scenario? I faced this issue a long time ago and the final solution was around it, not through. – Wheeled

I came up with a different solution based on interceptors which are, I believe, a newer feature in EF (probably didn't exist at the time of your answer). See: #26762327 – Mud

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.

Occlusive

Thank you for your answer, but unfortunately I'm looking for a way to add hints into the query that EF generates, not a stored procedure. – Foulness

I've had a quick google and there is nothing obvious for your problem with EF, other ORMS such as NHibernate allow query hints. These posts below are the closest solution I can find to your problem: #8031569 https://mcmap.net/q/157182/-entity-framework-with-nolock – Occlusive