Query Performance Tuning When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters?  Do you run the query and view the actual execution plan?  If so, what are the things you look for?  Obviously table/index scans are the bit hits, but what else?
For a quick assessment, get the execution plan out of SSMS and in to Plan Explorer. 


Review the most expensive operations for anything unexpected. Sorts, worktables, inappropriate join operators (e.g. nested loop where you expect a merge or hash).
Look at the rowcounts at each stage of the plan, are they broadly within the range you expected to see?
Look at the estimated vs actual rows. If you're actuals are close to the estimates, it's more likely you have a good plan. If there are big variations, find out why (missing and/or out of date statistics for instance).
Evaluate the potential for parameter sniffing issues. Look for areas where cardinality may vary and test against a range of input parameters.


Lots of freely available reference material out there, Grant Fitchley's SQL Server Execution Plans is a good start. I also found Joe Chang's blog posts and ebook on execution plan costs very useful.