Why is there a >50% key lookup when an index exists covering all columns in the key lookup?

Jamie S 2016-10-17 18:47:47

The index which I believe should cover this is below, should this not mean there's an index seek instead?

CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20161017-115359] ON [dbo].[tblPackDocs_Documents]
(
[PK_UploadedDocumentID] ASC
)
INCLUDE ( [FK_FileType],
[FK_QuoteID],
[DateReceived],
[IncludeInPack],
[FileName],
[ParentDocumentID],
[ApprovedByAgent],
[IntegratorsFilename],
[OriginalFileName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Aaron Bertrand 2016-10-17 18:57:41
Looks like it's using the index [ix_docs_quote_include] to perform a seek. In that index, FK_QuoteID, which is part of the key?, is used in a join condition. In order to use the index above you may consider moving that column from the INCLUDE list to the key. You may also try an explicit WITH INDEX hint and compare the plans – it is sometimes quite clear why one index is chosen over another (I often recommend this approach to demonstrate why a scan was chosen over a seek, in cases where the seek actually turns out to be more expensive). In other cases it is simply a coin flip if the costs are roughly the same.
Jamie S 2016-10-17 19:04:50
Wow, that was fast, and dramatic. Down from 7 minutes to 24 seconds using an index hint. Thank you!
Aaron Bertrand 2016-10-17 19:05:55
Well, ideally, you should figure out why SQL Server needed the index hint, as that shouldn't stay in production code IMHO.
Jamie S 2016-10-17 19:10:07
Normally I'd agree, but this entire table is scheduled for removal in a couple of months.
Vlady Oselsky 2016-10-18 13:09:30
Have you tried to run it inside PE to get actual cost? A lot of times you can get extra data that is necessary to better troubleshoot the performance issue. Would be good to see "Table I/O" and "Index Analysis" tabs populated