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

RichardBrianSmith 2015-08-11 08:47:09

SQLkiwi 2015-08-11 10:14:15
Good indexes to support that query are:

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

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:

Detail Seek

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.

SQLkiwi 2015-08-22 11:51:52
@RichardBrianSmith Did you ever get a chance to look at this and try the suggested indexes?
RichardBrianSmith 2015-08-25 11:09:28
Hi SQLkiwi, YES, I took my shonky index off and put your 2 on and the total time reduced from 13 to 3 minutes, thank you 🙂 If you have time — how did you get from the selects to the placement of the columns in the index please?
SQLkiwi 2015-08-29 09:36:49
Mostly it was a question of recognising that IS NOT NULL is an equality condition from an index seeking point of view. As such, the Value column should go ahead of the ArchiveDate (because of the inequality) in the index column order. Once that was done, it's just a case of ensuring the indexes are covering and support the obvious nested loops join strategy.
Kevin 2015-09-10 19:14:32
Could you explain that statement a bit more fully, Paul? Is that specific ordering of the columns in the suggested index likely to change if, for example, the ArchiveDate had much more selectivity than the Value column? Many thanks, -Kev
SQLkiwi 2015-09-10 19:31:52
@Kevin No, it's just the general principle that equality conditions need to go before inequalities to get b-tree seeking. It's not obvious that IS NOT NULL is an equality test, but it is.