I have the following query:
DECLARE @p__linq__0 UNIQUEIDENTIFIER SET @p__linq__0 = '... some guid ...' SELECT TOP 1 [EventId] AS [EventId], [DateCreated] AS [DateCreated], [LocationId] AS [LocationId], [SourceName] AS [SourceName], [SourceState] AS [SourceState], [Priority] AS [Priority], [EventDescription] AS [EventDescription], [FirstTrigger] AS [FirstTrigger] FROM [dbo].[Watchdog] WHERE [LocationId] = @p__linq__0 AND [FirstTrigger] = 1 ORDER BY [DateCreated] DESC
Watchdog table defines 2 indecies:
- Clustered index on
EventIdprimary key column
- Unclustered index on
Reading other posts on how to eliminate key lookup I added another non-clustered index which includes all columns from
CREATE NONCLUSTERED INDEX [LocationId_FirstTrigger] ON [dbo].[Watchdog] ( [LocationId] ASC, [FirstTrigger] ASC ) INCLUDE ( [EventId], [DateCreated], [SourceName], [SourceState], [Priority], [EventDescription]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] GO
However, this didn't help and actual execution plan is the same. If I look at key lookup the output is actually included in newly added non clustered index.
My question is, why it's still doing
key lookup instead of index scan/seek ?
Following some suggestions in the comments, I dropped newly created non clustered index & instead recreated non clustered index on
DateCreated column including columns from
Now execution plan is the following:
Also query execution time dropped from 1+ minute to few seconds (this table has 18+ million rows).
Does this mean key lookup was done due to
ORDER BY on non-clustered index ?