I have an execution plan which shows a high cost sort, then a merge join and then a high cost filter. The query runs very slowly by the way. I hear "this is an optimizer injected sort to support a merge join" and the advice is to "create an index to support the join" implying then the sort will be replaced by this index. I only can create a non-clustered index. When I look at the order by on the sort in the plan I see a static value from a support table and a value from a temp table. Its hard for me to believe that these items are "high cost" but none the less.
The question is where is the actual problem is. Is it the order by in the sort? The other half of the merge join which is from a clustered index scan? Or is it before the sort in that half of the tree. This is a really simple query. I have tried to get rid of the scan but I am 3 indexes in and its not working.
Where to start?