• I think some of this was mentioned to some extent in the replies, but here are my two cents:

    1. Subtree cost: Not sure how often people use this, but I have been taught to look at this as a measuring stick when tuning a stored procedure. It is supposed to combine all the costs (io/cpu ...) into one, and provide an overall cost figure. Maybe it is not often used because there isn't a good documentation on it [or any at all?] into what the numbers actually mean. I always try to reduce the number, and in my experience an ideal stored procedure would have this number in the range of 0.1 to 0.2 and once it crosses 1.0 it indicates trouble. NOTE:This is for SELECT only stored procedures which are expected to return results in milliseconds.

    2. Memory tables: In my experience I try to avoid #temp tables and use @temp tables [i.e. variable tables in memory]. I do this only in cases where the the resulting data to be stored in the temp table is known before hand and is not too big [i.e. 100 rows and just a few small columns]. I found that using #temp tables in sps which are called thousands of times over a period of 1 hour is not a very good thing

    3. sp_recompile: I use this command when tuning a stored procedure to only take off the execution plan of sp I am tuning from procedure cache. But of course, still better not to do it in production, although good to do in a production like server to have real tuning results [i.e. not to tune for the data size in test environment and then get in trouble once it is pushed to production].

    4. If a stored procedure has IF/ELSE, where with IF you touch one set of tables and with ELSE another set of tables, I found that breaking it into 3 sps, one 1 main one, which based on IF/ELSE condition calls the appropriate one of the other two would speed things up. I think it is because it fixes the problem of the original sp where it would create one execution plan which would not work in the other case

    5. Index Rebuild: In a production server where data gets deleted/inserted a lot (like ours), indexes would need to be rebuild periodically [ideally, daily]. Otherwise, you might be tuning an sp which takes 10 seconds, which would be taking milliseconds if you simply rebuilt your indexes.

    Thanks!