Here are the several tips to increase the performance of your SQL:
1. The number of indexes should not be too much because every index increases the time takes to perform INSERTS, UPDATES and DELETES.
2. The indexes should be as narrow as possible.
3. Try to create indexes on columns rather than charcter values.
4. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key
5. Analyse that indexes are available for JOIN conditions.
6. Analyse that indexes are available for ORDER BY clause.
7. Analyse that indexes are available for GROUP BY clause.
8. Use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes.
9. If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
10. Create surrogate integer primary key (identity for example) if your table will not have many insert operations.