Well I agree with your statement about being careful with temporary tables, don't ignore them. In many cases I have taken a complex multi-table query and broken it up into simpler queries that have significant less overall query cost. This is especially true when the original complex query contains a PK table scan on a very large table and then joining it to a derived result set in lieu of performing an index seek on the large table. This is especially obvious when looking at the query plan and observing large intermediate result sets (thick lines) where there should not be such.

Usually when I am given a stored proc to "tune", after cleaning it up (converting to ANSI-92 standard for readibility, SET NOCOUNT ON, moving DDL statements to top of stored proc, and fully qualifing object names) I analyze the query plan to see if it makes sense. As M$ frequently states, the query optimizer doesn't pick the best plan, just one that it thinks is OK. My job then is to "help" the optimizer pick a better plan.

Usually I use profiler to determine which stored procs need attention, then the query plan to identify statements within the stored proc that need attention.

Also, don't forget to consider revising/adding indexes to help performance. The INCLUDE clause in SQL Server 2005 is a "God send" for covering indexes.

Cheers,

Mike Byrd