Good indexes to support that query are:
It's better to run queries directly from Plan Explorer, because this captures additional performance information, which is useful to highlight the underlying issue.
In this case, the big problem is that the existing indexes do not support a complete seeking operation on the Detail table. There is a large residual predicate as shown below:
Notice the very high Estimated I/O Cost. This 'seek' locates all rows with non-NULL
It is hard to see just how expensive this seek is without exposing the residual predicate as a separate Filter using undocumented trace flag 9130. Being undocumented, you would never use it on a production system, of course. It is useful to reveal the issue, however.
Anyway, the indexes above should improve performance very markedly.
By SQLkiwi ♦ 6.6k answered Aug 11, 2015 at 10:14 AM