Same query different execution plans

Question

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-c172873564e9'' as uniqueidentifier), cast(''dda75f7a-d366-472e-81b5-c4f3119dd715'' as uniqueidentifier), cast(''cc469264-a706-49c6-961b-d6520437e796'' as uniqueidentifier), cast(''ee2d7ea0-1f94-4cc3-9f64-d8d56374cae5'' as uniqueidentifier), cast(''f7dc6b77-3735-479d-b420-e145d4e3d66f'' as uniqueidentifier), cast(''8a7dbe93-ed1a-49f3-a3d0-e19c6dd4e4ef'' as uniqueidentifier), cast(''b0524a60-f980-4b82-a799-e788a9a4d04b'' as uniqueidentifier), cast(''ceaf8a0b-f410-4f88-9062-e804b76b6e78'' as uniqueidentifier))) AND ([Extent1].[EventCode] = @p__linq__0) AND ([Extent1].[Date] >= @p__linq__1) AND ([Extent1].[Date] <= @p__linq__2) AND (([Extent1].[SiteID] = @p__linq__3) OR (([Extent1].[SiteID] IS NULL) AND (@p__linq__3 IS NULL)))
)  AS [Project1]
ORDER BY [Project1].[Date] DESC',N'@p__linq__0 int,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 uniqueidentifier',@p__linq__0=1799,@p__linq__1='2018-04-22 10:00:00',@p__linq__2='2018-04-23 10:00:00',@p__linq__3='B02A51FE-2248-E611-A64E-782BCB72ACED' 
go

So what I did was remove the parameters from query and run it as a single unit like this.

 select * from (  SELECT  1 as c1,  [Extent1].[Date] AS [Date], 
    [Extent1].[AssetID] AS [AssetID],         
    [Extent1].[EventData] AS [EventData]  
    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-c172873564e9' as uniqueidentifier), cast('dda75f7a-d366-472e-81b5-c4f3119dd715' as uniqueidentifier), cast('cc469264-a706-49c6-961b-d6520437e796' as uniqueidentifier), cast('ee2d7ea0-1f94-4cc3-9f64-d8d56374cae5' as uniqueidentifier), cast('f7dc6b77-3735-479d-b420-e145d4e3d66f' as uniqueidentifier), cast('8a7dbe93-ed1a-49f3-a3d0-e19c6dd4e4ef' as uniqueidentifier), cast('b0524a60-f980-4b82-a799-e788a9a4d04b' as uniqueidentifier), cast('ceaf8a0b-f410-4f88-9062-e804b76b6e78' as uniqueidentifier))) AND ([Extent1].[EventCode] = 1799) AND ([Extent1].[Date] >= '2018-04-22 10:00:00') AND ([Extent1].[Date] <= '2018-04-23 10:00:00') AND (([Extent1].[SiteID] = 'B02A51FE-2248-E611-A64E-782BCB72ACED') OR (([Extent1].[SiteID] IS NULL) AND ('B02A51FE-2248-E611-A64E-782BCB72ACED' IS NULL))) 
) as proeject1  order by proeject1.Date desc

These are the respective IO costs for queries.

(3721 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Alarm'. Scan count 5, logical reads 69032, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Better plan

(3721 row(s) affected)
Table 'Alarm'. Scan count 28, logical reads 564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Actual execution plan with good performance

Actual execution plan with bad performance

Indexes:

CREATE NONCLUSTERED INDEX [IX_Alarm_Lat] 
ON [dbo].[Alarm] ( [lat] ASC ) 
INCLUDE ( [AssetID], [Date])

CREATE NONCLUSTERED INDEX [IX_AlarmEventDate] 
ON [dbo].[Alarm] ( [AssetID] ASC, [EventCode] ASC, [Date] ASC, [Ended] ASC ) 
INCLUDE ( [ID], [EventData], [No], [AlarmTS], [ResetID], [lat], [lon], 
[StartTime], [SiteID], [AlarmStatus]) 

I have tried:

1. Option recompile
2. DBCC FREEPROCCACHE
3. sp_updatestats
4. Rebuilding corresponding index

But query always picks this plan causing a huge load on server disk.

I have read Slow in the Application, Fast in SSMS? - Understanding Performance Mysteries by Erland Sommarskog but it hasn't answered my question:

I have two queries, both running in SSMS, that have different performance. The main difference is that one is parameterized and the other is a plain query. I totally understand the differences between the queries, but my question is about picking the wrong index.

Answer 1

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.