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]
I am going to follow this answer below and create an interceptor to add the with option(recompile) to the query.