Expensive Sorts

We warn about this when one Sort is >=50% of the entire plan cost, because you may be able to address it easily with an index, and SQL won’t always generate missing index requests when they crop up.

Sorting in the database can be pretty painful, and if you can offload it to the app or presentation layer, that’s usually best.

It’s a stop-and-go operation, which means it needs to consume the entire result set before beginning the Sort, and it won’t pass data along until the Sort is complete. The Sort may be spilling to tempdb, but that won’t show in a cached plan; only in the actual execution plan.
These are fairly easy to diagnose and correct. Look for the Sort operator in your plan, and hover over it to see which column(s) you’re ordering by.

If you’re ordering by values in Descending order, your index definition will have to reflect that.

When indexing for these gets complicated

If you’re joining or filtering on different columns than you’re ordering by, you may lose some performance in those operations by having your ordering column(s) as the left-most columns in the key of your index. Try to strike a balance here, and always test changes carefully in dev.

Keep in mind that SQL may inject Sorts into an execution plan even if you don’t call ORDER BY specifically, to assist with aggregations, windowing functions, merge joins, and sometimes key lookups, when SQL judges that sorted input will make the operation run faster.