Even though an index is created on column AddressLine1, the SQL Server engine does not choose it. It still scans the whole clustered index.
Add a column to Person.Address table
USE AdventureWorks
GO
ALTER TABLE Person.Address ADD UpperAddress NVARCHAR(60);
GO
UPDATE Person.Address SET UpperAddress=UPPER(AddressLine1)
GO
CREATE INDEX ix_UpperAddress ON Person.Address (UpperAddress) INCLUDE (AddressLine1) WITH DROP_EXISTING
It consumes more space but boosts the query performance a lot.
What is the KEY LOOKUP? The index does not cover all columns.
Observation: don't use * in your query; don't include a unnecessary column in your query.
