Home Dashboard Directory Help
Search

Filtered index not used and key lookup with no output by GilaMonster


Status: 

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


34
0
Sign in
to vote
Type: Bug
ID: 454744
Opened: 5/20/2009 9:45:37 AM
Access Restriction: Public
1
Workaround(s)
view
14
User(s) can reproduce this bug

Description

A query with an IS NULL predicate that precisely matches a filtered index predicate where the index is covering gets a plan that uses a clustered index scan. If the index is forced via a hint then the expected nonclustered index scan is seen, but there's a key lookup present in the plan as well that's not looking anything up. It's output list is empty.

If the predicate of the query and the index is inverted (to IS NOT NULL) then the index is selected as expected and there is no key lookup present.
Details
Sign in to post a comment.
Posted by Microsoft on 4/10/2010 at 11:08 AM
Dear Customer,

Thank you for pointing this out. After further investigation, we have discovered that this is actually not a bug but rather a known gap in functionality. This is now an active DCR for a future release of SQL Server.

Thank You,
Mohammed Ali
Posted by Microsoft on 6/3/2009 at 4:10 PM
Dear Customer,

Thanks for reporting this issue to us. It is valid, and we expect to release a fix in SQL Server Service Pack 2.

Thanks,
Yavor
Senior Program Manager
SQL Engine
Sign in to post a workaround.
Posted by RichardB CFCU on 9/29/2011 at 9:15 AM
I've experienced this as well (SQL2008R2 v10.50.1600.1 Std64bit), and a simple workaround is to INCLUDE the column that is being filtered on.

Example:
---
CREATE NONCLUSTERED INDEX [idx_FilteredKey1] ON [dbo].[TABLE]
(
    [TABLE_ID] ASC,
    [TABLE_ID2] ASC
)
INCLUDE ( [REMOVAL_TIMESTAMP]) --explicitly include the column here
WHERE ([REMOVAL_TIMESTAMP] IS NULL)
---

I look forward to this no longer being necessary in a future release!
File Name Submitted By Submitted On File Size  
Filtered indexes.sqlplan (restricted) 5/20/2009 -