Unnecessary key lookup being performed while using filtered index.

Faisal 2015-06-19 22:37:31

Why would SQL Server 2012 perform key lookup, while executing the following query in AdventreWorks2012 DB, when all the data needed to satisfy a query is available though a filtered index.

SELECT p.FirstName, p.LastName
FROM Person.Person p 
WHERE PersonType = 'SC'
AND p.ModifiedDate > '2008-01-01'
AND p.EmailPromotion = 1;

For the following non filtered index, SQL Server does not do a key lookup, since all the information required to execute the query is available in the index.

CREATE NONCLUSTERED INDEX IX_Test_Filtered_1
ON [Person].[Person] ([PersonType],[ModifiedDate],EmailPromotion)
INCLUDE ([FirstName],[LastName])

But, SQL Server does a key lookup on Person table when the following filtered index is used, which increased table reads significantly.

CREATE NONCLUSTERED INDEX IX_Test_Filtered_2
ON [Person].[Person] (EmailPromotion)
INCLUDE ([FirstName],[LastName])
WHERE (PersonType = 'SC' AND ModifiedDate > '2007-01-01')

I am forcing appropriate index usage using index index as follows:

SELECT p.FirstName, p.LastName
FROM Person.Person p WITH( INDEX(IX_Test_Filtered_2))
WHERE PersonType = 'SC'
AND p.ModifiedDate > '2008-01-01'
AND p.EmailPromotion = 1;
 
SELECT p.FirstName, p.LastName
FROM Person.Person p WITH( INDEX(IX_Test_Non_Filtered_1))
WHERE PersonType = 'SC'
AND p.ModifiedDate > '2008-01-01'
AND p.EmailPromotion = 1;

I am trying to use a filtered index on a very large table and would like to get rid of this key lookup.

/storage/attachments/1272-Plan.pesession
SQLkiwi 2015-06-19 23:34:53
Your index is filtered to only contain rows where ModifiedDate > '2007-01-01', but the query specifies ModifiedDate > '2008-01-01'. So, the index may include rows that should not be returned by the query (e.g. ModifiedDate = '2007-07-31').

To check if the value of ModifiedDate in each row meets the query filter, the query processor must look up ModifiedDate in the clustered index. You can avoid this by including ModifiedDate in the keys or INCLUDE list of the filtered index definition. For example:

CREATE NONCLUSTERED INDEX IX_Test_Filtered_2
ON [Person].[Person]
    ([EmailPromotion], [ModifiedDate])
INCLUDE
    ([FirstName], [LastName], [PersonType])
WHERE
    (PersonType = 'SC' AND ModifiedDate > '2007-01-01');

There are many good reasons to always include filter condition columns in the keys or include list of a filtered index.

June 19, 2015
No Comments