What would be the best index(es) to add to the tables in order to make this query as fast as possible. The sd.incidents tables is about 2.5M records, problems about 8K, and changes about 120K.

  --bunch of fields
    sd.incidents a
    left join sd.problems b on a.pr_number = b.pr_number
    left join sd.problems c on b.pr_number = c.parent_pr_number and c.is_root_cause = 1
    left join sd.changes d on b.caused_by_change_number_clean = d.change_number
    a.severity in (1,2,3) and
    (a.pr_number is not null or a.parent_number is null)

1 Answer 1


If you have a really large number of fields in the SELECT and/or the fields are wide, you should make sure the JOIN fields are your clustered indexes as well.

Pulling a few dozen fields via a key lookup can be the most expensive part of a query like this. Clustering on your JOIN keys forgoes that step.

The only real way to know is to run the query, check the execution plans, and see where your bottlenecks are. For the WHERE clause you could make a filtered index that just includes the cluster key of that table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.