The very first things I look at in a proc are cursors, scalar UDF's, and IN abuse. Table/clustered index scans and temp tables may or may not be appropriate, I think blanket warnings may take people in the wrong direction.