Filtered Index execution plan is not optimized

Filtered Index execution plan is not optimized by Nilmov

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	643850
Opened:	2/14/2011 5:08:13 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

I am using Adventureworks database as a testing
I created a filtered index

CREATE NONCLUSTERED INDEX NCI_Department
ON HumanResources.Employee(EmployeeID,LoginID)
WHERE Title= 'Marketing Manager'

SELECT he.EmployeeID,he.LoginID,he.Title
FROM HumanResources.Employee he
WHERE he.Title = 'Marketing Manager'

When i checked the execution plan, it is going for key lookup .Clustered.

WHy should it go for key lookup?
Select is using employeeID,LoginID and Title.
My index has employeeID, loginID and title in where clause.
Also, employeeID should not be required as employeeID is already clustered index key and it will be in NCI as well.

The reason behind raising this bug is why it is going for key lookup. Isn't it getting all the data required for fetching the select.

Details (expand)

English

SQL Server 2008 SP1

SQL Engine

Not Applicable

1. Use AdventureWorks database
2. Create a non clustered index
CREATE NONCLUSTERED INDEX NCI_Department
ON HumanResources.Employee(EmployeeID,LoginID)
WHERE Title= 'Marketing Manager'

3. Run the following select

SELECT he.EmployeeID,he.LoginID,he.Title
FROM HumanResources.Employee he
WHERE he.Title = 'Marketing Manager'

4. Check the execution plan

I can see a key lookup clustered in the execution plan

I shouldn't see a operator like key lookup clustered as my index structure has all the necessary information

0 attachments

Posted by Paul White NZ on 3/13/2012 at 5:12 AM

Martin, that's a separate issue being tracked at http://connect.microsoft.com/SQLServer/feedback/details/454744/filtered-index-not-used-and-key-lookup-with-no-output

Posted by Martin Smith on 2/15/2012 at 11:03 AM

Also I see in the BOL example against AdventureWorks the query is "WHERE EndDate IS NOT NULL" and that does not do a lookup but inverting the condition on the filtered index and query to "WHERE EndDate IS NULL" does cause a lookup so presumably there is in fact an existing mechanism that is supposed to handle this?

Posted by Martin Smith on 2/15/2012 at 10:55 AM

Not sure the scenario is that narrow. It also applies to "IS NULL" and one of the example use cases explicitly given for filtered indexes in BOL is for use with columns with mostly NULL values.

Posted by Microsoft on 2/15/2011 at 5:02 PM

Thanks for the feedback. The bookmark lookup is required because the index does not carry the column Title.

The column Title is used in the filter clause but that does not mean that the index stores it.

The filter clause is Title='Marketing Manger' so Title is a constant but the Optimizer does not exploit that and populate the column with that constant value though I suppose it could.

An easy fix is to include Title in the index. We'll close this because it seems a narrow scenario and we don't see doing anything about it in the forseeable future.

Eric Hanson
Program Manger
SQL Server Query Processing