What column(s) do I need to include as part of the index creation on Object2 to improve performance
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
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.