These basic tips by Ahmad Osama help steer your database development to avoid common performance issues.
1. Use SET NOCOUNT ON
2. Use fully qualified procedure name
3. sp_executesql instead of Execute for dynamic queries
4. Using IF EXISTS AND SELECT
5. Avoid naming user stored procedure as sp_procedurename
6. Use set based queries wherever possible.
7. Keep transaction short and crisp
You can read the details for each tip at his article here.