SQL Server Performance

Why Not Index Join?

Discussion in 'Performance Tuning for DBAs' started by rerichards, May 10, 2006.

  1. rerichards New Member

    I run a select statement resulting in the following explain plan:<br /><br />SELECT Min(TableUID) AS Table_UID, Field1, FieldFKID FROM dbo.MyTable GROUP<br />BY Field1, FieldFKID<br /><br /> |--Hash Match(Aggregate, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[MyTable].[Field1], [MyTable].[FieldFKID]),<br />RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[MyTable].[Field1]=[MyTable].[Field1] AND [MyTable].[FieldFKID]=<br />[MyTable].[FieldFKID]) DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1002]=MIN([MyTable].[TableUID])))<br /><br /> |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[MyDB].[dbo].[MyTable].[PK_MyTable]))<br /><br />Output List:<br />[MyTable].[TableUID], [MyTable].[Field1], [MyTable].[FieldFKID]<br /><br />The index that is being scanned [PK_MyTable] is a nonclustered composite<br />index composed of columns TableUID and Field1.<br /><br />I have a another nonclustered index named IX_FieldFKID that is composed of a<br />single column on FieldFKID.<br /><br />If I understand what the Output List is trying to tell me, that in order for<br />the optimizer to use an index seek, I could create a covering index by<br />including FieldFKID in composite index [PK_MyTable].<br /><br />This table has a little over 10,000 rows.<br /><br />Why does the optimizer make NOT make use of index IX_FieldFKID and perform an<br />Index Join, resulting in a Seek, rather than a Scan?<br /><br />
  2. mmarovic Active Member

    Do you have a clustered index on the table?
    Is there an index (hopefully clustered) starting with tableUID?
    To achive max performance for this query I would create composite non-clustered index consisting of field1 and fieldFKID (column order doesn't matter) if there is clustered index on tableUID. If not, I would add tableUID as the last column (third) column in that index.
  3. rerichards New Member

    There is a clustered index on the table, but it is not one of the three used in the query. And I agree, as we both stated in our comments, that a covering index would remove the scan. However, getting back to my question, why does the optimizer use an index join (or index intersection) to perform an index seek, as there are two indexes that cover all three fields?
  4. rerichards New Member

    Sorry, I reread what I wrote and it did not make sense. Allow me to revise with more clarity.

    There is a clustered index on the table, but it is not one of the three fields used in the query. And I agree, as we both stated in our comments, that a covering index would remove the scan. However, getting back to my question, why does the optimizer NOT use an index join (or index intersection) to perform an index seek, as there are two indexes that cover all three fields?
  5. mmarovic Active Member

    I guess the reason might be that pk_myTable doesn't start with field1. Can you try to change the column order and see what happens?
  6. Twan New Member

    Hi ya,

    a covered index would not remove the index scan either...? The query has no where clause so it will never do an index seek? You're asking sql to get the minimum value of tableuid for every combination of field1, fieldfkid, which will require an index scan into a worktable

    Cheers
    Twan
  7. rerichards New Member

    Shouldn't index [PK_MyTable] remain as it is with TableUID coming before Field1 since that is the order in the Select statement? Anyway, I still tried what you suggested by swapping the field order of index [PK_MyTable] to Field1, TableUID and it still returned the same index scan.
  8. mmarovic Active Member

    Uh, Twan is right, I must have been halucinating. There is no where clause. Composite index would provide faster execution because no other index access would be needed, but as Twan mentioned, scan stays anyway.
  9. rerichards New Member

    Understood. Thanks.

Share This Page