How to get rid of these Key Lookups? I have this query:

    SELECT TOP (20) table1.Vit_Codigo
    ,table1.Vit_Nome
    ,table1.Vit_Apelido
    ,table1.Vit_codigoStatus
    ,table1.Vit_Numero
    ,table1.Vit_Ano
    ,table1.Vit_CodigoTiposExames
    ,table3.Esp_Setor
    ,table2.TEs_Descricao
FROM table1
LEFT JOIN table2 ON tb_Vitima.Vit_CodigoTiposExames = table2.TEs_Codigo
LEFT JOIN table3 ON table3 .Esp_Codigo = table1.Vit_CodigoEspecialidade
WHERE (rtrim(ltrim(lower(tb_Vitima.Vit_Rg))) = 'n/a')
    AND (
        table1.Vit_codigoStatus IN (
            3
            ,4
            )
        )
ORDER BY table1.Vit_DataAberturaReal DESC


And these plans:





I made this index:

create index IX_SELECTTOP20_INCLUDE 
    on table1
    ( Vit_Rg ,Vit_codigoStatus )
include (
Vit_Codigo , Vit_Nome , Vit_Apelido , Vit_Numero ,
               Vit_Ano , Vit_CodigoTiposExames )


...but it seems the code is using the old index.

So, Why is my index still using key lookup?
Neither index appears to be "covering". To avoid a key lookup, you must at least INCLUDE every column involved in your SELECT, WHERE, and ORDER BY.

The reason the older index is being used is that it's better for the query. That's partially because the index on Vit_DataAberturaReal prevents a sort operator in the plan - because the data is already sorted properly. It's also because the first column in the new index is useless in your query non-sargable. If you can remove the RTRIM, LTRIM, and LOWER, then the performance may be better.

If the index is only to support this query, you may consider adding WHERE Vit_codigoStatus IN (3,4) to your index definition.

Ideally, the WHERE columns would be part of the index itself - not in the INCLUDE. If I were to make an index specifically to support this query, it would probably be along the lines of:

CREATE INDEX [IX_SELECTTOP20_INCLUDE]
    ([Vit_DataAberturaReal])
INCLUDE(
    [Vit_Codigo], [Vit_Nome], [Vit_Apelido], [Vit_codigoStatus],
    [Vit_Numero], [Vit_Ano], [Vit_CodigoTiposExames],
    [Vit_CodigoEspecialidade], [Vit_Rg]) 
WHERE [Vit_Rg] = 'n\a' AND [Vit_codigoStatus] IN ( 3, 4 )


Note: this will not support the LTRIM(RTRIM(LOWER(...))).