Avoiding hash Match by using covering index on object2.index2

SQLDB 2016-02-18 10:29:24


For the attached plan, takes around 50 minutes to complete for very simple statement.

yes, the amount of records it is fteching is quite large but is there a way i can improve the plan to run little faster.

Action taken: To avoid index scan i added the covering index (in the included column) for column1 in index2 of object2, but still it scans, not sure what could have possibly be wrong as i expect it to seek.

Aaron Bertrand 2016-02-19 21:13:40
Can you share the exact shape of Object2.Index2? Seems to me that Column2 might not be the leading key in that index? Could you try hinting a LEFT OUTER LOOP JOIN to make the HASH JOIN go away? Also you manually set transaction isolation level to snapshot, so why are there nolock hints on the individual tables? Do you want snapshot or read uncommitted?
RichardBrianSmith 2016-03-05 18:34:56
Without any definitions of the indexes already in-place how about …

create index ix_Object1_Column1Column3_inc on Object1 (Column1, Column3) include (column4);

create index ix_Object2_Column2 on Object2 (Column2);