First properly tune the indexes, in particular getting the correct clustering index (hint: very often this means not clustering on an identity column). That is usually the single biggest overall performance factor.

Only after that does it make sense to go into more detail on the remaining problem areas.