As Madhivanan mentioned, the only way to figure out details of indexes being used is by looking into the execution plan. The query optimizer may not pick an index that we believe it could use in a number of scenarios. A number of factors such as the selectivity of the condition, status of statistics, included columns available with the indexes etc will influence the choice of indexes.
if the table is really small, the query processor may decide to use a table scan always, instead of using an available index.
So execution plan is our best friend to figure out the details of how exactly the query processor executes our queries.
commented on Aug 15 2012 6:06AM