What column(s) do I need to include as part of the index creation on Object2 to improve performance

David Beaty 2017-05-15 07:07:40

Existing Index:

CREATE NONCLUSTERED INDEX [IDX_LoadDate] ON Schema1.Object2
(
Column1 ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON Column3

/storage/attachments/1934-tmp2001_Anonymized.pesession
Aaron Bertrand 2017-05-15 14:06:07
Is ON Column3 at the end a typo?
Aaron Bertrand 2017-05-15 14:14:12
This is an anonymized plan from Management Studio. We'll have much better luck digging into specifics if you can provide an actual plan generated within Plan Explorer and not anonymized if you can help it (anonymizing the plan forces us to disable the very useful Index Analysis feature, for example).

Not knowing any of the cardinalities or the primary key / clustered index on Object2 I would suspect that this index would fare better, though it would be a bit larger than the existing non-clustered index. It would eliminate the scan

CREATE NONCLUSTERED INDEX [IDX_LoadDate] 
  ON Schema1.Object2 ( Column3, Column1 )
  INCLUDE ( Column2, Column4 )
  WITH (DROP_EXISTING = ON);

You might also want to consider index tuning on Object1 to get a better join mechanism, but that should eliminate the scan on Object2 and the hash match required to assemble the input from the two different indexes on that table. If you can supply a better plan with more details, I am happy to look closer.

May 15, 2017
No Comments