SQL Server Performance

Wrong Index Being Used

Discussion in 'Performance Tuning for DBAs' started by mstone, Jan 23, 2006.

  1. mstone New Member

    I have a table with 50+ columns and 1M rows
    The PK is a clustered Index (integer)
    Two other Columns are Indexed (colA,ColB)

    Query1 = SELECT * FROM Table Where ColA = 0 AND ColB = 29
    Query2 = SELECT ColA, ColB FROM Table Where ColA = 0 AND ColB = 29
    Query3 = SELECT ColA, ColB,ColC FROM Table Where ColA = 0 AND ColB = 29

    Query1 uses the clustered index (kind of expected because of the select *)
    Query2 uses the index on ColA, ColB
    Query3 uses the clustered index

    Query 2 performs significantly faster + fewer logical reads
    1 & 3 are 5-10x slower

    running 1 or 3 with WITH (INDEX()) results in good performance.

    Also Just for fun I removed the PK Clustered index and the plan chooses to use a Table Scan rather than the ColA,ColB indexes.

    why does the optimizer prefer to use the clustered index (even though it clearly performs poorly)?
    Is it something in the table/index design that I need to look at?
    Thanks
    MS
  2. Luis Martin Moderator

    The cluster index on witch column was created?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. mstone New Member

    the PK is the clustered index. i.e. only involved in query1
  4. Luis Martin Moderator

    What I would like to know is: On colA, colB, colC or other column?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  5. mstone New Member

    ok sorry.
    It's on another column
    let's call it ColX
    MS
  6. Luis Martin Moderator

    Execution plan sometimes use scan table some times use cluster when no one index sastify the plan.

    In the 3rd query I suppose the reason is not to many rows in the table.

    How about if you take a look of ITW for each case?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  7. mstone New Member

    ITW makes no new recommendations.

    I'm really just trying to determine what logic the plan is using to determine why the clustered index may be faster?
    Is it possibly due to a large row size (with so many columns).
    I would have thought it looked at the where clause to determine what indexes to use. (where clause results in selecting of less than 1% of the total data)

    It seems to be looking at the select and if an index doesn't cover all columns it reverts to the clustered index or table scan.

    If there were more rows (there are nearly 1 Million right now) would it be likely to use the indexes on ColA and ColB or would it still choose the same plan?

    Any thoughts or can you direct me to any articles that may explain what is going on here?
    Thanks
    MS
  8. joechang New Member

    the optimizer uses a set formula for deciding between 1) a table scan, and 2) an index seek with a bookmark lookup to get values not in the index.

    hence Q1 and Q3 require the bookmark lookup

    the SQL optimizer assumes that 1 table scan page costs 0.00741sec, and a bookmark lookup for 1 row costs either 0.006325 or 0.00312 sec,
    depending on whether your table in memory or on disk, this formula is seriously in either direction, for modern server system, it might have been right at one point in time,

    but actually i have a suspicion that MS let an intern do the CBO calibration, instead of having a more experienced person do it.

    all of this are in my articles and powerpoint some where on this sight
  9. mstone New Member

    I suppose I'm stuck with the With(Index()), doesn't give you much faith in the plan optimizer <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I'll do some reading.<br /><br />Thanks
  10. Twan New Member

    HI ya,

    How selective is COlA in your example? It may be that SQL decides that since colA is not that selective it would need to go through x% of the index and therefore might as well scan rather than seek

    if there are fewer unique values for colA in the table than colB then switch the order of the columns in the index to see if that makes a difference

    Cheers
    Twan
  11. mstone New Member

    ColA and ColB are indexed seperately (having a compound index did not seem to change this plan)

    ColA is 1 of 30 possible values the value in my current select would return 10-20% of the table all others are even distributed

    ColB could be 1 or 10 values and the value 0 is about 25% of the table.

    Thoughts?

Share This Page