Slow query – I think my index is just making things worse :(

CREATE INDEX IX_dbo_tblWorkflowDataArchive__DataObjectID_I_ItemID_Description
ON dbo.tblWorkflowDataArchive
    (DataObjectID)
INCLUDE
    (ItemID, Description);
 
CREATE INDEX IX_dbo_tblArchiveDataObjectDetail__Value_ArchiveDate_I_FieldID
dbo.tblArchiveDataObjectDetail
    (Value, ArchiveDate)
INCLUDE
    (FieldID);

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 Value data (potentially very many millions of rows), then applies the ArchiveDate predicate to every row. Unfortunately, neither SSMS nor PE do a great job of showing the seek. We can improve the PE situation there.

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.