Covering index and getting rid of Key Lookup

Question

I have a SQL statement that is giving me a Key Lookup sucking the processing of this query below. Since its part of a 3 part union, getting rid of the key lookup is preferable.

My query is below.

SELECT
   c.customerName,
   c.customerNumber,
   totals.TotalLoanAmount,
   totals.TotalCommitmentAmount,
   l.loanNumber,
   l.commitmentAmount,
   ed.amountThreshold,
   ex.exceptionId,
   IsNull(ex.reminderDateGracePeriod, ed.defaultReminderDateGracePeriod) AS gracePeriod,
   ex.reminderDate AS targetDate,
   IsNull(ex.exceptionState, 'N') AS exceptionState,
   ex.exceptionState AS GeneralExceptionState
FROM 
   exceptionDefinition AS ed 
INNER JOIN 
   exception AS ex ON ed.exceptionDefId = ex.exceptionDefId
                   AND ex.loanId IS NULL
INNER JOIN 
   customer AS c ON c.customerId = ex.customerId
LEFT OUTER JOIN 
   loan AS l ON l.loanId = ex.loanId
INNER JOIN 
   viewCustomerLoanTotals AS totals ON totals.customerId = c.customerId
WHERE 
   ed.requireReminderDate = 'Y'
   AND ex.statusType = 'required'

I have tried to set a covering index for this as its doing a key lookup on the Clustered index on the primary key of the exception table.

ExceptionId

This is my covering index on the columns being selected WITH the statusType as part of the where clause.

CREATE NONCLUSTERED INDEX [IX_EXCEPTIONPROCESS_COVER] ON [dbo].[exception]
(
    [exceptionId] ASC,
    [loanId] ASC,
    [reminderDate] ASC,
    [reminderDateGracePeriod] ASC,
    [exceptionState] ASC
)
INCLUDE ([statusType]) ON [PRIMARY]

This isn't having any effect on the Key lookup at all. I have tried to force it to use the index, but it becomes a 91% resource rather than the 61% I am trying to get rid of.

Any insights would be great

Answer 1

The problem is that your index can't really be used for the join. To be usable, the first field(s) in the index must be such that you're also joining the table with (or exist in the where clause).

INNER JOIN exception AS ex ON 
    ed.exceptionDefId = ex.exceptionDefId AND 
    ex.loanId IS NULL

You're joining the tables with exceptionDefId (+loanID), but the index does not have that:

CREATE NONCLUSTERED INDEX [IX_EXCEPTIONPROCESS_COVER] ON [dbo].[exception]
(
    [exceptionId] ASC,
    [loanId] ASC,
...

If this index solely for this SQL, then should work better:

CREATE NONCLUSTERED INDEX [IX_EXCEPTIONPROCESS_COVER] ON [dbo].[exception] (
    exceptionDefId,
    loanId,
    statusType
) include (
   exceptionId,
   reminderDateGracePeriod,
   reminderDate,
   exceptionState,
   customerId
)

Order of the fields in the index should be also based on the selectivity (=which one has least rows on average per key). For include fields that doesn't matter.

Answer 2

You are getting one row and your are trying to optimize the query. The 61% (where almost all of the work of this query is being done) is relative to the entire query plan for the query. There is a limit as to how much you can push. - the SQL Server engine only puts in as much work (optimizing a query) as it thinks is worth it and it treats your query as something not worth putting a lot of effort into it.

Answer 3

Your "covering" index does not include all the columns that are being used from that table according to the "output list". Add the remaining columns.