What is a Sort operator and why can it be expensive?

A Sort operator orders rows for requirements such as ORDER BY, DISTINCT, Merge Join, ranking, or other plan operations.

Sorting can consume significant CPU and memory. If the memory grant is insufficient, the sort can spill to tempdb. Large sorts may sometimes be avoided by using an index whose key order already matches the required access pattern.

However, an index should not be added merely to eliminate every Sort operator; the write and storage costs must also be considered.