Some of this is probably SQL server specific, but we were having some scaling issues on legacy code earlier this year and I got a lot of bang for my buck checking for these things before diving in to specific issues:

- code with a high row count table variable when a temp table would give more accurate execution plans due to better cardinality estimates.

- ultra-complex join criteria with lots of OR logic that performed better as a UNION

- lazy function calling, where a developer used a function that did more than they needed and could be replaced with a simple join to a table

- looped calls to insert procs that could be done in bulk

There was another entire category of problem that I guess would be described as "I hate sets" and was mostly attributable to one former employee. These were recognizable immediately upon opening the code and were a nightmare.