philcart (7/29/2010)
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?
Updated statistics, certainly; that's a normal operational cost.
However, adding an index adds to the operational costs of writes to that table for the lifetime of the index; perhaps a simple, or even not so simple, rewrite would allow the query to improve performance without adding any additional costs?
Perhaps more importantly, how will you learn what types of query implementations work better in which circumstances on your particular environment without testing them? This is critically important in some cases, particularly for writing new SQL in an environment that's heavily loaded for whatever reason; if there's little headroom, then using as little as possible of what's left is vital, in addition to freeing up more headroom.