Menu
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.
485
It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure. You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure.
SQL Server uses a process called parameter sniffing when it executes stored procedures that have – you guessed it – parameters. When the procedure is compiled or recompiled, the value passed into the parameter is evaluated and used to create an execution plan.
This topic describes how to recompile a stored procedure in SQL Server by using Transact-SQL. There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called, the RECOMPILE query hint on individual statements, or by using the sp_recompile system stored procedure.
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.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
