Sql-server – SQL Server 2008 Index

indexsql serversql-server-2008

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.

select
  --bunch of fields
from
    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
where
    a.severity in (1,2,3) and
    (a.pr_number is not null or a.parent_number is null)

Best Answer

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.

Related Question