Shotgun approach:

Put indexes on columns of tables referenced by in the join clause.

Index columns which are selective and referenced in the where clause.

Use Graphical Show Plan: (Highlight your query & ctrl-L) go after the higher % tasks and add indexes where neccessary.

Remember the SQL optimizer will often ignore indexes that it determines will not enhance performance.

A couple reasons why an index will not help:

The first key in a composite index is not referenced in the query.

The first key in an index is not selective.

The index key is referenced by a funtion call: "where isnull(c1, 0) = 12"

hth,

joe