T-SQL Performance tuning best practices collection

Maximum Degree of Parallelism in T-SQL Queries

http://bloggerzspot.com/maximum-degree-of-parallelism-in-t-sql-queries/

Avoid SQL Server functions in the WHERE clause for Performance

When functions are used in the SELECT clause, the function has to be run with each data value to return the proper results.  This may not be a bad thing if you are only returning a handful of rows of data.  But when these same functions are used in the WHERE clause this forces SQL Server to do a table scan or index scan to get the correct results instead of doing an index seek if there is an index that can be used.

http://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/