Sql-server – Proactively Determining Bad Plans/Poor Performance at a Query Level

This is not an issue, it is by design and is considered a performance improvement.

If your queries do not run as expected you will need to address this in the code of your database/application.

You can force the old carnality estimator by using the trace flag 9481 at the server, session, or query level (using OPTION (QUERYTRACEON 9481)). This will force it no matter what the compatibility level of the database is.

There is also a ton of information that the CSS Team posted here.

A more detailed post on the Cardanility Estimator and changes around that process in SQL Server 2014, includes example.

The first query calls parameterized dynamic SQL which makes it eligible for parameter sniffing:

<ParameterList>
  <ColumnReference Column="@p__linq__3" ParameterCompiledValue="{guid'B02A51FE-2248-E611-A64E-782BCB72ACED'}" ParameterRuntimeValue="{guid'B02A51FE-2248-E611-A64E-782BCB72ACED'}" />
  <ColumnReference Column="@p__linq__2" ParameterCompiledValue="'2018-04-23 10:00:00.0000000'" ParameterRuntimeValue="'2018-04-23 10:00:00.0000000'" />
  <ColumnReference Column="@p__linq__1" ParameterCompiledValue="'2018-04-22 10:00:00.0000000'" ParameterRuntimeValue="'2018-04-22 10:00:00.0000000'" />
  <ColumnReference Column="@p__linq__0" ParameterCompiledValue="(1799)" ParameterRuntimeValue="(1799)" />
</ParameterList>

The second query doesn't use dynamic SQL. It has hardcoded values instead of parameters. The question, as I understand it, is why does SQL Server pick a less efficient plan with dynamic SQL even with sniffed parameter values that match the other query? This is a reasonable question.

First let me convince you that sometimes SQL Server must give different plans. Even with parameter sniffing, SQL Server still must cache a plan that's safe for all possible parameter values. For a simple example consider the following query:

CREATE TABLE #DEMO (
    ID INT NOT NULL,
    PRIMARY KEY (ID)
);

exec sp_executesql N'
SELECT *
FROM #DEMO
WHERE (@id IS NULL OR id = @id)'
, N'@id int'
, @id=1;

We get parameter sniffing:

<ParameterList>
  <ColumnReference Column="@id" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
</ParameterList>

But the query plan uses a scan:

A seek would be more efficient (if the table had any data). If I try to force a seek:

exec sp_executesql N'
SELECT *
FROM #DEMO WITH (FORCESEEK)
WHERE (@id IS NULL OR id = @id)'
,N'@id int'
,@id=1;

I get the following error:

Msg 8622, Level 16, State 1, Line 15 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

The cached plan needs to be valid for all possible parameter values. If the parameter has a value of NULL then a seek cannot be performed. Therefore, a scan is the only safe choice. If I use literal values:

SELECT *
FROM #DEMO
WHERE (1 IS NULL OR id = 1);

The query optimizer can simplify away the impossible part of the predicate (1 IS NULL) and I get a seek:

As far as I can tell you aren't running into this situation with your query. The point of this example is to show that you can't always expect to get the same query plan with the type of transformation you applied to your original query.

For your particular query I think the problem has to do with the data types of the parameters. When you write SQL with hardcoded parameter values you might be inadvertently using different data types from what you hardcoded. In the dynamic SQL version of the query you have @p__linq__1 and @p__linq__2 defined as datetime2(7). Does that match the column definition for Alarm.[Date]? If not then you might be ineligible for an index seek. Your query with literal values doesn't cast the dates to datetime2(7), so there's definitely a difference there. I suspect that this is the cause of your issue.