The query optimizer can't do its job well without the right tools and an environment that lets it choose from a broad range of possibilities. Here are a few guidelines to help you supply the right tools to the query processor.
Create indexes on frequently joined columns. Clustered indexes enable merge joins and fast lookups with nested-loop joins. Use the Index Tuning Wizard for recommendations.
Create covering indexes on combinations of frequently fetched columns. These indexes satisfy queries from the index's leaf level, and you can use them as input for merge joins if the first index column is also the join column.
Create indexes with care, especially covering indexes. Indexes improve performance of select queries but degrade performance of modification operations—in other words, they're good for OLAP but bad for online transactional processing (OLTP).
Avoid performing joins on columns with few unique values. These joins can be more costly than simply using table scans, so the query processor might decide not to use them, and they degrade the performance of modifications.
Separate tables that participate in joins onto different disks by using filegroups to exploit parallel disk I/O.
