Same query different execution plans

I am trying to optimize performance for a server and this particular query was causing huge reads from database, in turn causing timeout for queries. This query is generated from EF6 in Asp.Net MVC.

Here is the problematic query:

```
exec sp_executesql N'SELECT
[Project1].[C1] AS [C1],
[Project1].[Date] AS [Date],
[Project1].[AssetID] AS [AssetID],
[Project1].[EventData] AS [EventData]
FROM ( SELECT
[Extent1].[AssetID] AS [AssetID],
[Extent1].[Date] AS [Date],
[Extent1].[EventData] AS [EventData],
1 AS [C1]
FROM [dbo].[Alarm] AS [Extent1]
WHERE ([Extent1].[AssetID] IN (cast(''c6e3142e-5b1f-4a91-90d2-03a504e86ece'' as uniqueidentifier), cast(''4de25e8a-7401-49ae-bd6d-0861d67f0d2f'' as uniqueidentifier), cast(''455e3a5f-1091-4784-9964-0a1a54eaa644'' as uniqueidentifier), cast(''04b46c21-c44f-4b67-b64b-12f2764c0448'' as uniqueidentifier), cast(''a350992b-8548-4bf1-bd22-131c114a5343'' as uniqueidentifier), cast(''98ec1f36-cc54-45d2-a0e3-22aa1b669373'' as uniqueidentifier), cast(''27abcf37-2093-43d5-ae62-2e7b10fe4692'' as uniqueidentifier), cast(''c9f43598-2b9c-47b0-9230-37440e6aea54'' as uniqueidentifier), cast(''c5964caa-5c73-4c0e-bb80-4c1dc7e11039'' as uniqueidentifier), cast(''6ac30678-3876-43c9-b708-61ef19b5ea17'' as uniqueidentifier), cast(''e69d870a-87de-4e3d-b4fc-62c962489a7b'' as uniqueidentifier), cast(''a7c2f407-c605-4491-85fe-66c16fc15586'' as uniqueidentifier), cast(''a38f452e-ee3a-4be7-94ad-99c1474a417f'' as uniqueidentifier), cast(''b0f65616-d5d1-4af9-bffd-9c4b2b7f52e7'' as uniqueidentifier), cast(''3940fed6-9c40-4db6-bdc2-9dc5ef7b49ea'' as uniqueidentifier), cast(''e09f7618-c7d7-414d-b5d7-9ec22b3e9b64'' as uniqueidentifier), cast(''62c91349-d33f-42ed-b16d-a63424acca4a'' as uniqueidentifier), cast(''46812e72-45af-426e-9d72-aafdbcc9c4a7'' as uniqueidentifier), cast(''2a1d2b2a-0471-4f57-adc5-b42a03eb5e01'' as uniqueidentifier), cast(''2797d370-b237-4d2c-bede-b7af67f2b0f4'' as uniqueidentifier), cast(''0c50bb44-133e-4434-b403

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

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:

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.

StackExchange Database Administrators Q#204707, answer score: 5