A good set of tips. The first focuses on using SET and DBCC, the second focuses on Indexing strategies, and the third focuses on Query optimization:
http://www.dlt.com/quest/pdf/microsoft%20infrastructure/microsoft%20sql%20server/tuning%20part%201.pdf
http://www.dlt.com/quest/pdf/microsoft%20infrastructure/microsoft%20sql%20server/tuning%20part%202.pdf
http://www.dlt.com/quest/pdf/microsoft%20infrastructure/microsoft%20sql%20server/tuning%20part%203.pdf