Query optimization tips

1. Create necessary indexes as suggested by Database Tuning Adsvisor.
2. You can also look at the dynamic views like sys.dm_db_missing_index_details

3. If you have proper indexes, go for the query execution plan to find out heavy operations. Look for Table Scans and index scans.

4. Normally, these are, unnecessary use of user defined functions in SELECT, unnecessary use of user defined functions in WHERE, unnecessary joins, and joining on NON indexed columns (should be joined with PK-FK relationship)

5. If using derived tables in the query, check for the query which is slowing down the whole query.

6. Avoid unnecessary DISTINCT and Order BY

7. Apply your WHERE clause with some mandatory conditions. For example, From date, To date, location, etc to slice down the records and output

8. Create covering Index if required.

9. Learn how to read execution plan if you dont know.
 
10. Get youself familiar with the performance moniotor and its SQL Server, Disk I/O, etc related counters