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.
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