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

avatar image By RichardBrianSmith 97 asked Aug 11, 2015 at 08:47 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

sp.png (31.9 kB)
avatar image By SQLkiwi ♦ 6.6k answered Aug 11, 2015 at 10:14 AM
more ▼
(comments are locked)
avatar image SQLkiwi ♦ Aug 22, 2015 at 11:51 AM

@RichardBrianSmith Did you ever get a chance to look at this and try the suggested indexes?

avatar image RichardBrianSmith Aug 25, 2015 at 11:09 AM

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?

avatar image SQLkiwi ♦ Aug 29, 2015 at 09:36 AM

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.

avatar image Kevin Sep 10, 2015 at 07:14 PM

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

avatar image SQLkiwi ♦ Sep 10, 2015 at 07:31 PM

@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.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Aug 11, 2015 at 08:47 AM

Seen: 178 times

Last Updated: Sep 10, 2015 at 07:31 PM