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 i 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

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.

Thanks

Answer 1

Maybe I am missing something but why do you need the sub-query?
OP said from entity framework and even the better query had a sub query.
I wanted to break down the query and it was more than I bargained for. Worth a try:

exec sp_executesql N'
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 )
       )
 ORDER BY [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

try adding the recommended index
create index on EventCode, AssetID, SiteID, [Date]