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

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

avatar image By David Beaty 1 asked May 15 at 07:07 AM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ May 15 at 02:06 PM

Is ON Column3 at the end a typo?

10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Aaron Bertrand ♦ 1.7k answered May 15 at 02:14 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x588
x405
x105
x36
x1

asked: May 15 at 07:07 AM

Seen: 33 times

Last Updated: May 15 at 02:14 PM