SQL Server Performance

Join Performance Question!

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by telly, Feb 25, 2009.

  1. telly New Member

    First i want excuse for my bad english but i hope you will understand me.
    My Problem: I created a huge database with 2 tables.
    Table1 (20.000.000 rows):
    id [PK] (clustered index)
    col1 (non clustered index)
    Table2 (2.000 rows)
    id [PK] (clustered Index)
    Now i create the Statement:
    Select a.col1, a.col2, b.col1 FROM Table1 a inner join Table2 b on a.col1 = b.id
    This Statement use the correct index in Table2 but in Table1 it use the clustered index on id-column.
    My Questions:
    Why the optimizers chooses the clustered index ob Table1 and not the index on col1?
    How can i change this manuelly?
    Any other comments?
    Thnx for your help,

  2. FrankKalis Moderator

    Hm, how many rows are returned by that statement? If that number exceeds a certain threshold, it is easier to scan the clustered index. Can you post the execution plan?
  3. Amarlai New Member

    Join this forum today and found it really very informative.
  4. geebee2 New Member

    You can try an index hint ( don't know if it will work ), e.g.Select a.col1, a.col2, b.col1 FROM Table1 a with Index( IX_A_COL1 )inner join Table2 b on a.col1 = b.idProbably a better way is to specify the non-clustered index on Table1 to include the required data columns as well, both col1 and col2.That means it won't have to use the primary index to get the value for col2 ( which is why it isn't using the index on col1 at present ).
  5. madhuottapalam New Member

    You are selecting COL1 and COL2 from Table1 when the index (NCI) is only on COL1. In that case, if query uses NCI on COL1 to get COL2 data it has to go to data page hence there will be Book Mark Lookup which is generally bad from IO prespective. So the optimizer detected the NCI is not suitable for the query and it used CI. You may create a covering index on COL1 and COL2 and run the query, you will find that the index is being used.

Share This Page