I cannot get these two key lookups out of my plan

Bryan Wright 2016-08-30 15:13:53

Without rewriting this query I cannot get rid of these two key lookups our my plan. I know i could if I was allowed to rewrite the plan but in this case it is not possible. Can anyone help me out I have tried everything. I have tried various different indexes to remove them and nothing is working.

Aaron Bertrand 2016-08-30 15:17:11
Did you try adding the column DELETE_FLAG to the index [SFDC_ACCOUNT].[IX_SFDC_ACCOUNT_ACCOUNTX], and ACCOUNTID to the index [SFDC_OPPORTUNITY].[idx_SFDC_OPPORTUNITY_DelFlg_ETLID_inc_AcctID_ClsDt_TranTyp]? The latter can probably be an INCLUDE column because it is only used for output, but the former is used in a filter, so may be more useful as a key column.
Kevin 2016-08-31 16:08:04
A couple other notes since there's a lot more than just key lookups in here:

  1. Please start to use nested code to keep correlated code at the same level indentation. That'd make troubleshooting MUCH easier.
  2. Large numbers of CONVERT_IMPLICIT (on the Expressions Tab), which you should explicitly CAST to the appropriate data type. This may eliminate your Index Scan count. Both of the Index Scans in turn cause really big spools to tempdb.
  3. In fact, both of the Index Scans occur on what appear to be identical branches of the query plan. Could you possibly derive that duplicated set of work into a temp table and join to that intermediate result set?
  4. It appears that you have an aversion to explicit joins altogether, instead using correlated subqueries in places where joins could work just fine. You might want to try using appropriate INNER and OUTER JOIN clauses to see how performance is different and possibly better. Many times OUTER JOINs will out perform correlated subqueries featuring NOT.

Hope that helps,
-Kev

Vlady Oselsky 2016-09-01 12:28:53
Agree with what Kevin said, but also would like to point few things out.

  1. As a developer, my first response is to go after warning messages in the plans. In your case, it shows CONVERT_IMPLICIT . To make matter worse, it is NVARCHAR conversion. Jonathan Kehayias has a great post on how bad NVARCHAR conversions can be.
  2. Second, you should be concerned with thick lines that become thin. That indicates improper filtering that is unable to remove rows from base tables and have to do it after the fact. Most of the time it is caused by missing index on column required for a join or CONVERT_IMPLICIT or another NonSargable function which cause SQL Engine being unable to correctly utilize an index.
  3. Lastly, to your original question. Key lookups are caused when an index does not have all information required to get bring back all columns requested by the query. One way to fix it is to create "covered" index which simply means having INCLUDE part with columns that need to be brought back. The cost of having INCLUDE columns is lower than including those columns in the index. Sometimes it is better to have key lookup than having to include every single column in the table in an index. If you reduce how many rows comeback from the table that KEY Lookup operation is not going to be as costly as it is right now.