Search code examples
sql-serverlookupfiltered-index

Why a coverage filtered index do a Lookup

I have a table that need to check every minute to alert when the total is greater than a number.

 SELECT 
  count(CreatedAt) Total
 FROM 
  Process d 
 WHERE 
  d.ProcessedAt is null
  AND DATEDIFF(second, GETUTCDATE(), d.CreatedAt) > 30

My idea was create a filtered index, something like:

CREATE NONCLUSTERED INDEX [FIX_Process_CreatedAt_ProcessedAt] ON [dbo].[Process]
(
    [CreatedAt] ASC
)
WHERE ProcessedAt IS NULL

But looking at the execution plan, there is a key lookup.

I don't understand why, becuase the index has the two columns for the query.

Can anyone explain me why?

Solution

  • If the condition is NULL then you will have only one value for the record set and why you require an index to that value? On what basis it will do sorting? So you need to do filtered index where processedAt is not null and use that condition in code will help

    You need to include ProcessedAt column in INCLUDED Column in the create Index script

    Adding an example for explaining @Martin Smith's comment:

    Table script :

    Create Table TestKeyLookup
(
id int identity(1,1) primary key -- Created PK which will create clustered Index
,id_for_filteredIndex int NOT NULL
,another_id int NOT NULL
)

    Insert into table records :

    declare @i int = 50
while @i < 1000000 
begin
    insert into TestKeyLookup (id_for_filteredIndex, another_id) values (@i, @i+5)
    set @i = @i + 10
END

    Create Non Clustered filtered index on id_for_FilteredIndex column with condition on different column another_id

    create nonclustered index NCI_TestKeyLookup on dbo.TestKeyLookup(id_for_filteredIndex)
where another_id > **673105**

    If i query the table with exactly same condition then optimizer is not using KeyLookup

    select  count(id_for_filteredIndex) from TestKeyLookup with(index(NCI_TestKeyLookup))
where another_id > 673105

    It does use filtered index

    If i change the condition by increasing even +5 or 10 then it does keyLookup to clustered index

    select  count(id_for_filteredIndex) from TestKeyLookup with(index(NCI_TestKeyLookup))
where another_id > 673110

    Different Id where another_id is greater than filtered index condition

    I am trying to explain this only.. If there is a change in the condition then it uses KeyLookup to fetch. In a way you are correct if the column is nullable and it has null values then it is different