High cost SQL shows low cost when testing with parameters in SQL Server

Question

My activity monitor shows this high cost query being run below without the value for DivisionPoolId and a high cost to run. I test it out with a real DivisionPoolId and the cost is low. I am not understanding why the cost is showing high for one but not the other but you can see that the execution plans are different for some reason for the same query.

Also this SQL is created from Entity Framework.

Low cost:

SELECT 
    [Distinct1].[DivisionGameTeamResultId] AS [DivisionGameTeamResultId]
FROM 
    (SELECT DISTINCT 
         [Extent1].[DivisionGameTeamResultId] AS [DivisionGameTeamResultId]
     FROM 
         [Test].[DivisionBracketParticipant] AS [Extent1]
     LEFT OUTER JOIN 
         [Test].[DivisionBracketParticipantPool] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
     WHERE  
         ([Extent2].[DivisionPoolId] = 124396) 
         OR (([Extent2].[DivisionPoolId] IS NULL) AND (124396 IS NULL)))  AS [Distinct1]

High cost (Activity monitor for expensive queries):

SELECT 
    [Distinct1].[DivisionGameTeamResultId] AS [DivisionGameTeamResultId]
FROM 
    (SELECT DISTINCT 
         [Extent1].[DivisionGameTeamResultId] AS [DivisionGameTeamResultId]
     FROM  
         [Test].[DivisionBracketParticipant] AS [Extent1]
     LEFT OUTER JOIN 
         [Test].[DivisionBracketParticipantPool] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
     WHERE 
         ([Extent2].[DivisionPoolId] = @p__linq__0) 
         OR (([Extent2].[DivisionPoolId] IS NULL) AND (@p__linq__0 IS NULL)))  AS [Distinct1]

Most likely this is *parameter sniffing*. If you add `with option(recompile)` to the query using a parameter, do you see index seek? — Stu, Aug 07 '21 at 16:43
It’s entity framework so I can’t and I need the change tracking on this query — Mike Flynn, Aug 07 '21 at 16:44
Using an ORM is a bit like trying to steer a car using your knees with your hands tied behind your back - generally you're fine if you don't need to steer much ;-). If this is not a production system, try `dbcc freeproccache` and then run it from your app first. — Stu, Aug 07 '21 at 16:49
The second query probably uses an existing query plan that was created when the statistics were different. It's often very enlightening to run a query through Sql Server's tuning advisor. — Gert Arnold, Aug 07 '21 at 18:10
Consider opting out of emulating C# null comparisons with UseRelationalNulls https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.infrastructure.relationaloptionsextension.userelationalnulls?view=efcore-5.0 — David Browne - Microsoft, Aug 07 '21 at 20:35
I'm guessing the actual LINQ C# code could be optimized, if you [edit] your question and add it we can see. — Charlieface, Aug 07 '21 at 21:58

score 0 · Accepted Answer · answered Aug 07 '21 at 18:40

0

I am going to follow this answer below and create an interceptor to add the with option(recompile) to the query.

EF 6 Parameter Sniffing

answered Aug 07 '21 at 18:40

Mike Flynn

22,342
54
182
341

High cost SQL shows low cost when testing with parameters in SQL Server

1 Answers1