Nadrek (7/29/2010)
I didn't see a very important one:Pull up SQL Server Profiler, and watch the SQL:BatchCompleted and/or RPC:Completed numbers for CPU, Reads, Writes, and Duration. Know which is most important in your environment.
Then re-write the statement a few different ways against the full dataset (always more than once), and see, in your actual environment, how each works. Derived tables vs temp tables vs table variables vs CTE vs straight joins, one large statement vs smaller statements, etc. etc.
Know how much resources a given query "should" take; check if hints help, or hurt, and by how much, and decide if they're worth it or not. This is a reasonable way to find missing indexes or SQL Server generating bizzarely suboptimal query plans, by going "Hey! That's too much... why is that too much?"
Would have thought that this is part of an investigation/monitoring phase. Once you've identified a query that needs attention, you run though the great list provided by Brian.
It's amazing how often you don't need to worry about re-writing SQL when all that's needed is a new index, or updated statistics. If either of these two meet your needs, why go through the hassle of re-writing the query/procedure?
--------------------
Colt 45 - the original point and click interface 