0

What are the reasons that the optimizer chooses to insert a sort operator in the plan (to satisfy a stream aggregate or a merge join, etc.) instead of going for hash match or hash join?

I have now see a couple of examples where it obviously makes a mistake and hinting the query will make it a lot faster.

The examples I have seen are from SQL Server 2016.

Improve this question

1 Answer 1

Reset to default
2

What are the reasons that the optimizer chooses to insert a sort operator in the plan (to satisfy a stream aggregate or a merge join, etc.) instead of going for hash match or hash join?

It's a cost-based query optimizer that estimates the cost of a candidate plan based on the available statistics. Statistics may be missing or out-of-date, and even with good statistics the cost estimation process is not perfect.

So the reason is simply that based on the available statistics, it estimated that that was the cheaper plan.

Improve this answer
2
  • Hi David. Thanks for the answer. Even after UPDATE STATISTICS WITH FULLSCAN this happens but I guess your answer covers this senario also. Is the only fix then to use a hint?
    – xhr489
    Commented Feb 2, 2023 at 14:43
  • 1
    Statistics and be missing as well as out-of-date. Also there may be missing indexes.
    – David Browne - Microsoft
    Commented Feb 2, 2023 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.