How do I control parameter sniffing and/or query h

问题:

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.

回答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

回答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.