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 />
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.
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?
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?
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?
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
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.
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.
