Vlad Drumea comes up with a list:
This is my list of “main suspects” that make me instantly think a query is bad as soon as I see at least one of them.
Note, this is just based on the query text alone, seeing the execution plan is an instant confirmation.
Now, without further ado and in no specific order:
Click through for the list. There are some good items on it.