SQL query optmization
Indexing is always the first port of call. Ensure that you have the proper indexes in place before anything else.

Next run the query and study the actual execution plan which will give some much needed pointers.

http://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

Finally these tricks can give a well needed performance boost and most if not all are good practice anyway:


Ensure All tables have a primary Key.
Ensure All tables have a relevant clustered Index.
Ensure All Tables have the appropriate non clustered index. Specifically all columns referenced in the Where / Order By and Join clauses should be covered.
Avoid Views where possible.
Do not use SELECT *
Ensure all of your query is relevant especially where it joins to other tables.
Avoid Cursors unless absolutely necessary.