Post reply

Covering Index not used?

  • September 22, 2010 at 12:49 pm

    #226742

    Even though I have created my filtered index with all select columns included or as part of the index key, the optimizer still does a Key Lookup for every row. The filtered index is used, but then it looks up all found rows. Why does it do this lookup?

    [font="Courier New"]CREATE UNIQUE NONCLUSTERED INDEX ix_ZJournal2010_06_MI ON dbo.ZJournal2010_06

    (user_number,Branch,Region,Bank,TransactionTime)

    INCLUDE ( CBIGLCredits,CBIGLDebits)

    WHERE (CBIVisitNumber IS NULL AND (HostTranStatus IN ((1), (2), (3), (5)))

    AND CBIGLCredits IS NOT NULL AND CBIGLDebits IS NOT NULL)

    WITH (DROP_EXISTING = ON) ON Globalfs_Archive_2010_06

    GO

    SELECT TransactionTime

    FROM dbo.ZJournal2010_06 with (READUNCOMMITTED )

    where HostTranStatus IN (1, 2, 3, 5)

    and CBIVisitNumber IS NULL

    and CBIGLCredits is not null and CBIGLDebits is not null[/font]

    [font="Arial"] --Nested Loops(Inner Join, OUTER REFERENCES:(Uniq1002, Globalfs_Archive.dbo.ZJournal2010_06.user_number, Globalfs_Archive.dbo.ZJournal2010_06.TransactionTime, Expr1004) OPTIMIZED WITH UNORDERED PREFETCH)

    --Index Scan(OBJECT:(Globalfs_Archive.dbo.ZJournal2010_06.ix_ZJournal2010_06_MI))

    --Clustered Index Seek(OBJECT:(Globalfs_Archive.dbo.ZJournal2010_06.IX_ix_ZJournal2010_06), SEEK:(Globalfs_Archive.dbo.ZJournal2010_06.TransactionTime=Globalfs_Archive.dbo.ZJournal2010_06.TransactionTime AND Globalfs_Archive.dbo.ZJournal2010_06.user_number=Globalfs_Archive.dbo.ZJournal2010_06.user_number AND Uniq1002=Uniq1002), WHERE:(Globalfs_Archive.dbo.ZJournal2010_06.CBIVisitNumber IS NULL) LOOKUP ORDERED FORWARD)[/font]

  • September 22, 2010 at 1:06 pm

    #1225873

    This perhaps?

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=454744

    Try adding the CBIVisitNumber column as an include column. From what I recall when I investigated this, it's a usable workaround.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 22, 2010 at 1:14 pm

    #1225884

    Thanks Gail.

    I suspected that it is a "gap in functionality" but my query does not fit,

    "[font="Times New Roman"]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.[/font] "

    My query does use IS NOT NULL in both the WHERE and the index filter.

  • September 22, 2010 at 1:18 pm

    #1225888

    Thank you, Gail!

    The work-around, adding the always null CBIVisitNumber to the include list, worked very well.

  • September 22, 2010 at 1:20 pm

    #1225889

    Bodhi Densmore (9/22/2010)

    Thanks Gail.

    I suspected that it is a "gap in functionality" but my query does not fit,

    "[font="Times New Roman"]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.[/font] "

    My query does use IS NOT NULL in both the WHERE and the index filter.

    Your where clause (index and query) has 1 IS NULL predicate and 2 IS NOT NULL predicates. It's that one IS NULL that's causing the bug.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply