September 22, 2010 at 12:49 pm
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
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
September 22, 2010 at 1:14 pm
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
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
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply