• tony28 (5/8/2013)


    here is simple select, I found in little difficult, but here you can see also

    select ORDER_DATE from TB_INSPECTION_DETAIL WHERE ORDER_DATE='20130507' and COMMIT_NO='0085'

    subtree cost

    IX 0,0032838 - rows 1,62897

    PK 0,0464984 - rows 1,62897

    The query you are showing uses columns that are all within the nonclustered index you defined above. This makes the index into what is known as a covering index. In effect, for this query, it's a clustered index since all the information it needs is there. The optimizer is smart enough to recognize that the nonclustered index is going to be a better selection for this query because it's going to filter off the leading edge of that index whereas with the clustered index, the necessary columns, while in the key (that's a VERY large key by the way and may lead to other issues), would require more filtering.

    As to those costs, they're useful for comparing operations to each other within a plan, but they're not very useful outside the plan because they are estimated costs, even on an actual execution plan.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning