Home Dashboard Directory Help
Search

Filtered Index execution plan is not optimized by Nball


Status: 

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


7
1
Sign in
to vote
Type: Bug
ID: 643850
Opened: 2/14/2011 5:08:13 AM
Access Restriction: Public
1
Workaround(s)
view
2
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
Posted by Paul White NZ on 9/23/2013 at 6:10 PM
Buddylee17,

The Title column does not have to be added to the index keys, it can be an INCLUDE column.

It wouldn't be safe for the optimizer to 'short-circuit' (as you describe it) in all cases.

When the predicate Title = 'Marketing Manager' returns true, that does not mean the column and the constant are identical, just that they *compare equal*.

1. If the constant were 'mArKeTiNg MANager' and the collation case-insensitive, should the query return 'Marketing Manager' or mArKeTiNg MANager'? It couldn't return the former without a lookup.

2. The type of the column is nvarchar, where a varchar constant is specified. Should the query return a varchar?

There are all sorts of little subtleties like that. It is all too easy to optimize beyond correctness.
Posted by buddylee17 on 9/23/2013 at 12:43 PM
The fact that the optimizer can't figure this out and short circuit is pretty ridiculous. There's no reason to incur the extra physical and logical storage cost by adding Title to the key list. Especially when it's an equality comparison.
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
Sign in to post a workaround.
Posted by Paul White NZ on 9/23/2013 at 6:02 PM
Add Title to the index INCLUDE list, or specify the constant value in the SELECT list instead of the Title column.