In most cases, Adaptive Server uses only one index per table in a query. This means that the optimizer must often choose between indexes when there are multiple where clauses supporting both search arguments and join clauses. The optimizer first matches the search arguments to available indexes and statistics and estimates the number of rows and pages that qualify for each available index.
The most important item that you can verify using dbcc traceon(302) is that the optimizer is evaluating all possible where clauses included in the query.
If a SARG clause is not included in the output, then the optimizer has determined it is not a valid search argument. If you believe your query should benefit from the optimizer evaluating this clause, find out why the clause was excluded, and correct it if possible.
Once all of the search arguments have been examined, each join combination is analyzed. If the optimizer is not choosing a join order that you expect, one of the first checks you should perform is to look for the sections of dbcc traceon(302) output that show join order costing: there should be two blocks of output for each join.
If there is only one output for a given join, it means that the optimizer cannot consider using an index for the missing join order.
The most common reasons for clauses that cannot be optimized include:
Use of functions, arithmetic, or concatenation on the column in a SARG, or on one of the join columns
Datatype mismatches between SARGs and columns or between two columns in a join
Numerics compared against constants that are larger than the definition of the column in a SARG, or joins between columns of different precision and scale
See “Search arguments and useful indexes” on page 15 in Performance and Tuning: Optimizer for more information on requirements for search arguments.
