Filtered Index execution plan is not optimized - by Nball

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 643850 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 2/14/2011 5:08:13 AM
Access Restriction Public


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

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.
Sign in to post a comment.
Posted by Randy in Marin on 4/1/2015 at 5:00 PM
The optimizer should try to use the filter index to limit the records returned as a possible plan for the optimizer to test. The addition of a plan that does a simple CTE join would probably be enough. For example, the addition of the CTE below does not seem difficult to generalize. (I'm using SQL 2012.)

        SELECT TOP 1 *

Table 'INQUEUE'. Scan count 1, logical reads 242442, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1063 ms, elapsed time = 1069 ms.

    ;WITH FilterCTE (ROWID) AS (
    SELECT TOP 1 *
    INNER JOIN FilterCTE f
    ON f.ROWID = t.ROWID

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INQUEUE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 0 ms, elapsed time = 0 ms.

Looks like I'm saving going to save 1 second of CPU time per second.
Posted by Paul White NZ on 9/23/2013 at 6:10 PM

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
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