What would be the best way to avoid Sort operator
In the following plan, most of the elapsed time is spent on a sort operator.
What is the best way to avoid it? A cluster key exists on those sorted fields.
Thanks for advises.
The thing is here that the rows would be arriving unordered at the aggregate due to the joining on the rows throughout the tree, this naturally requires a sort. Also consider that, Table1 is loop joined to in the first instance and therefore the data out will need ordering after that join anyway.
There is an estimation error that starts on the join to Table4, this could be out of date stats but with an anonymized plan hard to say much further than that.
You could try the HASH GROUP hint, but no promises it wont make things worse 🙂